oracle returning into

在对数据表进行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空记录的问题依然存在！