Oracle 批绑定（batch bind）FORALL, BULK COLLECT

FORALL 用法小结： 
　　本文主要翻译、整理了ORACLE官方文档上有关FORALL的部份内容，不妥之处，还希望多和大家交流。 
在发送语句到SQL引擎前，FORALL语句告知PL/SQL 引擎批挷定输入集合。尽管FORALL语句包含一个迭代(iteration)模式，它并不一是个FOR循环。其语法为： 
　　FORALL index IN lower_bound..upper_bound sql_statement; 


一、如何使用批挷定提高性能（How Do Bulk Binds Improve Performance） 
　　在PL/SQL 和SQL引擎(engines)中，太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。下图显示PL/SQL引擎 和SQL引擎之间的context switches：（PL/SQL引擎执行存过语句仅发送SQL语句到SQL引擎，SQL引擎执行语句后返回数据给PL/SQL引擎） 
　　PL/SQL引擎发送一次SQL语句给SQL引擎，在SQL引擎中则为范围中每个index数字执行一次SQL语句。 
　　PL/SQL挷定操作包含以下三类： 
　　in-bind： When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement. 
　　out-bind：When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement. 
　　define： When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement. 
　　在SQL语句中，为PL/SQL变量指定值称为挷定(binding)， 
　　DML语句能传递所有集合元素到一个单个操作中，这过程称为批挷定（bulk binding）。 
　　如果集合有20个元素，批挷定让你用单个操作等效于执行与20个SELECT，INSERT，   UPDATE或DELETE语句。这个技术通过减少在PL/SQL和SQL引擎(engines)间的上下文切换来提高性能。批挷定包括： 
　　1．带INSERT, UPDATE, and DELETE语句的批挷定：在FORALL语句中嵌入SQL语句 
　　2．带SELECT语句的批挷定：在SELECT语句中用BULK COLLECT 语句代替INTO 
　　下边的例子分别用FOR和FORALL进行数据插入，以显示用批挷定的对性能的提高： 

Java代码  

1. SQL> SET SERVEROUTPUT ON  
2. SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));  
3. Table created.  
4. SQL> DECLARE  
5. 2 TYPE NumTab IS TABLE OF parts.pnum%TYPE INDEX BY BINARY_INTEGER;  
6. 3 TYPE NameTab IS TABLE OF parts.pname%TYPE INDEX BY BINARY_INTEGER;  
7. 4 pnums NumTab;  
8. 5 Pnames NameTab;  
9. 6 t1 NUMBER;  
10. 7 t2 NUMBER;  
11. 8 t3 NUMBER;  
12. 9 BEGIN  
13. 10 FOR i IN 1..500000 LOOP  
14. 11 pnums(i) := i;  
15. 12 pnames(i) := 'Part No.'||to_char(i);  
16. 13 END LOOP;  
17. 14 t1 := dbms_utility.get_time;  
18. 15  
19. 16 FOR i IN 1..500000 LOOP  
20. 17 INSERT INTO parts VALUES(pnums(i),pnames(i));  
21. 18 END LOOP;  
22. 19 t2 := dbms_utility.get_time;  
23. 20  
24. 21 FORALL i IN 1..500000  
25. 22 INSERT INTO parts VALUES(pnums(i),pnames(i));  
26. 23 t3 := dbms_utility.get_time;  
27. 24  
28. 25 dbms_output.put_line('Execution Time (secs)');  
29. 26 dbms_output.put_line('---------------------');  
30. 27 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));  
31. 28 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2));  
32. 29 END;  

SQL> / 
Execution Time (secs) 
--------------------- 
FOR loop: 2592 
FORALL: 358 
PL/SQL procedure successfully completed 
　　从而可以看出FORALL语句在性能上有显著提高。 
　　注释：SQL语句能涉及多个集合，然而，性能提高只适用于下标集合(subscripted collections) 
二、FORALL 如何影响回滚(How FORALL Affects Rollbacks) 
　　在FORALL语句中，如果任何SQL语句执行产生未处理的异常(exception)，先前执行的所有数据库改变都会被回滚。然而，如果产生的异常被捕获并处理，则回滚改变到一个隐式的保存点，该保存点在每个SQL语句执行前被标记。之前的改变不会被回滚。例如： 

Java代码  

1. CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));  
2. INSERT INTO emp2 VALUES(10, 'Clerk');  
3. INSERT INTO emp2 VALUES(10, 'Clerk');  
4. INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title  
5. INSERT INTO emp2 VALUES(30, 'Analyst');  
6. INSERT INTO emp2 VALUES(30, 'Analyst');  
7. Comit;  
8. DECLARE  
9. TYPE NumList IS TABLE OF NUMBER;  
10. depts NumList := NumList(10, 20, 30);  
11. BEGIN  
12. FORALL j IN depts.FIRST..depts.LAST  
13. UPDATE emp2 SET job = job || ' (temp)'  
14. WHERE deptno = depts(j);  
15. -- raises a "value too large" exception  
16. EXCEPTION  
17. WHEN OTHERS THEN  
18. COMMIT;  
19. END;  
20. /  
21. PL/SQL procedure successfully completed  
22. SQL> select * from emp2;  

DEPTNO JOB 
---------- --------------- 
10 Clerk temp 
10 Clerk temp 
20 Bookkeeper 
30 Analyst 
30 Analyst 
　　上边的例子SQL引擎执行UPDATE语句3次，指定范围内的每个索引号一次。第一个(depts(10))执行成功，但是第二个(depts(20))执行失败(插入值超过了列长)，因此，仅仅第二个执行被回滚。 
　　当执行任何SQL语句引发异常时，FORALL语句中断(halt)。上边的例子中，执行第二个UPDATE语句引发异常， 因此第三个语句不会执行。 
三、用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 
　　在进行SQL数据操作语句时，SQL引擎打开一个隐式游标(命名为SQL)，该游标的标量属性(scalar attribute)有 %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT。 
　　FORALL语句除具有上边的标量属性外，还有个复合属性(composite attribute)：%BULK_ROWCOUNT，该属性具有索引表(index-by table)语法。它的第i个元素存贮SQL语句(INSERT, UPDATE或DELETE)第i个执行的处理行数。如果第i个执行未影响行，%bulk_rowcount (i)，返回0。FORALL与%bulk_rowcount属性使用相同下标。例如： 


DECLARE 
TYPE NumList IS TABLE OF NUMBER; 
depts NumList := NumList(10, 20, 50); 
BEGIN 
FORALL j IN depts.FIRST..depts.LAST 
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j); 
-- Did the 3rd UPDATE statement affect any rows? 
IF SQL%BULK_ROWCOUNT(3) = 0 THEN ... 
END; 
　　%ROWCOUNT 返回SQL语句所有执行处理总的行数 
　　%FOUND和 %NOTFOUND仅与SQL语句的最后执行有关，但是，可以使用%BULK_ROWCOUNT推断单个执行的值，如%BULK_ROWCOUNT(i)为0时，%FOUND和%NOTFOUND分别是FALSE和TRUE。 
四、用%BULK_EXCEPTIONS属性处理FORALL异常 
　　在执行FORALL语句期间，PL/SQL提供一个处理异常的机制。该机制使批挷定(bulk-bind)操作能保存异常信息并继续执行。方法是在FORALL语句中增加SAVE EXCEPTIONS关键字。语法为： 
　　FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS 
　　　　{insert_stmt | update_stmt | delete_stmt} 
　　执行期间引发的所有异常都被保存游标属性 %BULK_EXCEPTIONS中，它存贮一个集合记录，每记录有两个字段： 
　　%BULK_EXCEPTIONS(i).ERROR_INDEX：存贮在引发异常期间FORALL语句迭代(重复：iteration) 
　　%BULK_EXCEPTIONS(i).ERROR_CODE：存贮相应的Oracle错误代码 
　　%BULK_EXCEPTIONS.COUNT存贮异常的数量。(该属性不是%BULK_EXCEPTIONS集合记录的字段)。如果忽略SAVE EXCEPTIONS，当引发异常时，FORALL语句停止执行。此时，SQL%BULK_EXCEPTIONS.COUNT 返回1, 且SQL%BULK_EXCEPTIONS只包含一条记录。如果执行期间无异常 SQL%BULK_EXCEPTIONS.COUNT 返回 0.例子： 


DECLARE 
TYPE NumList IS TABLE OF NUMBER; 
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1); 
errors NUMBER; 
dml_errors EXCEPTION; 
PRAGMA exception_init(dml_errors, -24381); 
BEGIN 
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS 
DELETE FROM emp WHERE sal > 500000/num_tab(i); 
EXCEPTION 
WHEN dml_errors THEN 
errors := SQL%BULK_EXCEPTIONS.COUNT; 
dbms_output.put_line('Number of errors is ' || errors); 
FOR i IN 1..errors LOOP 
dbms_output.put_line('Error ' || i || ' occurred during '|| 
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 
dbms_output.put_line('Oracle error is ' || 
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); 
END LOOP; 
END; 
该例子中，当i等于2，6,10时，产生异常ZERO_DIVIDE，完成后SQL%BULK_EXCEPTIONS.COUNT为3，其值为(2,1476), (6,1476)和(10,1476)，错误输出如下： 
　　Number of errors is 3 
　　Error 1 occurred during iteration 2 
　　Oracle error is ORA-01476: divisor is equal to zero 
　　Error 2 occurred during iteration 6 
　　Oracle error is ORA-01476: divisor is equal to zero 
　　Error 3 occurred during iteration 10 
　　Oracle error is ORA-01476: divisor is equal to zero 
五、用BULK COLLECT子句取回查询结果至集合中 
　　在返回到PL/SQL引擎之前，关键字BULK COLLECT告诉SQL引擎批挷定输出集合。该关键字能用于SELECT INTO, FETCH INTO和RETURNING INTO语句中。语法如下： 
　　... BULK COLLECT INTO collection_name[, collection_name] ... 


示例1： 
DECLARE 
TYPE NumTab IS TABLE OF emp.empno%TYPE; 
TYPE NameTab IS TABLE OF emp.ename%TYPE; 
enums NumTab; -- no need to initialize 
names NameTab; 
BEGIN 
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp; 
... 
END; 
示例2： 
CREATE TYPE Coords AS OBJECT (x NUMBER, y NUMBER); 
CREATE TABLE grid (num NUMBER, loc Coords); 
INSERT INTO grid VALUES(10, Coords(1,2)); 
INSERT INTO grid VALUES(20, Coords(3,4)); 
DECLARE 
TYPE CoordsTab IS TABLE OF Coords; 
pairs CoordsTab; 
BEGIN 
SELECT loc BULK COLLECT INTO pairs FROM grid; 
-- now pairs contains (1,2) and (3,4) 
END; 
示例3： 
DECLARE 
TYPE SalList IS TABLE OF emp.sal%TYPE; 
sals SalList; 
BEGIN 
SELECT sal BULK COLLECT INTO sals FROM emp 
WHERE ROWNUM <= 100; 
... 
END; 
示例4：Examples of Bulk Fetching from a Cursor： 
DECLARE 
TYPE NameList IS TABLE OF emp.ename%TYPE; 
TYPE SalList IS TABLE OF emp.sal%TYPE; 
CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000; 
names NameList; 
sals SalList; 
BEGIN 
OPEN c1; 
FETCH c1 BULK COLLECT INTO names, sals; --可返回到一个或多个集合 
END; 
示例5：Examples of Bulk Fetching from a Cursor： 
DECLARE 
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE; 
dept_recs DeptRecTab; 
CURSOR c1 IS 
SELECT deptno, dname, loc FROM dept WHERE deptno > 10; 
BEGIN 
OPEN c1; 
FETCH c1 BULK COLLECT INTO dept_recs; --返回到一个记录(records)集合 
END; 
六、用LIMIT 子句限制批取出操作行 
　　LIMIT子句可限制从数据库中取出的行数。该子句仅能用于批(非标量 not scalar)FETCH语句.语法为： 
　　FETCH ... BULK COLLECT INTO ... [LIMIT rows]; 
　　rows可以是文字的(literal)，变量(variable)或表达式(expression)，但必须返回一个数字。否则，PL/SQL引发预定义异常VALUE_ERROR，如果为负数，PL/SQ引发INVALID_NUMBER。如果需要，PL/SQL四舍五入(round)为最近的整数. 
　　下例中，在每个循环迭代中，FETCH语句返回到索引表empnos中，先前的值会被覆盖。 


DECLARE 
TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 
CURSOR c1 IS SELECT empno FROM emp; 
empnos NumTab; 
rows NATURAL := 10; 
BEGIN 
OPEN c1; 
LOOP 
/**//* The following statement fetches 10 rows (or less). */ 
FETCH c1 BULK COLLECT INTO empnos LIMIT rows; 
EXIT WHEN c1%NOTFOUND; 
... 
END LOOP; 
CLOSE c1; 
END; 
七、用RETURNING INTO子句取回DML结果至集合 
　　你能在INSERT、UPDATE或DELETE语句的RETURNING INTO子句中使用BULK COLLECT子句。(注意，此处是没有SELECT语句的) 


DECLARE 
TYPE NumList IS TABLE OF emp.empno%TYPE; 
enums NumList; 
BEGIN 
DELETE FROM emp WHERE deptno = 20 
RETURNING empno BULK COLLECT INTO enums; 
-- if there were five employees in department 20, 
-- then enums contains five employee numbers 
END; 
八、BULK COLLECT上的限制 
　　1．You cannot bulk collect into an associative array that has a string type for the key. . 
　　2．BULK COLLECT语句只能用于服务器端(server-side)，而非客户端 
　　3．在BULK COLLECT INTO语句中的所有目标必须是集合，如下例： 


DECLARE 
TYPE NameList IS TABLE OF emp.ename%TYPE; 
names NameList; 
salary emp.sal%TYPE; 
BEGIN 
SELECT ename, sal BULK COLLECT INTO names, salary -- illegal target 
FROM emp WHERE ROWNUM < 50; 
... 
END;. 
　　4．复合目标(如对象)不能用于RETURNING INTO子句中. 
　　5．当需要隐式数据类型转换时，多复合目标(如对象集合)不能用于bulk collect into子句。 
九、同时使用FORALL 和BULK COLLECT 
　　Using FORALL and BULK COLLECT Together 
　　你能使FORALL语句与BULK COLLECT结合，如下例：如果集合depts有3个元素，每个元素导致5行被删除，则语句完成时，集合enums有15个元素： 
　　FORALL j IN depts.FIRST..depts.LAST 
　　DELETE FROM emp WHERE empno = depts(j) 
　　RETURNING empno BULK COLLECT INTO enums; 
　　注意：不能在FORALL语句中使用SELECT ... BULK COLLECT语句。 
十、Using Host Arrays with Bulk Binds 
　　客户端程序能用匿名PL/SQL块批挷定输入和输出数组(arrays)。实际上，这是与服务器端传递集合最有效的方式。 
　　Host arrays are declared in a host environment such as an OCI or Pro*C program and must be prefixed with a colon to distinguish them from PL/SQL collections. In the example below, an input host array is used in a DELETE statement. At run time, the anonymous PL/SQL block is sent to the database server for execution. 
　　DECLARE 
　　... 
　　BEGIN 
　　-- assume that values were assigned to the host array 
　　-- and host variables in the host environment 
　　FORALL i IN :lower..:upper 
　　DELETE FROM emp WHERE deptno = :depts(i); 
　　... 
　　END; 
　　限制：以下限制将应用于FORALL语句： 
　　1． 在FORALL循环中，UPDATE语句中的SET子句和WHERE子句中不能指向同一个集合，你可能需要获得另一个集合副本，以在WHERE子句指向新的名称。 
　　2． INSERT，UPDATE或DELETE语句必须至少涉及一个集合。如在LOOP插入一组常量的FORALL语句将引发异常。（ FORALL的INSERT之类的语句一定要用in-bind 方式，如： 


SQL> BEGIN 
2 FORALL i IN 1..100 
3 INSERT INTO parts VALUES(i,i); 
4 END; 
5 / 
ORA-06550: 第 3 行, 第 5 列: 
PLS-00435: 没有 BULK In-BIND 的 DML 语句在 FORALL 内不能使用 
ORA-06550: 第 2 行, 第 12 列: 
PL/SQL: Statement ignored 
　　3． 指定范围内的所有集合元素必须存在，如果一元素丢失或删除，你将收到一个错误，如： 


DECLARE 
TYPE NumList IS TABLE OF NUMBER; 
depts NumList := NumList(10, 20, 30, 40); 
BEGIN 
depts.DELETE(3); -- delete third element 
FORALL i IN depts.FIRST..depts.LAST 
DELETE FROM emp WHERE deptno = depts(i); -- causes an error 
END; 
　　4． 下例显示，复合值的输入集合不能被分解和跳跃数据库列 


CREATE TABLE coords (x NUMBER, y NUMBER); 
CREATE TYPE Pair AS OBJECT (m NUMBER, n NUMBER); 
DECLARE 
TYPE PairTab IS TABLE OF Pair; 
pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6)); 
TYPE NumTab IS TABLE OF NUMBER; 
nums NumTab := NumTab(1, 2, 3); 
BEGIN 
/**//* The following statement fails. */ 
FORALL i IN 1..3 
UPDATE coords SET (x, y) = pairs(i) 
WHERE x = nums(i); 
END; 
The workaround is to decompose the composite values manually: 
DECLARE 
TYPE PairTab IS TABLE OF Pair; 
pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6)); 
TYPE NumTab IS TABLE OF NUMBER; 
nums NumTab := NumTab(1, 2, 3); 
BEGIN 
/**//* The following statement succeeds. */ 
FORALL i in 1..3 
UPDATE coords SET (x, y) = (pairs(i).m, pairs(i).n) 
WHERE x = nums(i); 
END; 
　　5． 集合下标不能是表达式，示例： 
　　FORALL j IN mgrs.FIRST..mgrs.LAST 
　　DELETE FROM emp WHERE mgr = mgrs(j+1); -- invalid subscript 
　　6． 游标属性%BULK_ROWCOUNT不能分配给其它集合，同样，它也不能作为参数传递到子程序。