共计 4725 个字符，预计需要花费 12 分钟才能阅读完成。
场景
在存储过程、PL/SQL 块里需要返回 INSERT、DELETE、UPDATE、MERGE 等 DML 语句执行后的信息时使用，合理使用 returning 能够简化程序逻辑、提高程序性能。
概述
创建测试表
create table hh_emp_test as select * from scott.emp;
使用 returning 语句
declare
v_empno hh_emp_test.empno%type;
v_ename hh_emp_test.ename%type;
begin
update 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 类型
举例：
declare
type 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;
begin
update 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 loop
dbms_output.put_line(v_tab_empno(i)||’-‘||v_tab_ename(i));
end loop;
end;
输出：
7782-test
7839-test
7934-test
注意：
- 多行 returning 须用 bulk collect into
使用 RECORD 类型
示例：
declare
type 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;
begin
update 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 loop
dbms_output.put_line(v_tab(i).empno||’-‘||v_tab(i).ename);
end loop;
end;
输出：
7782-test
7839-test
7934-test
Dml 修改单行 + 动态 sql
示例：
declare
v_empno hh_emp_test.empno%type;
v_ename hh_emp_test.ename%type;
begin
execute immediate ‘update hh_emp_test set ename=”test” where empno=:empno returning empno,ename into :v_empno,:v_ename’
using 7369
returning into v_empno, v_ename;
rollback;
dbms_output.put_line(v_empno || ‘-‘ || v_ename);
end;
输出：
7369-test
注意：
- returning into在动态 sql 内部和外面都要写，且外面的 returning 后面不加字段直接into。
- using在 returning 前面
- into后面变量名不固定，注意冒号(:)，可以是命名规则下的任意字符。
dml 修改多行 + 动态 sql
使用 TABLE 类型
示例：
declare
type 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;
begin
execute immediate ‘update hh_emp_test set ename=”test” where deptno=:deptno returning empno,ename into :v_tab_empno,:v_tab_ename’
using 10
returning bulk collect
into v_tab_empno, v_tab_ename;
rollback;
for i in 1 .. v_tab_empno.count loop
dbms_output.put_line(v_tab_empno(i) || ‘-‘ || v_tab_ename(i));
end loop;
end;
输出：
7782-test
7839-test
7934-test
注意：
- 动态 sql 内部仍然是 returning into 而不是 returning bulk collect into
- returning bulk collect into 要写在外面，且后面同样不能是 record
使用 RECORD 类型
示例：
declare
type 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;
begin
execute immediate ‘update hh_emp_test set ename=”test” where deptno=10 returning empno,ename :v_tab’
returning bulk collect
into v_tab;
rollback;
for i in 1 .. v_tab.count loop
dbms_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 类型
示例：
declare
type 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 is
select * from hh_emp_test;
begin
open v_cur;
fetch v_cur bulk collect
into t_tab_source limit 3;
while t_tab_source.count > 0 loop
forall i in 1 .. t_tab_source.count
update hh_emp_test
set ename = ‘test’
where empno = t_tab_source(i).empno
returning empno, ename bulk collect into v_tab;
rollback;
for i in 1 .. v_tab.count loop
dbms_output.put_line(v_tab(i).empno || ‘-‘ || v_tab(i).ename);
end loop;
fetch v_cur bulk collect
into t_tab_source limit 3;
end loop;
close v_cur;
end;
输出：
7369-test
7499-test
7521-test
7566-test
7654-test
7698-test
7782-test
7839-test
7844-test
7900-test
7902-test
7934-test
使用 TABLE 类型
示例：
declare
type 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 is
select * from hh_emp_test;
begin
open v_cur;
fetch v_cur bulk collect
into t_tab_source limit 3;
while t_tab_source.count > 0 loop
forall i in 1 .. t_tab_source.count
update hh_emp_test
set ename = ‘test’
where empno = t_tab_source(i).empno
returning empno, ename bulk collect into v_tab_empno,v_tab_ename;
rollback;
for i in 1 .. v_tab_empno.count loop
dbms_output.put_line(v_tab_empno(i) || ‘-‘ || v_tab_ename(i));
end loop;
fetch v_cur bulk collect
into t_tab_source limit 3;
end loop;
close v_cur;
end;
输出：
7369-test
7499-test
7521-test
7566-test
7654-test
7698-test
7782-test
7839-test
7844-test
7900-test
7902-test
7934-test
小结：
Forall 的使用和静态 sql dml 修改多行的方法类似。
总结
Oracle Returning 语句随场景不同，语法有变化，要注意动态 sql returning 多行的情况不能使用 record 只能使用 table 类型。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135457.htm
