--创建测试表并插入数据
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语句