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

• 2019 年 10 月 11 日
• 筆記

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

答案部分

这个知识点也是检测程序员编程能力的主要考题，分为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程序员面试笔试宝典》，作者：李华荣。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步