批量提交__网上整理

update批量提交
CREATE TABLE T2 AS SELECT OBJECT_NAME FROM DBA_OBJECTS;

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