ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Returning Into子句研究(二)

Returning Into子句研究(二)

原创 Linux操作系统 作者:realkid4 时间:2011-03-22 13:40:49 0 删除 编辑

Returning bulk collect into子句支持批量

 

 

当进行DML操作的数据集合为批量多行的时候,可以使用bulk collect into进行结果集合收集。如果进行dml操作的结果集合为多行,但是使用returning into子句到变量,Oracle会报错。

 

set serveroutput on size 1000;

 

declare

  v_object_id t.object_id%type;

  v_object_name t.object_name%type;

  v_object_type t.object_type%type;

begin

 

  --update a record

  update t

  set object_id=20000

  where object_type='SEQUENCE'

  returning object_id, object_name, object_type

  into v_object_id, v_object_name, v_object_type; ---多条记录!

 

  dbms_output.put_line('Mod Record Numbers : numbers='||to_char(t_infos.count));

 

  --回退操作

  rollback;

end;

/

 

系统返回结果

 

 

ORA-01422: 实际返回的行数超出请求的行数

ORA-06512: line 15

 

 

此时,就需要使用returning bulk collect into子句。

 

 

set serveroutput on size 1000;

 

declare

  type t_type is record (v_id t.object_id%type,

                         v_name t.object_name%type,

                         v_type t.object_type%type);

  type t_list is table of t_type index by binary_integer;

                        

  v_object_id t.object_id%type;

  v_object_name t.object_name%type;

  v_object_type t.object_type%type;

 

  t_infos t_list; 

begin

 

  --update a record

  update t

  set object_id=20000

  where object_type='SEQUENCE'

  returning object_id, object_name, object_type

  bulk collect into t_infos;

 

  dbms_output.put_line('Mod Record Numbers : numbers='||to_char(t_infos.count));

 

  --delete a recordEmpty record

  delete t

  where object_type='TTT'

  returning object_id, object_name, object_type

  bulk collect into t_infos;

 

  dbms_output.put_line('Del Record Numbers : numbers='||to_char(t_infos.count));

 

  --delete a record Not Empty Record

  delete t

  where object_type='SEQUENCE'

  returning object_id, object_name, object_type

  bulk collect into t_infos;

 

  dbms_output.put_line('Del Record Numbers : numbers='||to_char(t_infos.count));

   

  --回退操作

  rollback;

end;

/

 

相同的实验结构,包括三个方面。

1、  批量更新多条记录;

2、  删除多条不存在记录;

3、  删除多条记录;

系统返回结果。

 

SQL>

 

Mod Record Numbers : numbers=340

Del Record Numbers : numbers=0

Del Record Numbers : numbers=340

 

PL/SQL procedure successfully completed

 

 

结果很明显。值得关注的就是对不存在记录的DML操作,系统会正确返回结果。

 

 

Forallreturning

 

Oracle官方文档中,包括如下阐述。

 

If you put an UPDATE statement with a RETURNING clause in a FORALL statement, you

can retrieve values from a set of updated rows into a collection of records.

 

简单含义说明ForallReturning关系,介绍如何在forall结构中使用returningForall语句可以对循环体中的SQL语句进行一次性批量提交,大大减少PL/SQL引擎与SQL引擎的切换次数(Oracle官方解释),提高性能。但是使用forall是有很多限制,首先forall结构体中只能包括一条SQL语句,而且使用数组成员必须为简单类型等。

 

forall中,是可以使用returning的,将变量返回给数据对象。

 

实验代码如下:

 

set serveroutput on size 1000;

 

declare

   type t_rowids is table of urowid index by binary_integer;

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

   type t_names is table of t.object_name%type index by binary_integer;

   

   t_rowids_infos t_rowids;

   t_ids_infos t_ids;

   t_names_infos t_names;

  

begin 

  --Get rowid List

  select rowid

  bulk collect into t_rowids_infos

  from t

  where rownum<1000;

 

  --update

  forall i in 1..t_rowids_infos.count

     update t

     set object_id=object_id

     where rowid=t_rowids_infos(i)

     returning object_name bulk collect into t_names_infos;

 

  dbms_output.put_line('Update Nums : '||to_char(t_names_infos.count));

 

  --delete

  forall i in 1..t_rowids_infos.count

     delete t where rowid=t_rowids_infos(i)

     returning object_name bulk collect into t_names_infos;

    

  dbms_output.put_line('Del Nums : '||to_char(t_names_infos.count));

 

  --回退操作

  rollback;

end;

/

 

实验代码中进行了更新和删除两种操作,在forall结构中直接调用returning XX bulk collect into <数组对象>,就可以直接将所有的更新影响过的记录输入到其中。

 

试验结果如下:

 

SQL>

 

Update Nums : 999

Del Nums : 999

 

PL/SQL procedure successfully completed

 

 

 

select into returning

 

最后,我们一起看下select into子句和returning结合的情况。

 

set serveroutput on size 1000;

 

declare

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

   t_ids_infos t_ids;  

begin 

  --Get rowid List

  insert into t

  select * from dba_objects

  returning object_id bulk collect into t_ids_infos;

 

  --回退操作

  rollback;

end;

/

 

试验结果如下:

 

ORA-06550: 14 , 13 :

PL/SQL: ORA-00933: SQL 命令未正确结束

ORA-06550: 12 , 3 :

PL/SQL: SQL Statement ignored

 

 

看来returning子句和insert into结构是不能轻易一起使用的。这可能与定位记录方式有一些关系。

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7754655