ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 聊聊Forall使用的几个细节问题

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

原创 Linux操作系统 作者:realkid4 时间:2013-11-19 22:48:36 0 删除 编辑

 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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: PL/SQL与DDL语句
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7629741