Oracle的bulk collect使用

       bulk collect的作用是将检索结果批量的、一次性的赋给集合变量，并把结果集从SQL引擎传给PL/SQL引擎。与每次获取一条数据，并每次都要将结果由SQL引擎传给PL/SQL引擎相比，可以很大程度上的节省开销。bulk collect可以和select into、fetch into、returning into一起使用，使用bulk collect后，into后的变量必须是集合型的。

1、select .. bulk collect into..

declare 
  TYPE name_tbl_type IS TABLE OF t_student.name%TYPE;
  v_name_tbl name_tbl_type;
begin
  select name bulk collect into v_name_tbl from t_student;
  
  for i in v_name_tbl.first..v_name_tbl.last loop
    dbms_output.put_line('Name: '||v_name_tbl(i));
  end loop;
end;

2、fetch .. bulk collect into..

declare 
  cursor cur_student is
       select gid, name from t_student;
       
  type student_rec_type is record(
      gid  number,
      name varchar2(100)
  );    
  type student_tbl_type is table of  student_rec_type;
  student_tbl student_tbl_type;
  
  LIMIT_NUM INTEGER := 3;
  
begin
  open cur_student;
  loop
      fetch cur_student bulk collect into student_tbl
          limit LIMIT_NUM; --使用limit子句限制提取数据量
          
      for i in student_tbl.first..student_tbl.last loop
        dbms_output.put_line('Gid: '||student_tbl(i).gid||', Name: '||student_tbl(i).name);
      end loop;
      exit when cur_student%NOTFOUND;
  end loop;
  close cur_student;
end;

3、returning..bulk collect into..

declare
  TYPE name_tbl_type IS TABLE OF t_student.name%type;
  TYPE gid_tbl_type IS TABLE OF t_student.gid%type;  
  v_name_tbl name_tbl_type := name_tbl_type('TONGZI', 'WENLI', 'DAZHUANG', 'ZHUANGSHAO');
  v_gid_tbl gid_tbl_type;
begin
  forall i in v_name_tbl.first..v_name_tbl.last
    insert into t_student(gid, name) values(seq_admin.nextval, v_name_tbl(i))
        returning gid BULK COLLECT into v_gid_tbl;--使用BULK COLLECT将列的值返回给数组
   commit;
   for i in 1..v_gid_tbl.count loop
     dbms_output.put_line('gid: '||v_gid_tbl(i)||', name:'||v_name_tbl(i)||' inserted');
   end loop;
end;

      如果采用动态SQL可以更好的提高查询性能，实现如下：

declare
  TYPE name_tbl_type IS TABLE OF t_student.name%type;
  TYPE gid_tbl_type IS TABLE OF t_student.gid%type INDEX BY BINARY_INTEGER;  
  v_name_tbl name_tbl_type := name_tbl_type('TONGZI', 'WENLI', 'DAZHUANG', 'ZHUANGSHAO');
  v_gid_tbl2 gid_tbl_type;
  v_gid_tbl gid_tbl_type;
begin
  FOR i IN 1..v_name_tbl.count LOOP
    v_gid_tbl2(i) := SEQ_ADMIN.NEXTVAL;
  END LOOP;  
  forall i in v_name_tbl.first..v_name_tbl.last
    execute immediate 'insert into t_student(gid, name) values(:1, :2) returning gid into :3' 
        using v_gid_tbl2(i), v_name_tbl(i)
        returning BULK COLLECT into v_gid_tbl;--使用动态SQL
   commit;
   for i in 1..v_gid_tbl.count loop
     dbms_output.put_line('gid: '||v_gid_tbl(i)||', name:'||v_name_tbl(i)||' inserted');
   end loop;
end;