Oracle 分批提交DML

/*
参考于TOM编程艺术 8.5章在循环中提交

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

DROP TABLE T4;
DROP TABLE T5;
CREATE TABLE T4 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE T5 AS SELECT * FROM T4 WHERE 1=0;
DECLARE  
   CURSOR MYCURSOR IS SELECT  ROWID FROM T4  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
        INSERT INTO T5 SELECT * FROM T4 WHERE ROWID=V_ROWID(I);
     COMMIT;
   END LOOP;
   CLOSE MYCURSOR;
END;