ORACLE RETURNING 用法总结
ORACLE RETURNING 用法总结
场景
在存储过程、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类型。
