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 record（Empty 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操作，系统会正确返回结果。

Forall与returning

在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.”

简单含义说明Forall与Returning关系，介绍如何在forall结构中使用returning。Forall语句可以对循环体中的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结构是不能轻易一起使用的。这可能与定位记录方式有一些关系。