Oracle 大量繫結forall bulk collect用法

採用bulk collect 可以將查詢結果一次性的載入到collections中，而不是通過CURSOR一條一條地處理，可以在select into,fetchinto,returning into語句中使用bulk collect.
  注意在使用bulk collect時，所有的into變數都必須是collections.
----在 select into語句中使用bulk collect
   DECLARE
   TYPE SalList IS TABLE OF emp.sal%TYPE;
   sals SalList;
   BEGIn
   SELECT sal BULK COLLECT INTO sals FROM emp WHERE ROWNUM<=100;
   SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;

----在fetch into 中使用bulk collect
    DECLARE
    TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
    dept_recs DeptRecTab;
    CURSOR c1 IS
    SELECT deptno,dname,loc FROM dept WHERE deptno>10;
    BEGIN
    OPEN c1;
    FETCH c1 BULK COLLECT INTO dept_recs;
    END;
/
----在returning into 中使用bulk collect
    CREATE TABLE emp2 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 FROM emp2 WHERE department_id=30;
    RETURNING employee_id,last_name BULK COLLECT INTO enums,names;
    DBMS_OUTPUT.PUT_LINE('Deleted' ||SQL%ROWCOUTN||'rows;');
    FOR i IN enms.FIRST..enums.LAST
    LOOP
    DBMS_OUTPUT.PUT_LINE('Employee #'||enums(i)||';'||names(i));
     END LOOP;
    END;
/

Oracle 大量繫結forall bulk collect
  大量繫結(Bulk binds)可以通過減少在pl/sql 和sql引擎之間的環境切換(context switches)提高了效能

   量綁定(Bulk binds)包括:
  (i)Input collections,use the FORALL statement，用來改善DML(INSERT,UPDATE和DELETE)操作的效能。
   (ii)Output collections,use the BULK　COLLECT clause,一般用來提高查詢(SELECT)的效能