FORALL 用法小结：
作者：sonic
本文主要翻译、整理了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 databa by an INSERT or UPDATE statement.
out-bind：When a databa value is assigned to a PL/SQL variable or a host variable by the RET
URNING clau of an INSERT, UPDATE, or DELETE statement. define： When a databa 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进行数据插入，以显示用批挷定的对性能的提高：SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
Table created.
SQL> DECLARE
2 TYPE NumTab IS TABLE OF parts.pnum%TYPE INDEX BY BINARY_INTEGER;
3 TYPE NameTab IS TABLE OF parts.pname%TYPE INDEX BY BINARY_INTEGER;
4 pnums NumTab;
5 Pnames NameTab;
6 t1 NUMBER;
7 t2 NUMBER;
8 t3 NUMBER;
9 BEGIN
10 FOR i IN 1..500000 LOOP
11 pnums(i) := i;
12 pnames(i) := 'Part No.'||to_char(i);
13 END LOOP;
14 t1 := _time;
15
16 FOR i IN 1..500000 LOOP
17 INSERT INTO parts VALUES(pnums(i),pnames(i));
18 END LOOP;
19 t2 := _time;
20
21 FORALL i IN 1..500000
22 INSERT INTO parts VALUES(pnums(i),pnames(i));
23 t3 := _time;
24李圣杰
25 dbms_output.put_line('Execution Time (cs)');
26 dbms_output.put_line('---------------------');
27 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
28 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2));
29 END;
SQL> /
Execution Time (cs)
---------------------
FOR loop: 2592
FORALL: 358
PL/SQL procedure successfully completed
百海教育从而可以看出FORALL语句在性能上有显著提高。
注释：SQL语句能涉及多个集合，然而，性能提高只适用于下标集合(subscripted collections)
二、FORALL 如何影响回滚(How FORALL Affects Rollbacks)
在FORALL语句中，如果任何SQL语句执行产生未处理的异常(exception)，先前执行的所有数据库改变都会被回滚。然而，如果产生的异常被捕获并处理，则回滚改变到一个隐式的保存点，该保存点在每个SQL语句执行前被标记。之前的改变不会被回滚。例如：
CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title INSERT INTO emp2 VALUES(3
0, 'Analyst');
INSERT INTO emp2 VALUES(30, 'Analyst');
Comit;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp2 SET job = job || ' (temp)'
WHERE deptno = depts(j);
-- rais a "value too large" exception
EXCEPTION
WHEN OTHERS THEN
COMMIT;
END;
/
PL/SQL procedure successfully completed
SQL> lect * 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;
返回SQL语句所有执行处理总的行数
%ROWCOUNT
%FOUND和 %NOTFOUND仅与SQL语句的最后执行有关，但是，可以使用
%BULK_ROWCOUNT推断单个执行的值，如%BULK_ROWCOUNT(i)为0时，%FOUND
和%NOTFOUND分别是FALSE和TRUE。
四、用%BULK_EXCEPTIONS属性处理FORALL异常
在执行FORALL语句期间，PL/SQL提供一个处理异常的机制。该机制使批挷定
(bulk-bind)操作能保存异常信息并继续执行。方法是在FORALL语句中增加SA VE EXCEPTIONS关键字。语法为：
FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{inrt_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集
合记录的字段)。如果忽略SA VE 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_um_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 s 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 pno%TYPE;
TYPE NameTab IS TABLE ame%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);
