[转]FOR ALL语法浅析

   同事问了我一个问题，使用FOR ALL和FOR LOOP的区别到底是什么。以前也一直没有深究这个问题，唯一清楚的是FOR ALL是一个批量操作，效率明显比FOR LOOP的方式要高，至于FOR ALL到底是一个SQL语句，还是将多个SQL语句同时提交给ORACLE还真不太清楚。


   于是，首先做了个简单的例子，但是通过触发器来观察二者的区别：

SQL> CREATE TABLE TTT (ID NUMBER);

表已创建。

SQL> CREATE OR REPLACE TRIGGER TRI_TTT BEFORE INSERT ON TTT
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('A');
4 END;
5 /

触发器已创建

SQL> SET SERVEROUT ON
SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FOR I IN 1..V_NUM.COUNT LOOP
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END LOOP;
9 END;
10 /
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A

PL/SQL 过程已成功完成。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FORALL I IN 1..V_NUM.COUNT
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END;
9 /
A

PL/SQL 过程已成功完成。

从触发器的除非动作上可以看出，FOR ALL语法和FOR LOOP的区别，FOR ALL对INSERT语句只调用了一次。

通过SQL_TRACE的方式也可以清楚的看到这一点：

SQL> DROP TRIGGER TRI_TTT;

触发器已删除。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FORALL I IN 1..V_NUM.COUNT
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END;
9 /

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FOR I IN 1..V_NUM.COUNT LOOP
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END LOOP;
9 END;
10 /

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

SQL> HOST TKPROF E:ORACLEADMINYTKUDUMPYTK_ORA_3964.TRC E:REPORT.TXT SYS=NO AGGREGATE=NO

其中FOR ALL语句对应的INSERT语句为：

INSERT INTO TTT
VALUES
(:B1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 5 16
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ---------
total 2 0.00 0.00 0 1 5 16

而FOR LOOP对应的INSERT语句为：

INSERT INTO TTT
VALUES
(:B1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
Parse 1 0.00 0.00 0 0 0 0
Execute 16 0.00 0.06 4 1 20 16
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ---------
total 17 0.00 0.06 4 1 20 16

从上面的TKPROF可以更清楚的看出，对于FOR ALL语法，INSERT语句只是执行了一次，但是产生了16条记录。从这一点上，FOR ALL语法于INSERT INTO SELECT语法更为相似。但FOR ALL又和INSERT INTO SELECT有着本质的区别：

SQL> TRUNCATE TABLE TTT;

表被截断。

SQL> ALTER TABLE TTT MODIFY ID NUMBER(3);

表已更改。

SQL> INSERT INTO TTT SELECT 994 + ROWNUM FROM TAB;
INSERT INTO TTT SELECT 994 + ROWNUM FROM TAB
*第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度


SQL> SELECT * FROM TTT;

未选定行

INSERT INTO SELECT语法属于一条语句，根据Oracle的语句级回滚，当插入由于个别数据发生错误的时候，整个插入语句被回滚。

但对于FOR ALL语句，虽然Oracle只执行了INSERT语句一次，但是，如果发生了错误，是可以捕获的，且错误发生之间的操作是可以保留下来的。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 V_NUM(5) := 1000;
7 BEGIN
8 FORALL I IN 1..V_NUM.COUNT
9 INSERT INTO TTT VALUES (V_NUM(I));
10 EXCEPTION
11 WHEN OTHERS THEN
12 COMMIT;
13 END;
14 END;
15 /

PL/SQL 过程已成功完成。

SQL> SELECT * FROM TTT;

ID
----------
1
2
3
4

    从这一点看，FOR ALL语法和INSERT INTO SELECT又有着本质的区别。个人感觉FOR ALL语法和Oracle的OCI中数组绑定语法十分类似。二者都采用数据绑定变量的方式，通过调用一次SQL，将整个数组的内容提交给Oracle，并且出现错误后，可以通过捕获错误的方式保留出错前已经进行的修改。

    个人认为，FOR ALL语法和OCI的数组绑定具有相同的内部机制。二者分别为PL/SQL和OCI提供了相同的批量处理功能。

 

________________________________________________________________________________________________

全文完

URL：http://yangtingkun.itpub.net/post/468/198828