insert all 的好处

例子子，从一张表里面读取数据插入到两张表；

1、最普通的方法是两个INSERT INTO SELECT语句。这种方法编码最简单，但是存在着两次插入的数据不一致的情况。如果要解决这个问题，必须通过人为加锁的方式，这样又会影响并发性。这样涉及到读两次的数据不一样

2、还有一种方式是通过临时表的方式。第一次将数据放到临时表中，然后通过临时表把数据分别插入目标表和日志表。这种方法虽然解决了并发性问题，但是效率比较低。相同的数据需要查询三次，插入三次。

3、如果数据量不大的话，还可以考虑使用SELECT BULK COLLECT INTO和FOR ALL INSERT语句配合。如果数据量比较大的话，可以考虑在上面的基础上加上LIMIT语句限制一次处理的数据量大小。这种方法不但解决了并发性而且只需要读取一次插入两次，执行效率相对比较高。唯一的缺点是，需要将数据放到内存的变量中，不但需要额外的内存空间，而且这种数据在内存中的中转必然要比数据从源表直接插入到目标表效率要低一些。而且这种方法需要的编码量相对较大。

4、最后想到了使用INSERT ALL语法。INSERT ALL语法是9i的新功能，使用INSERT ALL语法可以说是解决这个方法的最佳途径了，只需要读取一次，就可以完成两次插入，没有并发性问题，不需要额外的存储空间，编码简单，只需要一条SQL语句就可以搞定。

问题是：INSERT ALL的子查询中不支持序列

下面是个例子：

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> 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行。

 

发现ID 错位了

SQL> SELECT * FROM A;

ID NAME
---------- ------------------------------
1 DEPT
3 EMP
5 BONUS
7 SALGRADE
9 DUMMY

SQL> SELECT * FROM LOG_A;

ID NAME
---------- ------------------------------
2 DEPT
4 EMP
6 BONUS
8 SALGRADE
10 DUMMY

感觉上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;

 

这次执行的结果是正确的。Tom在他的书中描述过ROWNUM的确定结果集的功能，也就是说受到ROWNUM的影响，ORACLE将处理流程变成了

由于存在ROWNUM，Oracle在执行查询的时候就运行了F_GETSEQ函数，因此F_GETSET函数对于每条记录只在查询的时候执行一次。

如果将函数改写一下，将ROWNUM作为输入参数，一样可以解决这个问题。

SQL> CREATE OR REPLACE FUNCTION F_GETSEQ (P_IN IN NUMBER) 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 /

函数已创建。

 

除了上面描述的方法，如果是Oracle10g的话，还可以建立一个DETERMINISTIC的函数。在10g中Oracle完全信任DETERMINISTIC声明，对于相同的输入，会采用相同的输出，而不去真正的执行函数。

例如，在9i下执行：

SQL> CREATE OR REPLACE FUNCTION F_GETSEQ RETURN NUMBER DETERMINISTIC 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.

 

还有简单是采用临时的视图，避免了序列调了两次

INSERT all
INTO A(ID, NAME) values(id,tname)
INTO log_A(ID, NAME) values(id,tname)
with s as (
SELECT F_GETSEQ ID, TNAME FROM TAB
) select * from s