Oracle数据库中dml提交,Oracle分批提交DML

1、分批UPDATE

DROP TABLE T2;

CREATE TABLE T2 AS SELECT OBJECT_NAME FROM DBA_OBJECTS;

SELECT * FROM T2;

SELECT COUNT(*) FROM T2;

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;

/

2、分批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;

/

3、分批INSERT

将T_20160401的数据全部插入T_20160401_01表。

DECLARE

CURSOR MYCURSOR IS

SELECT ROWID FROM T_20160401 ORDER BY ROWID;

TYPE ROWID_TABLE_TYPE IS TABLE OF  ROWID INDEX BY PLS_INTEGER ;

V_ROWID ROWID_TABLE_TYPE;

V_COUNT NUMBER := 0;

V_START DATE;

V_END   DATE;

BEGIN

SELECT SYSDATE INTO V_START FROM DUAL;

OPEN MYCURSOR;

LOOP

FETCH MYCURSOR BULK COLLECT

INTO V_ROWID LIMIT 50000;

EXIT WHEN V_ROWID.COUNT = 0;

FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST

INSERT INTO T_20160401_01

SELECT * FROM T_20160401 T WHERE T.ROWID=V_ROWID(I);

V_COUNT:=V_COUNT+TO_CHAR(SQL%ROWCOUNT);

COMMIT;

END LOOP;

CLOSE MYCURSOR;

SELECT SYSDATE INTO V_END FROM DUAL;

DBMS_OUTPUT.PUT_LINE('START: ' || V_START);

DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_COUNT);

DBMS_OUTPUT.PUT_LINE('END: ' || V_END);

COMMIT;

END;

/

DECLARE

CURSOR MYCURSOR IS

SELECT * FROM T_20160401 ORDER BY ROWID;

TYPE ROWID_TABLE_TYPE IS TABLE OF T_20160401%ROWTYPE;

V_ROWID ROWID_TABLE_TYPE;

BEGIN

OPEN MYCURSOR;

LOOP

FETCH MYCURSOR BULK COLLECT

INTO V_ROWID LIMIT 50000;

EXIT WHEN V_ROWID.COUNT = 0;

FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST

INSERT INTO T_20160401_01 VALUES V_ROWID(I);

COMMIT;

END LOOP;

CLOSE MYCURSOR;

END;

/