首页 / 数据库 / MySQL / Oracle Returning 语句用法总结

场景

在存储过程、PL/SQL块里需要返回INSERT、DELETE、UPDATE、MERGE等DML语句执行后的信息时使用，合理使用returning能够简化程序逻辑、提高程序性能。

概述

创建测试表

create table hh_emp_test as select * from scott.emp;

使用returning语句

declarev_empno hh_emp_test.empno%type;v_ename hh_emp_test.ename%type;beginupdate hh_emp_test set ename="test" where empno=7369 returning empno,ename into v_empno,v_ename;rollback;dbms_output.put_line（v_empno||"-"||v_ename）;end;输出7369-test

场景分类

dml修改单行数据

使用方法见概述，此部分较简单，略。

dml修改多行数据

使用TABLE类型

举例：declaretype v_tp_tab_empno is table of hh_emp_test.empno%type index by pls_integer;v_tab_empno v_tp_tab_empno;type v_tp_tab_ename is table of hh_emp_test.ename%type index by pls_integer;v_tab_ename v_tp_tab_ename;beginupdate hh_emp_test set ename="test" where deptno=10 returning empno,ename bulk collect into v_tab_empno,v_tab_ename;rollback;for i in 1..v_tab_empno.count loopdbms_output.put_line（v_tab_empno（i）||"-"||v_tab_ename（i））;end loop;end;输出：7782-test7839-test7934-test注意：

1. 多行returning须用bulk collect into

使用RECORD类型

示例：declaretype v_tp_rec is record（empno number,ename varchar2（50））;type v_tp_tab is table of v_tp_rec index by pls_integer;v_tab v_tp_tab;beginupdate hh_emp_test set ename="test" where deptno=10 returning empno,ename bulk collect into v_tab;rollback;for i in 1..v_tab.count loopdbms_output.put_line（v_tab（i）.empno||"-"||v_tab（i）.ename）;end loop;end;输出：7782-test7839-test7934-test

Dml修改单行+动态sql

示例：declarev_empno hh_emp_test.empno%type;v_ename hh_emp_test.ename%type;beginexecute immediate "update hh_emp_test set ename=""test"" where empno=:empno returning empno,ename into :v_empno,:v_ename"using 7369returning into v_empno, v_ename;rollback;dbms_output.put_line（v_empno || "-" || v_ename）;end;输出：7369-test注意：

1. returning into在动态sql内部和外面都要写，且外面的returning后面不加字段直接into。
2. using在returning前面
3. into后面变量名不固定，注意冒号（:），可以是命名规则下的任意字符。

dml修改多行+动态sql

使用TABLE类型

示例：declaretype v_tp_tab_empno is table of hh_emp_test.empno%type index by pls_integer;v_tab_empno v_tp_tab_empno;type v_tp_tab_ename is table of hh_emp_test.ename%type index by pls_integer;v_tab_ename v_tp_tab_ename;beginexecute immediate "update hh_emp_test set ename=""test"" where deptno=:deptno returning empno,ename into :v_tab_empno,:v_tab_ename"using 10returning bulk collectinto v_tab_empno, v_tab_ename;rollback;for i in 1 .. v_tab_empno.count loopdbms_output.put_line（v_tab_empno（i） || "-" || v_tab_ename（i））;end loop;end;输出：7782-test7839-test7934-test注意：

1. 动态sql内部仍然是returning into而不是returning bulk collect into
2. returning bulk collect into要写在外面，且后面同样不能是record

使用RECORD类型

示例：declaretype v_tp_rec is record（empno number,ename varchar2（50））;type v_tp_tab is table of v_tp_rec index by pls_integer;v_tab v_tp_tab;beginexecute immediate "update hh_emp_test set ename=""test"" where deptno=10 returning empno,ename :v_tab"returning bulk collectinto v_tab;rollback;for i in 1 .. v_tab.count loopdbms_output.put_line（v_tab（i）.empno || "-" || v_tab（i）.ename）;end loop;end;执行报错：ORA-06550: 第 9 行, 第 5 列:PLS-00429: RETURNING 子句不支持的功能ORA-06550: 第 8 行, 第 3 列:PL/SQL: Statement ignored可见动态sql执行时，多行returning的多个字段须定义多个table类型的变量，目前为止（包括12c）不支持reurning record类型的语法。

forall中的returning

使用RECORD类型

示例：declaretype v_tp_rec is record（empno number,ename varchar2（50））;type v_tp_tab is table of v_tp_rec index by pls_integer;v_tab v_tp_tab;type t_tp_rec_source is table of hh_emp_test%rowtype index by pls_integer;t_tab_source t_tp_rec_source;cursor v_cur isselect * from hh_emp_test;beginopen v_cur;fetch v_cur bulk collectinto t_tab_source limit 3;while t_tab_source.count > 0 loopforall i in 1 .. t_tab_source.countupdate hh_emp_testset ename = "test"where empno = t_tab_source（i）.empnoreturning empno, ename bulk collect into v_tab;rollback;for i in 1 .. v_tab.count loopdbms_output.put_line（v_tab（i）.empno || "-" || v_tab（i）.ename）;end loop;fetch v_cur bulk collectinto t_tab_source limit 3;end loop;close v_cur;end;输出：7369-test7499-test7521-test7566-test7654-test7698-test7782-test7839-test7844-test7900-test7902-test7934-test

使用TABLE类型

示例：declaretype v_tp_tab_empno is table of hh_emp_test.empno%type index by pls_integer;v_tab_empno v_tp_tab_empno;type v_tp_tab_ename is table of hh_emp_test.ename%type index by pls_integer;v_tab_ename v_tp_tab_ename;type t_tp_rec_source is table of hh_emp_test%rowtype index by pls_integer;t_tab_source t_tp_rec_source;cursor v_cur isselect * from hh_emp_test;beginopen v_cur;fetch v_cur bulk collectinto t_tab_source limit 3;while t_tab_source.count > 0 loopforall i in 1 .. t_tab_source.countupdate hh_emp_testset ename = "test"where empno = t_tab_source（i）.empnoreturning empno, ename bulk collect into v_tab_empno,v_tab_ename;rollback;for i in 1 .. v_tab_empno.count loopdbms_output.put_line（v_tab_empno（i） || "-" || v_tab_ename（i））;end loop;fetch v_cur bulk collectinto t_tab_source limit 3;end loop;close v_cur;end;输出：7369-test7499-test7521-test7566-test7654-test7698-test7782-test7839-test7844-test7900-test7902-test7934-test小结：Forall的使用和静态sql dml修改多行的方法类似。

总结

Oracle Returning语句随场景不同，语法有变化，要注意动态sql returning多行的情况不能使用record只能使用table类型。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址