采用bulk collect可以将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理 可以在select into ，fetch into ， returning into语句使用bulk collect 注意在使用bulk collect时，所有的into变量都必须是collections
举几个简单例子：
① 在select into语句中使用bulk collect
代码如下:
declare type sallist is table of employees.salary%type; sals sallist; begin select salary bulk collect into sals from employees where rownum<=50; –接下来使用集合中的数据 end; /
② 在fetch into中使用bulk collect
代码如下:
declare type deptrectab is table of departments%rowtype; dept_recs deptrectab; cursor cur is select department_id,department_name from departments where department_id>10; begin open cur; fetch cur bulk collect into dept_recs; –接下来使用集合中的数据 end; /
③ 在returning into中使用bulk collect
代码如下:
create table emp as select * from employees;
declare type numlist is table of employees.employee_id%type; enums numlist; type namelist is table of employees.last_name%type; names namelist; begin delete emp where department_id=30 returning employee_id,last_name bulk collect into enums,names; dbms_output.put_line(‘deleted’||sql%rowcount||’rows:’); for i in enums.first .. enums.last loop dbms_output.put_line(’employee#’||enums(i)||’:’||names(i)); end loop; end; /
eate table emp as select * from employees;declare type numlist is table of employees.employee_id%type; enums numlist; type namelist is table of employees.last_name%type; names namelist;begin delete emp where department_id=30 returning employee_id,last_name bulk collect into enums,names; dbms_output.put_line(‘deleted’||sql%rowcount||’rows:’); for i in enums.first .. enums.last loop dbms_output.put_line(’employee#’||enums(i)||’:’||names(i)); end loop;end;/deleted6rows:employee#114:raphaelyemployee#115:khooemployee#116:baidaemployee#117:tobiasemployee#118:himuroemployee#119:colmenares
declare –按rowid排序的cursor –删除条件是oo=xx，这个需根据实际情况来定 cursor mycursor is select rowid from t where oo=xx order by rowid; type rowid_table_type is table of rowid index by pls_integer; v_rowid rowid_table_type; begin open mycursor; loop fetch mycursor bulk collect into v_rowid limit 5000;–5000行提交一次 exit when v_rowid.count=0; forall i in v_rowid.first..v_rowid.last delete t where rowid=v_rowid(i); commit; end loop; close mycursor; end; /
⑶ 限制bulk collect 提取的记录数
语法： fetch cursor bulk collect into …[limit rows]； 其中，rows可以是常量，变量或者求值的结果是整数的表达式
declare cursor allrows_cur is select * from employees; type employee_aat is table of allrows_cur%rowtype index by binary_integer; v_emp employee_aat; begin open allrows_cur; loop fetch allrows_cur bulk fetch into v_emp limit 100;
/*通过扫描集合对数据进行处理*/ for i in 1 .. v_emp.count loop upgrade_employee_status(v_emp(i).employee_id); end loop;
declare –声明集合类型 type vehtab is table of transportation%rowtype; –初始化一个这个类型的集合 gas_quzzlers vehtab; begin select * bulk collect into gas_quzzlers from transportation where mileage <20; …
京公网安备 11010802041100号