批量提交__网上整理

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;