sql - 将表中的数据存储到对象表中

Question

您将如何将数据传输到对象表中？

假设你有一张 table :

create table thisTable(
    column1 varchar2(20),
    column2 varchar2(20),
    column3 varchar2(20)
)
/

你有一个新的对象表:

create table oo_thisTable(
    object1 object1_t
)
/
create type object1_t as object (
    column1 varchar2(20),
    column2 varchar2(20),
    column3 varchar2(20)
)
/

如何将数据从 thisTable 传输到 oo_thisTable？

declare
    cursor c1 is
        select * from thisTable;
begin
    open c1;
    loop
        fetch c1 into column1, column2, column3;
        exit when c1%notfound;
        ...

Answer 1

无需使用PL/SQL，您可以使用纯SQL。

INSERT INTO oo_thistable SELECT object1_t(column1, column2, column3) FROM thistable;

演示

创建所需的类型和表:

SQL> create table thisTable(
  2      column1 varchar2(20),
  3      column2 varchar2(20),
  4      column3 varchar2(20)
  5  )
  6  /

Table created.

SQL> create type object1_t as object (
  2      column1 varchar2(20),
  3      column2 varchar2(20),
  4      column3 varchar2(20)
  5  )
  6  /

Type created.

SQL> create table oo_thisTable(
  2      object1 object1_t
  3  )
  4  /

Table created.

在thistable中插入几行:

SQL> INSERT INTO thistable VALUES('a','b','c');

1 row created.

SQL> INSERT INTO thistable VALUES('d','e','f');

1 row created.

SQL> INSERT INTO thistable VALUES('g','h','i');

1 row created.

现在我们要将 thistable 中的所有行插入到 oo_thistable 中:

SQL> INSERT INTO oo_thistable SELECT object1_t(column1, column2, column3) FROM thistable;

3 rows created.

验证:

SQL> SELECT * FROM oo_thistable;

OBJECT1(COLUMN1, COLUMN2, COLUMN3)
--------------------------------------------------------------------------------
OBJECT1_T('a', 'b', 'c')
OBJECT1_T('d', 'e', 'f')
OBJECT1_T('g', 'h', 'i')

您已插入所有行。