Oracle DML 子句 RETURNING INTO

最新推荐文章于 2021-09-16 11:22:06 发布

原创最新推荐文章于 2021-09-16 11:22:06 发布 · 183 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #SQL #编程 #PHP

pl/sql 专栏收录该内容

13 篇文章

订阅专栏

本文介绍Oracle数据库中DML语句的RETURNING子句用法，该子句可用于INSERT、UPDATE和DELETE操作中返回受影响行的值。文章通过实例展示了如何使用RETURNING子句返回单一值和集合值，并探讨了UPDATE操作后返回值的特点。

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语句配合使用的机会更多一些。