Oracle分批提交DML

1.采用分批操作并不能提高执行速度，执行效率不如单条DML语句。

2.分批插入可以减少对undo空间的占用，但频繁的提交，可能会导致前面提交的undo空间被其他事务占用而可能导致ORA-0155错误。

3.若分批操作中间半截失败掉，会将你数据库置于一种未知的状态。（DELETE操作不会出现这种情况）

    1、分批UPDATE DROP TABLE T2; CREATE TABLE T2 AS SELECT OBJECT_NAME FROM DBA_OBJECTS; SELECT * FROM T2;  SELECT COUNT(*) FROM T2; 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; / 2、分批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; / 3、分批INSERT 将T_20160401的数据全部插入T_20160401_01表。 DECLARE   CURSOR MYCURSOR IS     SELECT ROWID FROM T_20160401 ORDER BY ROWID;   TYPE ROWID_TABLE_TYPE IS TABLE OF  ROWID INDEX BY PLS_INTEGER ;   V_ROWID ROWID_TABLE_TYPE;   V_COUNT NUMBER := 0;   V_START DATE;   V_END   DATE; BEGIN   SELECT SYSDATE INTO V_START FROM DUAL;   OPEN MYCURSOR;   LOOP     FETCH MYCURSOR BULK COLLECT       INTO V_ROWID LIMIT 50000;     EXIT WHEN V_ROWID.COUNT = 0;     FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST       INSERT INTO T_20160401_01 SELECT * FROM T_20160401 T WHERE T.ROWID=V_ROWID(I); V_COUNT:=V_COUNT+TO_CHAR(SQL%ROWCOUNT);     COMMIT;   END LOOP;   CLOSE MYCURSOR; SELECT SYSDATE INTO V_END FROM DUAL;   DBMS_OUTPUT.PUT_LINE('START: ' || V_START);   DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_COUNT);   DBMS_OUTPUT.PUT_LINE('END: ' || V_END);   COMMIT; END; / DECLARE   CURSOR MYCURSOR IS     SELECT * FROM T_20160401 ORDER BY ROWID;   TYPE ROWID_TABLE_TYPE IS TABLE OF T_20160401%ROWTYPE;   V_ROWID ROWID_TABLE_TYPE; BEGIN   OPEN MYCURSOR;   LOOP     FETCH MYCURSOR BULK COLLECT       INTO V_ROWID LIMIT 50000;     EXIT WHEN V_ROWID.COUNT = 0;        FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST       INSERT INTO T_20160401_01 VALUES V_ROWID(I);     COMMIT;   END LOOP;   CLOSE MYCURSOR; END; /