有一个需求:将一个含多个clob的表,分别插入到两个表中(一个是不含clob的表,一个是含clob的表)。insert all此时派上用场了。
create table test
(id number,
col1 number,
col2 number,
col3 number,
col4 number
);
create table test1
(
id number,
col1 number,
col2 number
);
create table test2
(
id number,
col3 number
);
insert into test values(1,1,1,1,1);
insert into test values(2,2,2,2,2);
insert into test values(3,3,3,3,3);
insert into test values(4,4,4,4,4);
commit;
insert all
into test1(id,col1,col2) values(id,col1,col2)
into test2(id,col3) values(id,col3)
select * from test;
commit;
SQL> select * from test;
ID COL1 COL2 COL3 COL4
--- ----- ----- ----- -----
1a 1b 1c 1d 1e
2a 2b 2c 2d 2e
3a 3b 3c 3d 3e
4a 4b 4c 4d 4e
SQL> select * from test1;
ID COL1 COL2
---- ----- -----
1a 1b 1c
2a 2b 2c
3a 3b 3c
4a 4b 4c
SQL> select * from test2;
ID COL3
---- -----
1a 1d
2a 2d
3a 3d
4a 4d