ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 批量查询 Oracle的bulk collect用法

批量查询 Oracle的bulk collect用法

原创 Linux操作系统 作者:adrain_001 时间:2012-02-17 10:13:33 0 删除 编辑

采用bulk collect可以将查询结果一次性地加载到collections中,而不是通过cursor一条一条地处理。

可以在select into,fetch into,returning into语句使用bulk collect。

注意:在使用bulk collect时,所有的into变量都必须是collections。

create table t_test as

select object_id, object_name, object_type
from dba_objects
where wner = 'TEST';

1、在select into语句中使用bulk collect

declare
type object_list is table of t_test.object_name%type;
objs object_list;
begin
select object_name bulk collect
into objs
from t_test
where rownum <= 100;
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r));
end loop;
end;
/

2、在fetch into中使用bulk collect

declare
type objecttab is table of t_test%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from t_test
where rownum <= 10;
begin
open cob;
fetch cob bulk collect
into objs;
close cob;
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r).object_name);
end loop;
end;
/

以上为把结果集一次fetch到collect中,我们还可以通过limit参数,来分批fetch数据,如下:

declare
type objecttab is table of t_test%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from t_test
where rownum <= 10000;
begin
open cob;
loop
fetch cob bulk collect
into objs limit 1000;
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;
/

你可以根据实际来调整limit参数的大小,来达到最优的性能。limit参数会影响到PGA的使用率。

3、在returning into中使用bulk collect

declare
type id_list is table of t_test.object_id%type;
ids id_list;
type name_list is table of t_test.object_name%type;
names name_list;
begin
delete from t_test
where object_id <= 87510 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;

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

上一篇: PL/SQL表
请登录后发表评论 登录
全部评论

注册时间:2012-02-13

  • 博文量
    113
  • 访问量
    259910