insert all:不管是条件一还是条件几,只要符合就全部插入,else的结果,根据else上一个条件处理
insert first:顾名思义,first,符合第一个条件的数据集不会再去匹配第二个条件,else的结果,根据else上一个条件处理
测试实验:
测试表:data_test、a_test、b_test、c_test
create table data_test(id number(4),name varchar2(12));
insert into data_test values(1,'a');
insert into data_test values(2,'b');
insert into data_test values(3,'c');
insert into data_test values(4,'d');
insert into data_test values(5,'e');
insert into data_test values(6,'f');
insert into data_test values(7,'g');
insert into data_test values(8,'h');
insert into data_test values(9,'i');
create table a_test as select * from data_test where 1=0;
create table b_test as select * from data_test where 1=0;
create table c_test as select * from data_test where 1=0;
SQL> select * from data_test;
ID NAME
----- ------------
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
9 rows selected
ID NAME
----- ------------
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
9 rows selected
insert all 测试:
SQL> insert all
2 when (id<5 and id>2) then
3 into a_test
4 when (id<4) then
5 into b_test
6 else
7 into c_test
8 select * from data_test;
10 rows inserted
2 when (id<5 and id>2) then
3 into a_test
4 when (id<4) then
5 into b_test
6 else
7 into c_test
8 select * from data_test;
10 rows inserted
根据条件可知,插入a_test的是id为3、4这两条记录,b_test的是id为1、2、3这三条记录,插入c_test的是id为5、6、7、8、9
验证一下:
SQL> select * from a_test;
ID NAME
----- ------------
3 c
4 d
SQL> select * from b_test;
ID NAME
----- ------------
1 a
2 b
3 c
SQL> select * from c_test;
ID NAME
----- ------------
5 e
6 f
7 g
8 h
9 i
ID NAME
----- ------------
3 c
4 d
SQL> select * from b_test;
ID NAME
----- ------------
1 a
2 b
3 c
SQL> select * from c_test;
ID NAME
----- ------------
5 e
6 f
7 g
8 h
9 i
insert first测试:
SQL> insert first
2 when (id<5 and id>2) then
3 into a_test
4 when (id<4) then
5 into b_test
6 else
7 into c_test
8 select * from data_test;
2 when (id<5 and id>2) then
3 into a_test
4 when (id<4) then
5 into b_test
6 else
7 into c_test
8 select * from data_test;
根据条件可以知道,插入a_test的应该是id为3,4这两条记录,b_test 的应该是id为1,2这两条记录,而c_test的应该是id为5,6,7,8,9这五条记录
验证一下:
SQL> select * from a_test;
ID NAME
----- ------------
3 c
4 d
SQL> select * from b_test;
ID NAME
----- ------------
1 a
2 b
SQL> select * from c_test;
ID NAME
----- ------------
5 e
6 f
7 g
8 h
9 i
据说insert all 还能实现行转列的功能(旋转插入),待以后实验再加入
ID NAME
----- ------------
3 c
4 d
SQL> select * from b_test;
ID NAME
----- ------------
1 a
2 b
SQL> select * from c_test;
ID NAME
----- ------------
5 e
6 f
7 g
8 h
9 i
据说insert all 还能实现行转列的功能(旋转插入),待以后实验再加入
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26425571/viewspace-740617/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26425571/viewspace-740617/