理解insert all/insert first的使用

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

在常用的SQL写法中我们会经常遇到把一个表的数据插入另外一张表的情况，这是一个insert into 表名 select .... from 表名就可以解决了。但是如果是把一张表的数据同时插入两张表或两张以上的表该怎么办？你是不是已经想到了办法了，使用多个insert into
语句，例如：

insert into test1 select * from test;
insert into test2 select * from test;
......
commit;

通过把多张表的插入脚本放入一个事务中解决(如上)，但是这样做的结果有可能是test1表和test2表结果集不一样，因为这个事务中插入的源数据表test被读取了两次，在一个表数据变化较快的情况下，两次读取的数据可能会不一样,这就违反了我们之前的需求。如何解决?? 这种情况下我们可以把原表数据预先读到的一个全局临时表里，然后再从临时表里读出数据插入多个目的表，当然，引入我们本文的话题，使用insert all更为方便解决。

insert all分为无条件插入和有条件插入，在有条件插入的情况下还可以使用insert first,他与insert all会有一点小区别，在下面的例子中我们将会介绍。

1，insert all无条件插入

---构造一个环境

SQL> drop table test;Table droppedSQL> drop table test1;Table droppedSQL> drop table test2;Table droppedSQL> create table test as select deptno,dname from dept;Table createdSQL> create table test1 as select * from test where =;Table createdSQL> create table test2 as select * from test where =;Table createdSQL> select count(*) from test; COUNT(*)
----------SQL> select count(*) from test1; COUNT(*)
----------SQL> select count(*) from test1; COUNT(*)
----------

--插入数据

SQL> insert all
into test1(deptno,dname)
into test2(deptno,dname)
select deptno,dname from test; rows insertedSQL> select count(*) from test1; COUNT(*)
----------SQL> select count(*) from test2; COUNT(*)
----------SQL> commit;Commit complete

可以看到我们使用一个SQL语句实现了插入了多张表数据，而且这种方式要比写多个insert into 语句效率要高。在上面的SQL中不论插入多少张表，test表只会被读取一次。

2，带条件的insert all插入

SQL> select * from test;DEPTNO DNAME
------ --------------
ACCOUNTING
RESEARCH
SALES
OPERATIONSSQL> truncate table test1;Table truncatedSQL> truncate table test2;Table truncatedSQL> insert all
when deptno < then
into test1(deptno,dname)
when deptno < then
into test2(deptno,dname)
select deptno,dname from test; rows insertedSQL> select * from test1;DEPTNO DNAME
------ --------------
ACCOUNTING
RESEARCHSQL> select * from test2;DEPTNO DNAME
------ --------------
ACCOUNTING
RESEARCH
SALES
OPERATIONSSQL> commit;Commit complete

从上面的SQL中我们可以当有带条件的插入后，因为test1和test2表对应的条件不同，插入到两张表的数据也不同，这说明插入条件起来作用。test1的条件是部门号小于30的放入test1表，test2的条件是部门号小于50的放入test2表，从test表中查出的4条数据，每条数据都经过了这两个条件的判断(过滤)。

3，带条件的insert fist插入

SQL> truncate table test1;Table truncatedSQL> truncate table test2;Table truncatedSQL> select * from test;DEPTNO DNAME
------ --------------
ACCOUNTING
RESEARCH
SALES
OPERATIONSSQL> insert first
when deptno < then
into test1(deptno,dname)
when deptno < then
into test2(deptno,dname)
select deptno,dname from test; rows insertedSQL> select * from test1;DEPTNO DNAME
------ --------------
ACCOUNTING
RESEARCHSQL> select * from test2;DEPTNO DNAME
------ --------------
SALES
OPERATIONSSQL> commit;Commit complete

从test1和test2的结果输出我们会发现test1数据是正常的，而test2貌似数据少了，因为部门10、部门20也符合 deptno<50的条件，为什么没有插入进去那？其实不然，insert first是考虑先后关系的，如果有数据满足第一个when条件又满足第二个when条件，则执行第一个then插入语句，第二个then就不插入第一个then已经插入过的数据了。反之有数据不满足第一个when条件且满足第二个when条件,则数据会插入第二个条件下对应的表中，这也正是insert first与inset all的区别。

简单来说就是all只要满足条件，就会插入；first只要有一个满足条件，后面的条件不再判断。

注意：insert all 无法支持序列插入，会导致两边不一致,举例如下：

SQL> truncate table test1;Table truncatedSQL> truncate table test2;Table truncatedSQL> create sequence seq_test;Sequence createdSQL> insert all
into test1(deptno,dname)
into test2(deptno,dname)
select seq_test.nextval deptno,dname from test;insert all
into test1(deptno,dname)
into test2(deptno,dname)
select seq_test.nextval deptno,dname from testORA-: 此处不允许序号 --不能直接使用序列

SQL> CREATE OR REPLACE FUNCTION F_SEQ RETURN NUMBER AS
V_SEQ NUMBER;
BEGIN
SELECT SEQ_TEST.NEXTVAL INTO V_SEQ FROM DUAL;
RETURN V_SEQ;
END;
/Function createdSQL> insert all
into test1(deptno,dname)
into test2(deptno,dname)
select f_seq deptno,dname from test; rows insertedSQL> commit;Commit completeSQL> select * from test1;DEPTNO DNAME
------ --------------
ACCOUNTING
RESEARCH
SALES
OPERATIONSSQL> select * from test2;DEPTNO DNAME
------ --------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS