程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> ORACLE RETURNING 用法總結,oraclereturning

ORACLE RETURNING 用法總結,oraclereturning

編輯:Oracle教程

ORACLE RETURNING 用法總結,oraclereturning


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

注意:

使用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

注意:

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

注意:

使用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類型。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved