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 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次，指定范围内的每个索