2014-10-04 Created By BaoXinjian
一、摘要
PL/SQL程序中运行SQL语句是存在开销的，因为SQL语句是要提交给SQL引擎处理。
这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换，每次却换时，都有额外的开销。
但是，FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个，这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降。
1. FORALL与BULK COLLECT的使用方法:
(1). 使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。
(2). 使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
2. 未使用批处理时，系统需频繁切换上下文
3. 使用批处理后，系统只需切换上下文一次
二、批处理 - Bulk Collect
采用BULK COLLECT可以将查询结果一次性地加载到Collections中，而不是通过Cursor一条一条地处理。
可以在Select Into ，Fetch Into，Returning Into语句使用BULK COLLECT。
注意在使用BULK COLLECT时，所有的INTO变量都必须是Collections。
1. Select Into 中使用Bulk Collect
DECLARE
TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
v_table table_forall;
BEGIN
SELECT mub.user_id, mub.user_name
BULK COLLECT
INTO v_table
FROM mag_user_basic mub
WHERE mub.user_id BETWEEN 10000 AND 10100;
FORALL idx IN 1 .. v_table.COUNT
INSERT INTO test_forall
VALUES v_table (idx);
--VALUES(v_table(idx).user_id,v_table(idx).user_name);Error
--在PL/SQL中，BULK In-BIND与RECORD，%ROWTYPE是不能在一块使用的，
--也就是说，BULK In-BIND只能与简单类型的数组一块使用
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
END;
2. Fetch Into 中使用Bulk Collect
DECLARE
TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
v_table table_forall;
CURSOR c1
IS
SELECT mub.user_id, mub.user_name
FROM mag_user_basic mub
WHERE mub.user_id BETWEEN 10000 AND 10100;
BEGIN
OPEN c1;
--在fetch into中使用bulk collect
FETCH c1 BULK COLLECT INTO v_table;
FORALL idx IN 1 .. v_table.COUNT
INSERT INTO test_forall
VALUES v_table (idx);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
END;
3. 在Returning Into中使用Bulk Collect
DECLARE
TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;
enums IdList;
TYPE NameList IS TABLE OF test_forall.user_name%TYPE;
names NameList;
BEGIN
DELETE FROM test_forall2
WHERE user_id = 10100
RETURNING user_id, user_name BULK COLLECT INTO enums, names;
DBMS_OUTPUT.put_line ('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.put_line ('User #' || enums (i) || ': ' || names (i));
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
END;
三、批处理 - ForAll
FORALL告诉PL/SQL引擎要先把一个或多个集合的所有成员都绑定到SQL语句中，然后再把语句发送给SQL引擎。
1. 批量更新中，将For改成Forall
(1). 使用For语句进行Update
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70, ...);
-- department numbers
BEGIN
...
FOR i IN depts.FIRST..depts.LAST
LOOP
...
--UPDATE statement is sent to the SQL engine
-- with each iteration of the FOR loop!
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
END LOOP:
END;
(2). 使用Forall语句的批处理进行Update
--UPDATE statement is sent to the SQL engine just once, with the entire nested table
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp
SET sal = sal * 1.10
WHERE deptno = depts (i);
Thanks and Regards
参考：http://www.jb51.net/article/35424.htm
参考：http://log-cd.iteye.com/blog/411122
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
