BULK In-BIND與RECORD(轉)

在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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242235/，如需轉載，請註明出處，否則將追究法律責任。