pl/sql中bulk collect的用法

bulk collect可以将查询结果一次性地加载到collections中，而不用一条一条地处理。在select into,fetch into,returning into语句使用使用bulk collect时，所有的into变量都必须是collections。

create table jy

(

object_id number(12),

object_name varchar2(20),

object_type varchar2(20)

)

在select into语句中使用bulk collect

declare

type object_list is table of jy.object_name%type;

objs object_list;

begin

select object_name bulk collect

into objs

from jy;

for r in objs.first .. objs.last loop

dbms_output.put_line(''|| objs(r));

end loop;

end;

/

在fetch into中使用bulk collect

declare

type objecttab is table of jy%rowtype;

objs objecttab;

cursor cob is

select object_id, object_name, object_type

from jy;

begin

open cob;

fetch cob bulk collect

into objs;

close cob; -- 把结果集一次fetch到collect中，我们还可以通过limit参数，来分批fetch数据

for r in objs.first .. objs.last loop

dbms_output.put_line(' ' || objs(r).object_name);

end loop;

end;

declare

type objecttab is table of jy%rowtype;

objs objecttab;

cursor cob is

select object_id, object_name, object_type

from jy;

begin

open cob;

loop

fetch cob bulk collect

into objs limit 100;--每次取一百条数据这是可以根据你的数据库性能来决定的

exit when cob%notfound;

dbms_output.put_line('count:' || objs.count || ' first:' || objs.first ||

' last:' || objs.last);

for r in objs.first .. objs.last loop

dbms_output.put_line(' objs(r)=' || objs(r).object_name);

end loop;

end loop;

close cob;

end;

在returning into中使用bulk collect

declare

type id_list is table of jy.object_id%type;

ids id_list;

type name_list is table of jy.object_name%type;

names name_list;

begin

delete from jy  returning object_id, object_name bulk collect into ids,

names;

dbms_output.put_line('deleted ' || sql%rowcount || ' rows:');

for i in ids.first .. ids.last loop

dbms_output.put_line('object #' || ids(i) || ': ' || names(i));

end loop;

end;