【DB笔试面试468】分批插入、分批更新、分批删除、分批提交的写法有哪些？

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

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

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