Oracle分批提交DML

1.採用分批操作並不能提高執行速度，執行效率不如單條DML語句。

    2.分批插入可以減少對undo空間的佔用，但頻繁的提交，可能會導致前面提交的undo空間被其他事務佔用而可能導致ORA-0155錯誤。

    3.若分批操作中間半截失敗掉，會將你資料庫置於一種未知的狀態。（DELETE操作不會出現這種情況）

    */

    --分批 update

    DROP TABLE T2;

    CREATE TABLE T2 AS SELECT OBJECT_NAME FROM DBA_OBJECTS;

    SELECT * FROM T2;

    SELECT COUNT(*) FROM T2;

    --is table of 建立一個xx型別的陣列

    DECLARE

    TYPE RIDARRAY IS TABLE OF ROWID;

    TYPE VCARRAY IS TABLE OF T2.OBJECT_NAME%TYPE;

    L_RIDS RIDARRAY;

    L_NAMES VCARRAY;

    CURSOR C IS SELECT ROWID, OBJECT_NAME FROM T2;

    BEGIN

    OPEN C;

    LOOP

    FETCH C BULK COLLECT INTO L_RIDS, L_NAMES LIMIT 10;

    FORALL I IN 1 .. L_RIDS.COUNT

    UPDATE T2

    SET OBJECT_NAME = LOWER(L_NAMES(I))

    WHERE ROWID = L_RIDS(I);

    COMMIT;

    EXIT WHEN C%NOTFOUND;

    END LOOP;

    CLOSE C;

    END;

    --分批delete

    DROP TABLE T3;

    CREATE TABLE T3 AS SELECT * FROM DBA_OBJECTS;

    DECLARE

    CURSOR MYCURSOR IS SELECT ROWID FROM T3 ORDER BY ROWID; --------按ROWID排序的CURSOR，刪除條件是XXX=XXXX，根據實際情

    TYPE ROWID_TABLE_TYPE IS TABLE OF ROWID INDEX BY PLS_INTEGER;

    V_ROWID ROWID_TABLE_TYPE;

    BEGIN

    OPEN MYCURSOR;

    LOOP

    FETCH MYCURSOR BULK COLLECT INTO V_ROWID LIMIT 5000; --------每次處理5000行，也就是每5000行一提交

    EXIT WHEN V_ROWID.COUNT=0;

    FORALL I IN V_ROWID.FIRST..V_ROWID.LAST

    DELETE FROM T3 WHERE ROWID=V_ROWID(I);

    COMMIT;

    END LOOP;

    CLOSE MYCURSOR;

    END;

    /

分批insert方法相同不再演示。