一般情况下,在plsql语句中,对一个表做update的时候,我们可以直接通过returning子句来得到更新的数据的情况(包括更新的和未更新的),如:
DECLARE
type rec_return is record(ename varchar2(30), sal number(10,2));
TYPE tbl_return IS TABLE OF rec_return index by pls_integer;
v_ret tbl_return;
BEGIN
update scott.emp t
set t.sal = t.sal + 100
returning t.ename, t.sal bulk collect into v_ret;
END;
但是,如果是一个动态语句,我们如何实现类似returning bulk collect into这样的功能呢?这个问题上次已经解决过一次,但是没有记录下来,很快就忘掉了,这次要用的时候就没想起来。这次又翻了下文档,顺便记录下来:
DECLARE
TYPE tbl_ename IS TABLE OF varchar2(255) index by pls_integer;
type tbl_sal is table of number(10,2) index by pls_integer;
v_ret tbl_ename;
v_sal tbl_sal;
v_sqltext varchar2(2000) := '';
BEGIN
v_sqltext := 'update scott.emp t set t.sal = t.sal + 100 returning t.ename, t.sal into :1, :2';
execute immediate v_sqltext
returning bulk collect into v_ret, v_sal;
END;
可惜的是,在10g中,不支持多维数组,比如以上例子中ename和sal我可以组成一个record类型,然后再将这个record类型封装成数组,如:
SQL> DECLARE
2 type rec_return is record(ename varchar2(255), sal number(10,2));
3 TYPE tbl_return IS TABLE OF rec_return index by pls_integer;
4 v_ret tbl_return;
5 v_sqltext varchar2(2000) := '';
6 BEGIN
7 v_sqltext := 'update scott.emp t set t.sal = t.sal + 100 returning t.ename, t.sal into :1, :2';
8 execute immediate v_sqltext
9 returning bulk collect into v_ret;
10 END;
11 /
DECLARE
type rec_return is record(ename varchar2(255), sal number(10,2));
TYPE tbl_return IS TABLE OF rec_return index by pls_integer;
v_ret tbl_return;
v_sqltext varchar2(2000) := '';
BEGIN
v_sqltext := 'update scott.emp t set t.sal = t.sal + 100 returning t.ename, t.sal into :1, :2';
execute immediate v_sqltext
returning bulk collect into v_ret;
END;
ORA-06550: 第 10 行, 第 5 列:
PLS-00429: RETURNING 子句不支持的功能
ORA-06550: 第 9 行, 第 5 列:
PL/SQL: Statement ignored
说明该种类型在动态语句的情况下不受支持。