星空网 > 软件开发 > 数据库

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

注意:

  1. 多行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

注意:

  1. returning into在动态sql内部和外面都要写,且外面的returning后面不加字段直接into。
  2. using在returning前面
  3. 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

注意:

  1. 动态sql内部仍然是returning into而不是returning bulk collect into
  2. 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 RETURNING 用法总结

关键词:oracle

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

贺州交友:https://www.vstour.cn/t/贺州交友.html
贺州景点:https://www.vstour.cn/t/贺州景点.html
贺州酒店:https://www.vstour.cn/t/贺州酒店.html
贺州刻章:https://www.vstour.cn/t/贺州刻章.html
贺州两日游路线:https://www.vstour.cn/t/贺州两日游路线.html
贺州论坛:https://www.vstour.cn/t/贺州论坛.html
珠海市图书馆官网入口:https://www.vstour.cn/a/335174.html
珠海图书馆几点开门几点关门?附营业时间:https://www.vstour.cn/a/335175.html
相关文章
我的浏览记录
最新相关资讯
跨境电商服务平台 | 深圳旅行社 | 东南亚物流