FORALL執行UPDATE語句

以前研究過FORALL中的INSERT語句，發現語句透過繫結陣列的方式，實現了批次繫結，一次執行的方式，從而提高了執行的效率。

但是對於UPDATE語句而言，Oracle的實現和INSERT是不同的：

SQL> CREATE TABLE T AS SELECT ROWNUM ID FROM TAB;

表已建立。

SQL> SELECT * FROM T;

        ID
----------
         1
         2
         3
         4
         5
         6

已選擇6行。

SQL> DECLARE
  2   TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3   V_ID T_ID;
  4  BEGIN
  5   SELECT ID
  6   BULK COLLECT INTO V_ID
  7   FROM T;
  8   FORALL I IN V_ID.FIRST..V_ID.LAST
  9    UPDATE T
 10    SET ID = V_ID(I);
 11  END;
 12  /

PL/SQL 過程已成功完成。

SQL> SELECT * FROM T;

        ID
----------
         6
         6
         6
         6
         6
         6

已選擇6行。

SQL> ROLLBACK;

回退已完成。

結果並不像想象中的，仍然是123456，而是所有的記錄都被更新為6。顯然Oracle不太可能只使用陣列變數在中最後一個值進行更新，因此T表中的記錄應該是被更新了6次，所以對於UPDATE語句而言，繫結陣列中有多少個值，更新就執行了多少次，而對於當前沒有指定WHERE條件，所以每次執行的時候所有的記錄都被更新。

正確的寫法類似於：

SQL> DECLARE
  2   TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3   V_ID T_ID;
  4  BEGIN
  5   SELECT ID
  6   BULK COLLECT INTO V_ID
  7   FROM T;
  8   FORALL I IN V_ID.FIRST..V_ID.LAST
  9    UPDATE T
 10    SET ID = V_ID(I)
 11    WHERE ID = V_ID(I);
 12  END;
 13  /

PL/SQL 過程已成功完成。

SQL> SELECT * FROM T;

        ID
----------
         1
         2
         3
         4
         5
         6

已選擇6行。

下面驗證一下剛才的分析是否準確：

SQL> CREATE OR REPLACE TRIGGER T_STATEMENT
  2  BEFORE UPDATE ON T
  3  BEGIN
  4   DBMS_OUTPUT.PUT_LINE('STATEMENT');
  5  END;
  6  /

觸發器已建立

SQL> CREATE OR REPLACE TRIGGER T_ROW
  2  BEFORE UPDATE ON T
  3  FOR EACH ROW
  4  BEGIN
  5   DBMS_OUTPUT.PUT_LINE('ROW:' || :OLD.ID);
  6  END;
  7  /

觸發器已建立

SQL> SET SERVEROUT ON

SQL> DECLARE
  2   TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3   V_ID T_ID;
  4  BEGIN
  5   SELECT ID
  6   BULK COLLECT INTO V_ID
  7   FROM T;
  8   FORALL I IN V_ID.FIRST..V_ID.LAST
  9    UPDATE T
 10    SET ID = V_ID(I)
 11    WHERE ID = V_ID(I);
 12  END;
 13  /

STATEMENT
ROW:1
STATEMENT
ROW:2
STATEMENT
ROW:3
STATEMENT
ROW:4
STATEMENT
ROW:5
STATEMENT
ROW:6

PL/SQL 過程已成功完成。

SQL> DECLARE
  2   TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3   V_ID T_ID;
  4  BEGIN
  5   SELECT ID
  6   BULK COLLECT INTO V_ID
  7   FROM T;
  8   FORALL I IN V_ID.FIRST..V_ID.LAST
  9    UPDATE T
 10    SET ID = V_ID(I);
 11  END;
 12  /

STATEMENT
ROW:1
ROW:2
ROW:3
ROW:4
ROW:5
ROW:6
STATEMENT
ROW:1
ROW:1
ROW:1
ROW:1
ROW:1
ROW:1
STATEMENT
ROW:2
ROW:2
ROW:2
ROW:2
ROW:2
ROW:2
STATEMENT
ROW:3
ROW:3
ROW:3
ROW:3
ROW:3
ROW:3
STATEMENT
ROW:4
ROW:4
ROW:4
ROW:4
ROW:4
ROW:4
STATEMENT
ROW:5
ROW:5
ROW:5
ROW:5
ROW:5
ROW:5

PL/SQL 過程已成功完成。

對於FORALL中的UPDATE語句，執行的次數由FORALL語句中陣列的長度決定，這與INSERT語句只執行一次是有明顯不同的。