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>



相关阅读:
Linux系统下设置环境变量Path的方法
用asp自动解析网页中的图片地址
全国计算机等级考试二级Access考试大纲
无法使用GHOST及避免出现A:\GHOSTERR.TXT的解决方法
javascript Ajax获取远程url的返回判断
Tomcat下JSP、Servlet和JavaBean环境配置
操作Oracle的php类
VBS教程:正则表达式简介 -早期起源
HTML的复用技巧
表单数据提交时的字符编码问题
js链接确认提醒功能
GetRows的用法详解!
THINKPHP+JS实现缩放图片式截图的实现
Oracle的物化视图管理
快速导航

Copyright © 2016 phpStudy | 皖ICP备18014864号-4