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