聊聊Forall使用的几个细节问题

 Oracle PL/SQL是我们进行数据库应用开发极其方便的工具。PL/SQL的提出，是给面向集合操作的SQL和过程化程序结构之间提供一种过渡衔接。同其他程序设计语言一样，PL/SQL也存在基本的顺序、条件和循环语句结构。

Forall作为For循环结构的一种变体，在PL/SQL程序优化过程中占到了很大的比重。Forall的优势在笔者之前的文章中介绍多次，PL/SQL与SQL引擎一次性切换、批量操作等等。同时，Forall在语法结构上面也受到很多限制。本篇中，笔者打算介绍Forall使用过程中几个常见的细节问题。

1、环境准备

笔者选择Oracle 11gR2进行使用，创建测试数据表T。

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE   11.2.0.1.0 Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

--创建数据表

SQL> create table t as select * from dba_objects;

Table created

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     72758

 

 

2、Rollback与Forall异常

 

我们经常遇到这样的场景，一个for/forall循环结构，其中包括一句DML语句。执行过程中，由于一些特殊情况执行报错。那么，我们有没有想过，当由于一条语句执行的原因引起异常Exception，那些没问题的执行能否被接受提交呢？

实验数据表T中，就包含这样的潜在陷阱。

SQL> desc t

Name           Type          Nullable Default Comments

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

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                         

SUBOBJECT_NAME VARCHAR2(30)  Y                         

OBJECT_ID      NUMBER        Y                        

DATA_OBJECT_ID NUMBER        Y                        

OBJECT_TYPE    VARCHAR2(19)  Y                         

CREATED        DATE          Y                        

LAST_DDL_TIME  DATE          Y                        

TIMESTAMP      VARCHAR2(19)  Y

 

 

Object_type和timestamp最大长度均为19，两个极端情况之和达到38，当然也可能小于38。字段subobject_name长度为30。这样，两个字段和进行更新赋值的时候，就可能出现部分不能更新，部分能够更新的情况。于是，我们编写了下面一段代码。

SQL> declare

  2    type id_list is table of t.object_id%type index by binary_integer;

  3    id_infos id_list;

  4    i number;

  5  begin

  6    select object_id

  7    bulk collect into id_infos

  8    from t;

  9 

 10    forall i in id_infos.first..id_infos.last

 11        update t set SUBOBJECT_NAME=OBJECT_TYPE||TIMESTAMP

 12         where object_id=id_infos(i);

 13 

 14    commit;

 15  end;

 16  /

 

declare

  type id_list is table of t.object_id%type index by binary_integer;

  id_infos id_list;

  i number;

begin

  select object_id

  bulk collect into id_infos

  from t;

 

  forall i in id_infos.first..id_infos.last

      update t set SUBOBJECT_NAME=OBJECT_TYPE||TIMESTAMP

       where object_id=id_infos(i);

 

  commit;

end;

 

ORA-12899: 列 "SYS"."T"."SUBOBJECT_NAME" 的值太大 (实际值: 31, 最大值: 30)

ORA-06512: 在 line 10

 

 

果然以报错结尾。那么，我们commit动作下的结果集合，是否有呢？

SQL> select count(*) from t where subobject_name=object_type||timestamp;

 

  COUNT(*)

----------

         0

 

 

结论：当我们使用forall的时候，如果其中的SQL语句抛出了异常，结果取决于代码抛出的异常是否有Exception进行捕获处理。如果没有语句进行捕获处理，会自动进行rollback动作，将操作结果回滚。

那么，如果我们进行异常捕获，结果如何呢？

SQL> declare

  2    type id_list is table of t.object_id%type index by binary_integer;

  3    id_infos id_list;

  4    i number;

  5  begin

  6    select object_id

  7    bulk collect into id_infos

  8    from t;

  9 

 10    forall i in id_infos.first..id_infos.last

 11        update t set SUBOBJECT_NAME=OBJECT_TYPE||TIMESTAMP

 12         where object_id=id_infos(i);

 13 

 14    commit;

 15  exception

 16    when others then

 17       commit;

 18  end;

 19  /

 

PL/SQL procedure successfully completed

 

SQL> select count(*) from t where subobject_name=object_type||timestamp;

 

  COUNT(*)

----------

      1153

 

 

同样抛出了异常，但是已经更新成功的数据被完整的保存下来了。所以：如果我们在forall抛出异常的时候进行捕获，在Exception处理语句中进行commit的话，我们成功更新的记录是可以保存下来的。

思考：究竟是没有捕获，就全rollback对了？还是捕获之后，成功更新的能够被commit对了呢？答案是：没有对错！

这个过程其实提供了两种选择。选择正确的语法语句结构，实现心中理想的业务逻辑。基本的出发点是：异常Exception总不会是业务流程的一部分。

如果业务处理要求一次性的要么全部更新，要么一条都不更新，使用不捕获，或者在捕获中rollback就没有问题。如果我们希望处理那种一条条独立的交易更新，更新之间其实没有什么关系，下次续做的时候还可以继续操作，那么Exception捕获后commit是不错的选择，毕竟已经成功处理过了。

 

3、Forall中Bulk_Exceptions捕获

 

如果细心的朋友，将上面的例子拿到for结构上去运行，结果其实是一样的。那么，是不是forall和for在异常上是相同的呢？我们说不是的。

从原理上分析，forall一次性将所有的SQL推入提交SQL引擎进行执行。这个过程中，实际上是同时执行的效果，获取到的异常应该是多个。而For则是一条条的执行，最多能发现一条异常语句，就抛出了。

如果需要完全捕获forall的异常，就需要使用save exceptions字句在forall中。使用之后，所有的异常，就会以数组的形式记录在SQL%Bulk_Exception这个隐含的异常数组结构中。

 

QL> set serveroutput on;

SQL> declare

  2    type id_list is table of t.object_id%type index by binary_integer;

  3    id_infos id_list;

  4    i number;

  5    errors_num number;

  6  begin

  7    select object_id

  8    bulk collect into id_infos

  9    from t

 10    where rownum<2000;

 11 

 12    forall i in id_infos.first..id_infos.last save exceptions

 13        update t set SUBOBJECT_NAME=OBJECT_TYPE||TIMESTAMP

 14         where object_id=id_infos(i);

 15 

 16    commit;

 17  exception

 18    when others then

 19       errors_num := sql%bulk_exceptions.count;

 20       dbms_output.put_line('Total Exception is : '||to_char(errors_num));

 21 

 22       if errors_num>10 then

 23          errors_num := 10;

 24       end if;

 25 

 26       for i in 1..errors_num loop

 27           dbms_output.put_line('Error # '||i||' is '||sql%bulk_exceptions

 

(i).error_index||' '||sqlerrm(-sql%bulk_exceptions(i).error_code));

 28       end loop;

 29 

 30       rollback;

 31  end;

 32  /

 

Total Exception is : 3

Error # 1 is 1001 ORA-12899: 列  的值太大 (实际值: , 最大值: )

Error # 2 is 1019 ORA-12899: 列  的值太大 (实际值: , 最大值: )

Error # 3 is 1024 ORA-12899: 列  的值太大 (实际值: , 最大值: )

 

PL/SQL procedure successfully completed

 

 

sql%bulk_exceptions是一个数组对象。每个元素是一个code和index的结合体，同常规的exception结构相似。

使用save exceptions语句的时候，要评估性能问题。对异常对象数组的数据填入需要额外的空间、时间消耗。所以，要斟酌使用。

4、forall与rowcount

 

我们在for语句中，可以写多条语句，可以获取到每次一个DML处理的记录数目。在forall里面，我们也是可以实现这个功能的。

使用sql%bulk_rowcount，可以保存下每次DML操作影响到的记录数量。同bulk_exceptions一样，bulk_rowcount也是一个数组对象，每个成员都记录对应的迭代更新语句的影响记录条数。

 

SQL> set serveroutput on;

SQL> declare

  2    type owner_list is table of t.owner%type index by binary_integer;

  3    owner_infos owner_list;

  4    i number;

  5    l number;

  6  begin

  7    select distinct owner

  8    bulk collect into owner_infos

  9    from t;

 10 

 11    forall i in owner_infos.first..owner_infos.last

 12       update t set object_id=object_id+1

 13         where owner=owner_infos(i);

 14 

 15    for i in owner_infos.first..owner_infos.last loop

 16        dbms_output.put_line('Iteration # '||i||' : '||sql%bulk_rowcount(i)||'rows.');

 17    end loop;

 18    commit;

 19  end;

 20  /

 

Iteration # 1 : 12 rows.

Iteration # 2 : 1 rows.

Iteration # 3 : 1509 rows.

（篇幅原因，有省略……）

Iteration # 31 : 30930 rows.

Iteration # 32 : 316 rows.

Iteration # 33 : 8 rows.

 

PL/SQL procedure successfully completed

 

 

这样在处理forall的时候，能有很多方便的功能。

 

5、结论

 

作为一种语句，forall在很多时候比传统的for是有优势的。但是对应一种新的语法结构，我们需要去了解很多东西，学习实验很多东西，才能真正掌握好。

 

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

转载于:http://blog.itpub.net/17203031/viewspace-777035/