Oracle扩展PL/SQL简介(六)

8.    bulk collect /forall
使用bulk collect可以成块地读取数据，它可使SQL引擎在返回输出结果给PL/SQL引擎之前大批绑定输出集合。这样可以一次性地把数据动态地装载到集合中，但bulk collect需要大量内存。bulk collect可用于select into、fetch into和returning into语句中。
●    select into中使用bulk collect
declare
  type t_email is table of employees.email%type;
  v_email_list t_email;
begin
  select email bulk collect
    into v_email_list
    from employees
   where department_id = 50;
  dbms_output.put_line('获取email地址数：' || v_email_list.count);
end;
/
获取email地址数：45
●    fetch into 中使用bulk collect
declare
  type t_emp is table of employees%rowtype;
  v_emp_list t_emp;
  cursor c_emp is
    select * from employees where department_id = 50;
begin
  open c_emp;
  --在fetch into中使用bulk collect
  fetch c_emp bulk collect
    into v_emp_list;
  dbms_output.put_line('获取雇员总数：' || v_emp_list.count);
end;
/
获取雇员总数：45
●    在returning into中使用bulk collect
通过使用returning子句bulk collect可返回值给调用过程，不需要额外获取fetch语句。
--创建测试表
create table emptemp as select * from employees where department_id=50;
--执行过程
declare
  type t_id_list is table of number;
  type t_name_list is table of varchar2(32);
  ids   t_id_list;
  names t_name_list;
begin
  delete from emptemp
   where commission_pct is null
  returning employee_id, first_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('Employees #' || ids(i) || ': ' || names(i));
  end loop;
  commit;
exception
  when others then
    rollback;
end;