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; /
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;