【转】insert first&insert all的区别
insert first/all利用insert first/all使得INSERT语句可以同时插入多张表，还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。
insert first/all语法
[ ALL | FIRST ]
WHEN condition THEN insert_into_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
2when a=1 theninto b
3when a=1 theninto c
4select * 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
2when a=1 theninto b
3when a=1 theninto c
4select * 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
页:
[1]