——Part 1
在对数据表进行dml操作（Insert、Update和delete）的时候，有时会需要获取到进行操作的数据。最简单的方法就是在DML操作之前或者之后进行一下select操作，将数据获取到。此外，还可以使用一种更为简洁的方法，就是使用Oracle SQL的returning into子句。
Returning Into简介
在进行insert、update和delete操作的时候，都可以在末尾加入returning into字句。这字句的作用是将进行DML操作影响到数据行的列值，保存进指定的PL/SQL变量中。使用该字句的效果，与进行insert、update之后执行select into，以及在delete之前进行select into的效果相同。在Oracle官方的PL/SQL指导书中，推荐这种方法进行变量保存，能够最大限度的保证数据的一致。
下面我们通过一系列的代码示例进行说明。
实验环境准备
//10g的数据库环境
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> drop table t;
SQL> create table t as select * from dba_objects;
SQL> select count(*) from t;
COUNT(*)
----------
53300
单行变量returning into实验
returning单行数据pl/sql变量，是我们最常用的一种使用。下面的pl/sql匿名块分别从insert、update和delete三个方面进行实验。
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
--add a record
insert into t
(object_id, object_name)
values
(seq_t.nextval, 'MyTest')
returning object_id, object_name, object_type
into v_object_id, v_object_name, v_object_type;
dbms_output.put_line('New Record Returning : id='||v_object_id
||' name='||v_object_name
||' type='||nvl(v_object_type,'null'));
--update a record
update t
set object_id=20000
where object_id=1000
returning object_id, object_name, object_type
into v_object_id, v_object_name, v_object_type;
dbms_output.put_line('Mod Record Returning : id='||v_object_id
||' name='||v_object_name
||' type='||nvl(v_object_type,'null'));
--delete a record（Empty record）--删除一行不存在数据的时候，观察反映。
delete t
where object_id=1000
returning object_id, object_name, object_type
into v_object_id, v_object_name, v_object_type;
dbms_output.put_line('Del Record Returning : id='||v_object_id
||' name='||v_object_name
||' type='||nvl(v_object_type,'null'));
--delete a record （Not Empty Record）
delete t
where object_id=2000
returning object_id, object_name, object_type
into v_object_id, v_object_name, v_object_type;
dbms_output.put_line('Del Record Returning : id='||v_object_id
||' name='||v_object_name
||' type='||nvl(v_object_type,'null'));
--回退操作
rollback;
end;
/
上面代码，共包括四个实验项目。分别为：
1、 添加一行记录，返回指定列表达式给一系列的pl/sql变量；
2、 修改一行存在的数据记录，返回指定列表表达式给一系列的pl/sql变量；
3、 删除一行不存在的数据记录，同样returning into给变量；
4、 删除一行存在的数据记录，返回结果；
上述代码执行结果为：
SQL>
New Record Returning : id=100008 name=MyTest type=null
Mod Record Returning : id=20000 name=V_$BUFFER_POOL_STATISTICS type=VIEW
Del Record Returning : id=20000 name=V_$BUFFER_POOL_STATISTICS type=VIEW
Del Record Returning : id=2000 name=GV_$AW_LONGOPS type=VIEW
PL/SQL procedure successfully completed
结果分析：
1、 对insert操作，returning子句成功的将结果列的值返回给pl/sql变量；
2、 对update操作，returning子句则是将修改过的值返回给变量；
3、 对delete一条不存在记录，returning字句表现的很诡异了。首先，对应的pl/sql变量取值没有被flush into为null，还是保留着原有的变量值！其次，如果是一般的select into 变量值，在没有一行返回值的情况下，会报错。而此处的returning子句没有抛出异常，只是简单的没有赋值；
4、 对delete一条存在的记录，returning记录实现了将删除信息返回给pl/sql变量的作用；
结论：在文档中对returning子句的阐述，效果为insert、update之后进行select，delete之前进行select。实际测试的结果，在特殊情况下，还有一些区别需要注意！
Type类型into支持
对into后面的对象，除了使用简单的pl/sql变量外，还可以使用如type的记录类型。修改脚本如下：
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);
v_object_id t.object_id%type;
v_object_name t.object_name%type;
v_object_type t.object_type%type;
v_t_type t_type;
begin
--add a record
insert into t
(object_id, object_name)
values
(seq_t.nextval, 'MyTest')
returning object_id, object_name, object_type
into v_t_type;
dbms_output.put_line('New Record Returning : id='||v_t_type.v_id
||' name='||v_t_type.v_name
||' type='||nvl(v_t_type.v_type,'null'));
--update a record
update t
set object_id=20000
where object_id=1000
returning object_id, object_name, object_type
into v_t_type;
dbms_output.put_line('Mod Record Returning : id='||v_t_type.v_id
||' name='||v_t_type.v_name
||' type='||nvl(v_t_type.v_type,'null'));
/* v_object_id := null;
v_object_name := null;
v_object_type := null;*/
--delete a record（Empty record）
delete t
where object_id=1000
returning object_id, object_name, object_type
into v_t_type;
dbms_output.put_line('Del Record Returning : id='||v_t_type.v_id
||' name='||v_t_type.v_name
||' type='||nvl(v_t_type.v_type,'null'));
--delete a record （Not Empty Record）
delete t
where object_id=2000
returning object_id, object_name, object_type
into v_t_type;
dbms_output.put_line('Del Record Returning : id='||v_t_type.v_id
||' name='||v_t_type.v_name
||' type='||nvl(v_t_type.v_type,'null'));
--回退操作
rollback;
end;
/
输出结果为：
SQL>
New Record Returning : id=100009 name=MyTest type=null
Mod Record Returning : id=20000 name=V_$BUFFER_POOL_STATISTICS type=VIEW
Del Record Returning : id=20000 name=V_$BUFFER_POOL_STATISTICS type=VIEW
Del Record Returning : id=2000 name=GV_$AW_LONGOPS type=VIEW
PL/SQL procedure successfully completed
结论：returning into对type的支持是效果很好的，提供了很强的代码简洁性。但是对delete空记录的问题依然存在！；
——Part 2
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结构是不能轻易一起使用的。这可能与定位记录方式有一些关系。
----Part3 bulk 附录
BULK COLLECT 子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
有关FORALL语句的用法请参考：批量SQL之 FORALL 语句
一、BULK COLLECT批量绑定的示例
二、使用LIMIT限制FETCH数据量
在使用BULK COLLECT 子句时，对于集合类型，如嵌套表，联合数组等会自动对其进行初始化以及扩展(如下示例)。因此如果使用BULK
COLLECT子句操作集合，则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性，如果数据量较大，而集合在此时又自动扩展，为避
免过大的数据集造成性能下降，因此使用limit子句来限制一次提取的数据量。limit子句只允许出现在fetch操作语句的批量中。
用法：
FETCH ... BULK COLLECT INTO ... [LIMIT rows]
三、RETURNING 子句的批量绑定
BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用。当与这几个DML语句结合时，我们
需要使用RETURNING子句来实现批量绑定。
四、FORALL与BULK COLLECT 综合运用
FORALL与BULK COLLECT是实现批量SQL的两个重要方式，我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。
五、BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。
185
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
