forall用法小结[转贴]

本文主要翻译、整理了 ORACLE 官方文档上有关 FORALL 的部份内容，不妥之处，还

希望多和大家交流。

在发送语句到 SQL 引擎前，FORALL 语句告知 PL/SQL 引擎批挷定输入集合。尽管

FORALL 语句包含一个迭代(iteration)模式，它并不一是个 FOR 循环。其语法为：

FORALL index IN lower_bound..upper_bound

sql_statement;

[@more@]

一、如何使用批挷定提高性能（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 进行数据插入，以显示用批挷定的对性能的提高：

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 := dbms_utility.get_time;

15

16 FOR i IN 1..500000 LOOP

17 INSERT INTO parts VALUES(pnums(i),pnames(i));

18 END LOOP;

19 t2 := dbms_utility.get_time;

20

21 FORALL i IN 1..500000

22 INSERT INTO parts VALUES(pnums(i),pnames(i));

23 t3 := dbms_utility.get_time;

24

25 dbms_output.put_line('Execution Time (secs)');

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 (secs)

---------------------

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(30, '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);

-- raises a "value too large" exception

EXCEPTION

WHEN OTHERS THEN

COMMIT;

END;

/

PL/SQL procedure successfully completed

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);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7490392/viewspace-1027801/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7490392/viewspace-1027801/