Returning Into子句研究

http://www.itpub.net/thread-1503225-1-1.html

 

在对数据表进行dml操作（Insert、Update和delete）的时候，有时会需要获取到进行操作的数据。最简单的方法就是在DML操作之前或者之后进行一下select操作，将数据获取到。此外，还可以使用一种更为简洁的方法，就是使用OracleSQL的returninginto子句。

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 Database10gEnterpriseEdition Release10.2.0.1.0 - Prod

PL/SQL Release10.2.0.1.0 - Production

CORE 10.2.0.1.0Production

TNS for 32-bit Windows: Version10.2.0.1.0 - Production

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