INSERT FIRST和INSERT ALL的区别

--创建测试表并插入数据
SQL> create table test_1
  2  (
  3    c1 varchar2(10),
  4    c2 varchar2(10)
  5  )
  6  ;
 
Table created
 
SQL>
SQL> create table test_2
  2  (
  3    c1 varchar2(10),
  4    c2 varchar2(10)
  5  )
  6  ;
 
Table created
 
SQL>
SQL> create table test_3
  2  (
  3    c1 varchar2(10),
  4    c2 varchar2(10)
  5  )
  6  ;
 
Table created
 
SQL> insert into test_1 values(10,20);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
--测试insert first
SQL> insert first
  2  when c1=10 then
  3    into test_2 values(c1,c2)
  4  when c2=20 then
  5   into test_3 values(c1,c2)
  6   select * from test_1;
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_2;
 
C1       C2
---------- ----------
10       20
 
SQL> select * from test_3;
 
C1       C2
---------- ----------

--通过上面的例子可以看到，虽然test_1表里面的记录同时满足insert first
--中的两个条件，但是只执行了第一个条件下的into语句，也就是说insert --first针对一条记录，检查到满足的第一个条件就停止，不会检查以后的

--条件
 
--测试insert ALL
 
SQL> delete from test_2;
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL>
SQL> insert ALL
  2  when c1=10 then
  3    into test_2 values(c1,c2)
  4  when c2=20 then
  5   into test_3 values(c1,c2)
  6   select * from test_1;
 
2 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_2;
 
C1       C2
---------- ----------
10       20
 
SQL> select * from test_3;
 
C1       C2
---------- ----------
10       20
 
SQL> delete from test_2;
 
1 row deleted
 
SQL> delete from test_3;
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL>
SQL> create table test_4
  2  (
  3    c1 varchar2(10),
  4    c2 varchar2(10)
  5  )
  6  ;
 
Table created
 
SQL>
SQL> insert ALL
  2  WHEN c1=30 THEN
  3      into test_4 values(c1,c2)
  4  when c1=10 then
  5    into test_2 values(c1,c2)
  6  when c2=20 then
  7   into test_3 values(c1,c2)
  8   select * from test_1;
 
2 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_4;
 
C1       C2
---------- ----------
 
SQL> select * from test_3;
 
C1       C2
---------- ----------
10       20
 
SQL> select * from test_2;
 
C1       C2
---------- ----------
10       20
 
SQL> 

--通过上面的例子可以看到，insert all会针对一条记录，检查所有的条

--件，如果条件满足，就执行该条件下的into语句