【转】insert first&insert all的区别 ---OCP--047--170
insert first/all
利用insert first/all使得INSERT语句可以同时插入多张表,还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。
insert first/all语法
[ ALL | FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]
insert first/all 是对每一行来进行判断
两者区别:
insert first:对于每一行数据,只插入到第一个when条件成立的表,不继续检查其他条件。
insert all :对于每一行数据,对每一个when条件都进行检查,如果满足条件就执行插入操作。
create table test_a(a number,b number); create table b as select * from test_a; create table c as select * from test_a; insert into test_a values(1,0); insert into test_a values(1,1); insert into test_a values(1,2); insert into test_a values(2,0); insert into test_a values(3,0); SQL> select * from test_a; A B ---------- ---------- 1 0 1 1 1 2 2 0 3 0 现分别对insert first和insert all语句进行简单测试:(暂不考虑SQL是否具有真实作用) SQL> insert all 2 when a=1 then into b 3 when a=1 then into c 4 select * from test_a; 6 rows created. Elapsed: 00:00:00.01 SQL> select * from b; A B ---------- ---------- 1 0 1 1 1 2 3 rows selected. Elapsed: 00:00:00.00 SQL> select * from c; A B ---------- ---------- 1 0 1 1 1 2 3 rows selected. Elapsed: 00:00:00.01 SQL> rollback; Rollback complete. Elapsed: 00:00:00.01 SQL> select * from b; no rows selected Elapsed: 00:00:00.00 SQL> select * from c; no rows selected Elapsed: 00:00:00.00 SQL> insert first 2 when a=1 then into b 3 when a=1 then into c 4 select * from test_a; 3 rows created. Elapsed: 00:00:00.00 SQL> SQL> select * from b; A B ---------- ---------- 1 0 1 1 1 2 3 rows selected. Elapsed: 00:00:00.00 SQL> select * from c; no rows selected Elapsed: 00:00:00.00