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

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

 

转载于:https://blog.51cto.com/934886/801813