理解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 dropped
 
SQL> drop table test1;
 
Table dropped
 
SQL> drop table test2;
 
Table dropped
 
SQL> create table test as select deptno,dname from dept;
 
Table created
 
SQL> create table test1 as select * from test where 1=2;
 
Table created
 
SQL> create table test2 as select * from test where 1=2;
 
Table created
 
SQL> select count(*) from test;
 
  COUNT(*)
----------
         4
 
SQL> select count(*) from test1;
 
  COUNT(*)
----------
         0
 
SQL> select count(*) from test1;
 
  COUNT(*)
----------
         0

 

--插入数据

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

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

 

 

2，带条件的insert all插入

 

SQL> select * from test;
 
DEPTNO DNAME
------ --------------
    10 ACCOUNTING
    20 RESEARCH
    30 SALES
    40 OPERATIONS
 
SQL> truncate table test1;
 
Table truncated
 
SQL> truncate table test2;
 
Table truncated
 
SQL> insert all
  2  when deptno <30 then
  3  into test1(deptno,dname)
  4  when deptno <50 then
  5  into test2(deptno,dname)
  6  select deptno,dname from test;
 
6 rows inserted
 
SQL> select * from test1;
 
DEPTNO DNAME
------ --------------
    10 ACCOUNTING
    20 RESEARCH
 
SQL> select * from test2;
 
DEPTNO DNAME
------ --------------
    10 ACCOUNTING
    20 RESEARCH
    30 SALES
    40 OPERATIONS
 
SQL> commit;
 
Commit complete

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

 

3，带条件的insert fist插入

 

SQL> truncate table test1;
 
Table truncated
 
SQL> truncate table test2;
 
Table truncated
 
SQL> select * from test;
 
DEPTNO DNAME
------ --------------
    10 ACCOUNTING
    20 RESEARCH
    30 SALES
    40 OPERATIONS
 
SQL> insert first
  2  when deptno <30 then
  3  into test1(deptno,dname)
  4  when deptno <50 then
  5  into test2(deptno,dname)
  6  select deptno,dname from test;
 
4 rows inserted
 
SQL> select * from test1;
 
DEPTNO DNAME
------ --------------
    10 ACCOUNTING
    20 RESEARCH
 
SQL> select * from test2;
 
DEPTNO DNAME
------ --------------
    30 SALES
    40 OPERATIONS
 
SQL> 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 truncated
 
SQL> truncate table test2;
 
Table truncated
 
SQL> create sequence seq_test;
 
Sequence created
 
SQL> insert all
  2  into test1(deptno,dname)
  3  into test2(deptno,dname)
  4  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 test
 
ORA-02287: 此处不允许序号      --不能直接使用序列

 

SQL> CREATE OR REPLACE FUNCTION F_SEQ RETURN NUMBER AS
  2   V_SEQ NUMBER;
  3   BEGIN
  4   SELECT SEQ_TEST.NEXTVAL INTO V_SEQ FROM DUAL;
  5   RETURN V_SEQ;
  6   END;
  7  /
 
Function created
 
SQL> insert all
  2  into test1(deptno,dname)
  3  into test2(deptno,dname)
  4  select f_seq deptno,dname from test;
 
8 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test1;
 
DEPTNO DNAME
------ --------------
     1 ACCOUNTING
     3 RESEARCH
     5 SALES
     7 OPERATIONS
 
SQL> select * from test2;
 
DEPTNO DNAME
------ --------------
     2 ACCOUNTING
     4 RESEARCH
     6 SALES
     8 OPERATIONS

  从上面的SQL结果中我们可以看到test1和test2表deptno值出现了不一样,这就是说insert all 无法支持序列插入，会导致两边不一致。

 

 

 

可参考：

http://blog.itpub.net/29196873/viewspace-1122075/

http://blog.csdn.net/ghostgant/article/details/5700228