Oracle中FORALL執行UPDATE語句

在ITPUB上看到一個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

返回欄目頁：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

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語句只運行一次是有明顯不同的。

作者：51cto部落格 Oracle小混子