分批插入、分批更新、分批删除、分批提交的写法有哪些？
答案部分
这个知识点也是检测程序员编程能力的主要考题，分为3个类别：分批插入、分批更新、分批删除及分批提交。主要采用游标加BULK COLLECT的方式来实现，用LIMIT来限制每次需要操作的行数。其中，分批更新也可以采用游标FOR循环加ORDER BY排序的方式，效率也是非常不错的。
下面作者给出3个模版程序，若笔试中出现类似的题目，则可以照猫画虎。
1、分批UPDATE
代码语言：javascript复制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
代码语言：javascript复制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表。
代码语言：javascript复制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/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
