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;

4.用于动态语句

declare
v_query_sql   varchar2(500);
type type_emp_table is table of emp%rowtype index by binary_integer;
v_emp_table type_emp_table;
begin
   v_query_sql := 'select * from emp';
   execute immediate v_query_sql bulk collect into v_emp_table;
   forall i in 1..v_emp_table.count
    insert into emp_bak values v_emp_table(i);
end;

declare
v_query_sql   varchar2(500);
type type_emp_table is table of emp%rowtype index by binary_integer;
v_emp_table type_emp_table;
type type_ename_table is table of emp.ename%type index by binary_integer;
v_ename_table type_ename_table;
begin
   update emp set flag=1 where deptno=20 returning flag bulk collect into v_ename_table;   
    for i in 1..v_ename_table.count loop
       dbms_output.put_line(v_ename_table(i));
   end loop;
end;