批量查询 Oracle的bulk collect用法

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

转载于:http://blog.itpub.net/26690043/viewspace-716644/