Oracle DML 子句 RETURNING INTO

The RETURNING INTO clause allows us to return column values for rows affected by DML statements. The following test table is used to demonstrate this clause.

DROP TABLE t1;
DROP SEQUENCE t1_seq;

CREATE TABLE t1 (
  id NUMBER(10),
  description VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE SEQUENCE t1_seq;

INSERT INTO t1 VALUES (t1_seq.nextval, 'ONE');
INSERT INTO t1 VALUES (t1_seq.nextval, 'TWO');
INSERT INTO t1 VALUES (t1_seq.nextval, 'THREE');
COMMIT;

When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.

SET SERVEROUTPUT ON
DECLARE
  l_id t1.id%TYPE;
BEGIN
  INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR')
  RETURNING id INTO l_id;
  COMMIT;

  DBMS_OUTPUT.put_line('ID=' || l_id);
END;
/
ID=4

PL/SQL procedure successfully completed.

SQL>

The syntax is also available for update and delete statements.

SET SERVEROUTPUT ON
DECLARE
  l_id t1.id%TYPE;
BEGIN
  UPDATE t1
  SET    description = description
  WHERE  description = 'FOUR'
  RETURNING id INTO l_id;

  DBMS_OUTPUT.put_line('UPDATE ID=' || l_id);

  DELETE FROM t1
  WHERE  description = 'FOUR'
  RETURNING id INTO l_id;

  DBMS_OUTPUT.put_line('DELETE ID=' || l_id);

  COMMIT;
END;
/
UPDATE ID=4
DELETE ID=4

PL/SQL procedure successfully completed.

SQL>

When DML affects multiple rows we can still use the RETURNING INTO, but now we must return the values into a collection using the BULK COLLECT clause.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF t1.id%TYPE;
  l_tab t_tab;
BEGIN
  UPDATE t1
  SET    description = description
  RETURNING id BULK COLLECT INTO l_tab;

  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));
  END LOOP;

  COMMIT;
END;
/
UPDATE ID=1
UPDATE ID=2
UPDATE ID=3

PL/SQL procedure successfully completed.

SQL>

We can also use the RETURNING INTO clause in combination with bulk binds.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_desc_tab IS TABLE OF t1.description%TYPE;
  TYPE t_tab IS TABLE OF t1%ROWTYPE;
  l_desc_tab t_desc_tab := t_desc_tab('FIVE', 'SIX', 'SEVEN');
  l_tab   t_tab;
BEGIN
  
  FORALL i IN l_desc_tab.first .. l_desc_tab.last
    INSERT INTO t1 VALUES (t1_seq.nextval, l_desc_tab(i))
    RETURNING id, description BULK COLLECT INTO l_tab;

  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('INSERT ID=' || l_tab(i).id || 
                         ' DESC=' || l_tab(i).description);
  END LOOP;

  COMMIT;
END;
/
INSERT ID=5 DESC=FIVE
INSERT ID=6 DESC=SIX
INSERT ID=7 DESC=SEVEN

PL/SQL procedure successfully completed.

SQL>

This functionality is also available from dymanic SQL.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF t1.id%TYPE;
  l_tab t_tab;
BEGIN
  EXECUTE IMMEDIATE 'UPDATE t1
                     SET    description = description
                     RETURNING id INTO :l_tab'
  RETURNING BULK COLLECT INTO l_tab;

  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));
  END LOOP;

  COMMIT;
END;
/
UPDATE ID=1
UPDATE ID=2
UPDATE ID=3

PL/SQL procedure successfully completed.

SQL>

For more information see:

• RETURNING INTO Clause

Hope this helps. Regards Tim...

from: http://www.oracle-base.com/articles/misc/dml_returning_into_clause.php

--End--

RETURNING语句几个小问题

2009-11-27 15:23

ORACLE的DML语句中可以指定RETURNING语句。RETURNING语句的使用在很多情况下可以简化PL/SQL编程。

这里不打算说明RETURNING语句的使用（其实使用起来也很简单，和SELECT INTO语句没有多大区别。），主要打算说明RETURNING语句的几个特点。

 

 

其实这篇文章源于同事问我的一个问题：

使用UPDATE语句的时候，RETURNING得到的结果是UPDATE之前的结果还是UPDATE之后的结果？

这个问题把我问住了。考虑DELETE的情况，RETURNING返回的肯定是DELETE之前的结果，而考虑INSERT的情况，RETURNING返回的一定是INSERT之后的结果。但是UPDATE到底返回那种情况，就无法推断出来了。而且，由于一般在使用UPDATE的RETURNING语句时，都会返回主键列，而主键列一般都是不会修改的，因此确实不清楚Oracle返回的是UPDATE之前的结果还是之后的结果。

当然，一个简单的例子就可以测试出来：

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NAME VARCHAR2(30);
3 BEGIN
4 INSERT INTO T VALUES (1, 'YANGTK') RETURNING NAME INTO V_NAME;
5 DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_NAME);
6 V_NAME := NULL;
7 UPDATE T SET NAME = 'YTK' RETURNING NAME INTO V_NAME;
8 DBMS_OUTPUT.PUT_LINE('UPDATE: ' || V_NAME);
9 V_NAME := NULL;
10 DELETE T RETURNING NAME INTO V_NAME;
11 DBMS_OUTPUT.PUT_LINE('DELETE: ' || V_NAME);
12 END;
13 /
INSERT: YANGTK
UPDATE: YTK
DELETE: YTK

PL/SQL 过程已成功完成。

显然，UPDATE操作的RETURNING语句是返回UPDATE操作之后的结果。

顺便总结几个RETURNING操作相关的问题：

1．RETURNING语句似乎和RETURN通用。

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NAME VARCHAR2(30);
3 BEGIN
4 INSERT INTO T VALUES (1, 'YANGTK') RETURN NAME INTO V_NAME;
5 DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_NAME);
6 V_NAME := NULL;
7 UPDATE T SET NAME = 'YTK' RETURN NAME INTO V_NAME;
8 DBMS_OUTPUT.PUT_LINE('UPDATE: ' || V_NAME);
9 V_NAME := NULL;
10 DELETE T RETURN NAME INTO V_NAME;
11 DBMS_OUTPUT.PUT_LINE('DELETE: ' || V_NAME);
12 END;
13 /
INSERT: YANGTK
UPDATE: YTK
DELETE: YTK

PL/SQL 过程已成功完成。

2．RETURNING语句也可以使用SQLPLUS的变量，这样，RETURNING语句不一定非要用在PL/SQL语句中。

SQL> VAR V_NAME VARCHAR2(30)
SQL> INSERT INTO T VALUES (1, 'YANGTK') RETURNING NAME INTO :V_NAME;

已创建 1 行。

SQL> PRINT V_NAME

V_NAME
--------------------------------
YANGTK

SQL> UPDATE T SET NAME = 'YTK' RETURNING NAME INTO :V_NAME;

已更新 1 行。

SQL> PRINT V_NAME

V_NAME
--------------------------------
YTK

SQL> DELETE T RETURNING NAME INTO :V_NAME;

已删除 1 行。

SQL> PRINT V_NAME

V_NAME
--------------------------------
YTK

3．INSERT INTO VALUES语句支持RETURNING语句，而INSERT INTO SELECT语句不支持。MERGE语句不支持RETURNING语句。

SQL> MERGE INTO T USING (SELECT * FROM T) T1
2 ON (T.ID = T1.ID)
3 WHEN MATCHED THEN UPDATE SET NAME = T1.NAME
4 WHEN NOT MATCHED THEN INSERT VALUES (T1.ID, T1.NAME)
5 RETURNING NAME INTO :V_NAME;
RETURNING NAME INTO :V_NAME
*第 5 行出现错误:
ORA-00933: SQL 命令未正确结束

SQL> INSERT INTO T SELECT * FROM T RETURNING NAME INTO :V_NAME;
INSERT INTO T SELECT * FROM T RETURNING NAME INTO :V_NAME
*第 1 行出现错误:
ORA-00933: SQL 命令未正确结束

这两个限制确实不大方便。不知道Oracle在以后版本中是否会放开。

个人感觉RETURNING语句和BULK COLLECT INTO语句配合使用的机会更多一些。