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;