FORALL用法小结

FORALL用法小结

一、如何使用批挷定提高性能（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引擎）

在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

二、FORALL 如何影响回滚(How FORALL Affects Rollbacks)
在FORALL语句中，如果任何SQL语句执行产生未处理的异常(exception)，先前执行的所有数据库改变都会被回滚。然而，如果产生的异常被捕获并处理，则回滚改变到一个隐式的保存点，该保存点在每个SQL语句执行前被标记。之前的改变不会被回滚。

三、用%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属性使用相同下标。

%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

五、用BULK COLLECT子句取回查询结果至集合中
在返回到PL/SQL引擎之前，关键字BULK COLLECT告诉SQL引擎批挷定输出集合。该关键字能用于SELECT INTO, FETCH INTO和RETURNING INTO语句中。语法如下：
... BULK COLLECT INTO collection_name[, collection_name] ...

六、用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)为最近的整数.

七、用RETURNING INTO子句取回DML结果至集合
你能在INSERT、UPDATE或DELETE语句的RETURNING INTO子句中使用BULK COLLECT子句。(注意，此处是没有SELECT语句的)

八、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语句中的所有目标必须是集合

4．复合目标(如对象)不能用于RETURNING INTO子句中.
5．当需要隐式数据类型转换时，多复合目标(如对象集合)不能用于bulk collect into子句。

限制：以下限制将应用于FORALL语句：
1． 在FORALL循环中，UPDATE语句中的SET子句和WHERE子句中不能指向同一个集合，你可能需要获得另一个集合副本，以在WHERE子句指向新的名称。
2． INSERT，UPDATE或DELETE语句必须至少涉及一个集合。如在LOOP插入一组常量的FORALL语句将引发异常。（ FORALL的INSERT之类的语句一定要用in-bind 方式）

3． 指定范围内的所有集合元素必须存在，如果一元素丢失或删除，你将收到一个错误

4．复合值的输入集合不能被分解和跳跃数据库列

5． 集合下标不能是表达式

6． 游标属性%BULK_ROWCOUNT不能分配给其它集合，同样，它也不能作为参数传递到子程序

EXAMPLE：使用FORALL实现批量更新

DECLARE
TYPE tbl_so_head IS TABLE OF cux_so_order_header%ROWTYPE;  
  l_so_tbl tbl_so_head;  
  CURSOR cur_update IS
    SELECT h.*
      FROM cux_so_order_header h
       FOR UPDATE OF h.header_id NOWAIT;
BEGIN
  OPEN cur_update;  
   --在fetch into中使用bulk collect  
   FETCH cur_update BULK COLLECT INTO l_so_tbl;    
  FORALL i IN l_so_tbl.first .. l_so_tbl.last
    UPDATE cux_so_order_header h
       SET h.system_code = 'TB'
     WHERE h.header_id = l_so_tbl(i).header_id;
  CLOSE cur_update;   
END;