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;