分批插入、分批更新、分批删除、分批提交的写法有哪些？

DB宝 2018-12-26

643

♣题目部分分批插入、分批更新、分批删除、分批提交的写法有哪些？

     
♣答案部分


这个知识点也是检测程序员编程能力的主要考题，分为3个类别：分批插入、分批更新、分批删除及分批提交。主要采用游标加BULK COLLECT的方式来实现，用LIMIT来限制每次需要操作的行数。其中，分批更新也可以采用游标FOR循环加ORDER BY排序的方式，效率也是非常不错的。
下面作者给出3个模版程序，若笔试中出现类似的题目，则可以照猫画虎。
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;
/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。