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;
/