returning clause returning子句

returning clause
returning子句

使用RETURNING子句返回DML语句影响的记录的值
1.

drop table t purge;
create table t(a char, b int);
insert into t values ('a',1);
insert into t values ('b',3);
insert into t values ('c',4);
commit;

var v_a char
var v_b number
insert into t values ('d',2) returning a,b into :v_a,:v_b;
print v_a v_b
update t set b=9 where a='c' returning a,b into :v_a,:v_b;
print v_a v_b
delete t where a='b' returning a,b into :v_a,:v_b;
print v_a v_b

rollback;

SQL> var v_a char
SQL> var v_b number
SQL> insert into t values ('d',2) returning a,b into :v_a,:v_b;

1 row created.

SQL> print v_a v_b

V_A
--------------------------------
d


       V_B
----------
         2

SQL> update t set b=9 where a='c' returning a,b into :v_a,:v_b;

1 row updated.

SQL> print v_a v_b

V_A
--------------------------------
c


       V_B
----------
         9

SQL> delete t where a='b' returning a,b into :v_a,:v_b;

1 row deleted.

SQL> print v_a v_b

V_A
--------------------------------
b


       V_B
----------
         3

SQL>

对于INSERT语句, RETURNING返回的是插入后的值
对于UPDATE语句, RETURNING返回的是变更之后的值
对于DELETE语句, RETURNING返回的是删除前的值



2. RETURNING后面不仅可以是字段, 还可以是一个或多个表达式

var v_a varchar2(50)
var v_b number
var v_b2 number
insert into t values ('d',2) returning rowid,b into :v_a,:v_b;
print v_a v_b
update t set b=b+1 returning sum(b) into :v_b;
print v_b
delete t where a='b' returning rowid,sum(b) into :v_rid,:v_b;
delete t where a in ('b','c') returning min(b),sum(b) into :v_b,:v_b2;
print v_b v_b2
rollback;

SQL> var v_a varchar2(50)
SQL> var v_b number
SQL> var v_b2 number
SQL> insert into t values ('d',2) returning rowid,b into :v_a,:v_b;

1 row created.

SQL> print v_a v_b

V_A
--------------------------------------------------------------------------------------------------------------------------------
AAAFY8AAGAAAACPAAD


       V_B
----------
         2

SQL> update t set b=b+1 returning sum(b) into :v_b;

4 rows updated.

SQL> print v_b

       V_B
----------
        14

SQL> delete t where a='b' returning rowid,sum(b) into :v_rid,:v_b;
delete t where a='b' returning rowid,sum(b) into :v_rid,:v_b
                               *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> delete t where a in ('b','c') returning min(b),sum(b) into :v_b,:v_b2;

2 rows deleted.

SQL> print v_b v_b2

       V_B
----------
         4


      V_B2
----------
         9

SQL> rollback;

Rollback complete.

SQL>

表达式可以是rowid, 函数, 或是聚集函数(10g新特性)


3. RETURNING BULK COLLECT INTO返回多条记录
如果DML语句影响了多条记录, 而RETURNING子句后也不是聚集函数, 那么使用BULK COLLECT INTO一次返回多条记录

set serveroutpu on size unlimited
select * from t;
declare
  type t_t is table of t%rowtype index by binary_integer;
  v_t_a t_t;
begin
  update t set b=b+1 returning a,b bulk collect into v_t_a;
  for i in 1..v_t_a.count loop
    dbms_output.put_line(i||':a='||v_t_a(i).a||',b='||v_t_a(i).b);
  end loop;
  rollback;
end;
/

SQL> set serveroutpu on size unlimited
SQL> select * from t;

A          B
- ----------
a          1
b          3
c          4

SQL> declare
  2    type t_t is table of t%rowtype index by binary_integer;
  3    v_t_a t_t;
  4  begin
  5    update t set b=b+1 returning a,b bulk collect into v_t_a;
  6    for i in 1..v_t_a.count loop
  7      dbms_output.put_line(i||':a='||v_t_a(i).a||',b='||v_t_a(i).b);
  8    end loop;
  9    rollback;
 10  end;
 11  /
1:a=a,b=2
2:a=b,b=4
3:a=c,b=5

PL/SQL procedure successfully completed.

SQL>

4.使用中的限制

a.聚集函数和非聚集函数表达式不能一起用, 见前面的例子

b.聚集函数中不能用DISTINCT

select * from t;
var v_b number
update t set b=b+1 returning count(distinct b) into :v_b;
print v_b
rollback;

SQL> select * from t;

A          B
- ----------
a          2
b          4
c          5

SQL> var v_b number
SQL> update t set b=b+1 returning count(distinct b) into :v_b;
update t set b=b+1 returning count(distinct b) into :v_b
                             *
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> print v_b

       V_B
----------


SQL> rollback;

Rollback complete.

SQL>

c.聚集函数不能在INSERT语句里使用

var v_b number
insert into t values ('d',9) returning count(b) into :v_b;
print v_b
rollback;

SQL> var v_b number
SQL> insert into t values ('d',9) returning count(b) into :v_b;
insert into t values ('d',9) returning count(b) into :v_b
                                       *
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> print v_b

       V_B
----------


SQL> rollback;

Rollback complete.

SQL>

d.如果表达式包含主键字段或非空字段, 且存在BEFORE UPDATE触发器, UPDATE语句会失败
这是文档写的
"If the expr list contains a primary key column or other NOT NULL column, then the update statement fails if the table has a BEFORE UPDATE trigger defined on it."
但实际测试没有发现问题

alter table t modify (b not null);
alter table t modify (a primary key);
create or replace trigger tr_t
  before update on t for each row
begin
  :new.b := 2;
end;
/
var v_b number
var v_a number
update t set b=b+1 returning count(a),sum(b) into :v_b,:v_a;
print v_a v_b

e. INSERT INTO子查询不支持RETURNING

var v_b number
insert into t (select 'g',8 from t) returning sum(b) into v_b;

SQL> insert into t (select 'g',8 from t) returning sum(b) into v_b;
insert into t (select 'g',8 from t) returning sum(b) into v_b
                                    *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL>

f.多表插入语句,MERGE语句,并行DML,远程对象,LONG类型,视图,INSTEAD OF触发器均不支持RETURNING
(都没测试过...)




外部链接:
DELETE
INSERT
UPDATE
RETURNING INTO Clause
Examples of Dynamic Bulk Binds


-fin-