insert all语句用法详解(二)
CT 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函数对于每条记录只在查询的时候执行一次。
如果将函数改写一下，将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 /
函数已创建。
SQL> INSERT ALL INTO A (ID, NAME) VALUES (ID, TNAME)
2 INTO LOG_A (ID, NAME) VALUES (ID, TNAME)
3 SELECT F_GETSEQ(ROWNUM) ID, TNAME FROM TAB;
已创建48行。
SQL> SELECT * FROM A WHERE ROWNUM < 5;
ID NAME
---------- ------------------------------
73 DEPT
74 EMP
75 BONUS
76 SALGRADE
SQL> SELECT * FROM LOG_A WHERE ROWNUM < 5;
ID NAME
---------- ------------------------------
73 DEPT
74 EMP
75 BONUS
76 SALGRADE
SQL> ROLLBACK;
回退已完成。
除了上面描述的方法，如果是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.
SQL> SELECT F_GETSEQ FROM TAB;
F_GETSEQ
----------
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
25 rows selected.
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
而10g中，上面的查询变成了：
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 /
函数已创建。
SQL> SELECT F_GETSEQ FROM TAB;
F_GETSEQ
----------
97
97
97