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空记录的问题依然存在！

 

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

转载于:http://blog.itpub.net/17203031/viewspace-690146/