批量提交__网上整理update批量提交CREATE TABLE T2 AS SELECT OBJECT_NAME FROM DBA_OBJECTSdeclare type
批量提交__网上整理
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;
