insert all语句用法详解


insert all语句用法详解
 
首先描述一下需求，其实要进行的操作很简单：从一张表取数据插入到另一张表中，插入的目标表做了一个应用系统级的日志表，也就是说在插入目标表的同时，还需要将相同的数据插入到日志表中。
 
这个操作其实并不复杂，但是想找到一个效率最高、并发性最好的方法并不容易。
 
最普通的方法是两个INSERT INTO SELECT语句。这种方法编码最简单，但是存在着两次插入的数据不一致的情况。如果要解决这个问题，必须通过人为加锁的方式，这样又会影响并发性。
 
还有一种方式是通过临时表的方式。第一次将数据放到临时表中，然后通过临时表把数据分别插入目标表和日志表。这种方法虽然解决了并发性问题，但是效率比较低。相同的数据需要查询三次，插入三次。
 
PL/SQL的语法RETURNING语句其实很适合这种情况，可惜的是RETURNING语句只支持INSERT INTO VALUES语句，不支持INSERT INTO SELECT语句。
 
如果数据量不大的话，还可以考虑使用SELECT BULK COLLECT INTO和FOR ALL INSERT语句配合。如果数据量比较大的话，可以考虑在上面的基础上加上LIMIT语句限制一次处理的数据量大小。这种方法不但解决了并发性而且只需要读取一次插入两次，执行效率相对比较高。唯一的缺点是，需要将数据放到内存的变量中，不但需要额外的内存空间，而且这种数据在内存中的中转必然要比数据从源表直接插入到目标表效率要低一些。而且这种方法需要的编码量相对较大。
 
最后想到了使用INSERT ALL语法。INSERT ALL语法是9i的新功能，使用INSERT ALL语法可以说是解决这个方法的最佳途径了，只需要读取一次，就可以完成两次插入，没有并发性问题，不需要额外的存储空间，编码简单，只需要一条SQL语句就可以搞定。
 
从上面种种方面看，INSERT ALL语句简直就是这个问题的完美解决方案，但是问题才刚刚开始。
 
首先，碰到的第一个问题就是，INSERT ALL的子查询中不支持序列。而在将源表数据插入到目标表的过程中需要使用序列来构造ID。
 
不过这个问题被我们通过建立函数的方法绕过去了。
 
下面这个例子简单描述了这种情况：
 
SQL> CREATE TABLE A (ID NUMBER, NAME VARCHAR2(30));
 
表已创建。
 
SQL> CREATE TABLE LOG_A (ID NUMBER, NAME VARCHAR2(30));
 
表已创建。
 
SQL> CREATE SEQUENCE SEQ_TEST;
 
序列已创建。
 
SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME) 
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME) 
3 SELECT SEQ_TEST.NEXTVAL ID, TNAME FROM TAB;
SELECT SEQ_TEST.NEXTVAL ID, TNAME FROM TAB
*第 3 行出现错误:
ORA-02287: 此处不允许序号
 
Oracle的文档上也明确描述了不能在子查询中使用序列，但是通过测试发现，如果将序列封装在函数中是可以骗过Oracle的。
 
SQL> CREATE OR REPLACE FUNCTION F_GETSEQ 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 /
 
函数已创建。
 
SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME) 
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME) 
3 SELECT F_GETSEQ ID, TNAME FROM TAB;
 
已创建48行。
 
问题似乎解决了，但是更大的问题出现了，观察A表和LOG_A表发现居然得到的结果是不一样的：
 
SQL> SELECT * FROM A;
 
ID NAME
---------- ------------------------------
1 DEPT
3 EMP
5 BONUS
7 SALGRADE
9 DUMMY
11 TEST
13 DOCS
15 DR$MYINDEX$I
17 DR$MYINDEX$K
19 DR$MYINDEX$R
21 DR$MYINDEX$N
23 TEST_CLOB
25 FACT
27 MLOG$_DIM_A
29 MLOG$_DIM_B
31 MLOG$_FACT
33 MV_FACT
35 MLOG$_MV_FACT
37 RUPD$_MV_FACT
39 A
41 LOG_A
43 TEST_TAB
45 DIM_A
47 DIM_B
 
已选择24行。
 
SQL> SELECT * FROM LOG_A;
 
ID NAME
---------- ------------------------------
2 DEPT
4 EMP
6 BONUS
8 SALGRADE
10 DUMMY
12 TEST
14 DOCS
16 DR$MYINDEX$I
18 DR$MYINDEX$K
20 DR$MYINDEX$R
22 DR$MYINDEX$N
24 TEST_CLOB
26 FACT
28 MLOG$_DIM_A
30 MLOG$_DIM_B
32 MLOG$_FACT
34 MV_FACT
36 MLOG$_MV_FACT
38 RUPD$_MV_FACT
40 A
42 LOG_A
44 TEST_TAB
46 DIM_A
48 DIM_B
 
已选择24行。
 
SQL> ROLLBACK;
 
回退已完成。
 
感觉上Oracle居然似乎对源表进行了两次查询。但是从数据的分布情况上看又不像。个人感觉Oracle对于每条记录似乎是将取序列的函数执行了两次。
 
操作流程类似于
 
FOR ALL ROWID IN TAB LOOP
 
SELECT TNAME FROM TAB WHERE ROWID =:1;
 
INSERT INTO A (F_GETSEQ, TNAME);
 
INSERT INTO LOG_A (F_GETSEQ, TNAME);
 
END LOOP;
 
而同事又有了另一个发现，当包含了ROWNUM列时，得到的结果是正确的：
 
SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME) 
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME) 
3 SELECT ROWNUM RN, F_GETSEQ ID, TNAME FROM TAB;
 
已创建48行。
 
SQL> SELECT * FROM A;
 
ID NAME
---------- ------------------------------
49 DEPT
50 EMP
51 BONUS
52 SALGRADE
53 DUMMY
54 TEST
55 DOCS
56 DR$MYINDEX$I
57 DR$MYINDEX$K
58 DR$MYINDEX$R
59 DR$MYINDEX$N
60 TEST_CLOB
61 FACT
62 MLOG$_DIM_A
63 MLOG$_DIM_B
64 MLOG$_FACT
65 MV_FACT
66 MLOG$_MV_FACT
67 RUPD$_MV_FACT
68 A
69 LOG_A
70 TEST_TAB
71 DIM_A
72 DIM_B
 
已选择24行。
 
SQL> SELECT * FROM LOG_A;
 
ID NAME
---------- ------------------------------
49 DEPT
50 EMP
51 BONUS
52 SALGRADE
53 DUMMY
54 TEST
55 DOCS
56 DR$MYINDEX$I
57 DR$MYINDEX$K
58 DR$MYINDEX$R
59 DR$MYINDEX$N
60 TEST_CLOB
61 FACT
62 MLOG$_DIM_A
63 MLOG$_DIM_B
64 MLOG$_FACT
65 MV_FACT
66 MLOG$_MV_FACT
67 RUPD$_MV_FACT
68 A
69 LOG_A
70 TEST_TAB
71 DIM_A
72 DIM_B
 
已选择24行。
 
SQL> ROLLBACK;
 
回退已完成。
 
这次执行的结果是正确的。Tom在他的书中描述过ROWNUM的确定结果集的功能，也就是说受到ROWNUM的影响，ORACLE将处理流程变成了
 
FOR ALL ROWID IN TAB LOOP
 
SELECT ROWNUM RN, F_GETSEQ ID, TNAME FROM TAB WHERE ROWID =:1;
 
INSERT INTO A (ID, TNAME);
 
INSERT INTO LOG_A (ID, TNAME);
 
END LOOP;
 
由于存在ROWNUM，Oracle在执行查询的时候就运行了F_GETSEQ函数，因此F_GETSET函数对于每条记录只在查询的时候执行一次。<