关于嵌套表为record类型与bulk bind的结合使用

在PL/SQL中，BULK In-BIND与RECORD，%ROWTYPE是不能在一块使用的，也就是说，BULK In-BIND只能与简单类型的数组一块使用，这样导致如果有多个字段需要用BULK In-BIND来处理的话，代码就比较复杂：

declare
type tab_test is table of tmp_0925%rowtype;
v_id tab_test;

cursor cur_aids is select *
from tmp_0925
where rn >= v_begin and rn <= v_end;
begin
open cur_aids;
fetch cur_aids bulk collect into v_id;
v_cnt := v_id.count;

forall j in 1..v_cnt
update test
set (id, name, age) = (select v_id(j).id, v_id(j).name, v_id(j).age from dual)
where id = v_aids(j).id;

commit;
end;

LINE/COL ERROR
-------- -----------------------------------------------------------------
44/21 PLS-00382: expression is of wrong type
44/45 PLS-00436: implementation restriction: cannot reference fields
of BULK In-BIND table of records

如果想避免PLS-00436又想使用FORALL的话，代码如下：

declare
type tab_id is table of tmp_0925.id%type;
type tab_name is table of tmp_0925.name%type;
type tab_age is table of tmp_0925.age%type;
v_id tab_id;
v_name tab_name;
v_age tab_age;

cursor cur_aids is select *
from tmp_0925
where rn >= v_begin and rn <= v_end;
begin
open cur_aids;
fetch cur_aids bulk collect into v_id, v_name, v_age;
v_cnt := v_id.count;

forall j in 1..v_cnt
update test
set (id, name, age) = (select v_id(j), v_name(j), v_age(j) from dual)
where id = v_id(j);

commit;
end;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11423276/viewspace-985474/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11423276/viewspace-985474/