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; 在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; 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 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; 在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;
eric0435 系统架构师(高级工程师),ACOUG amp;CSOUG核心成员，Oracle Young Expert 。政府信息化技术顾问。湖南省政府采购评审专家，从2007年开始从事社会保险系统开发，应用架构设计，数据库管理工作。擅长Oracle数据库故障诊断，性能调优。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载