ITPub博客

首页 > 数据库 > Oracle > 关于嵌套表为record类型与bulk bind的结合使用

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

原创 Oracle 作者:flysky0814 时间:2007-11-28 10:40:21 0 删除 编辑

在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-03-31

  • 博文量
    53
  • 访问量
    382427