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;
Sql代码
/*
参考于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;