PL/SQL程序调优六

PL/SQL程序调优六（使用BULK COLLECT提取结果集到集合中较少循环的负载）

       在查询中使用BULK COLLECT关键字是提取结果集非常有效的方式。不使用循环，可以将结果存储到一个/多个集合中。可以在SELECT INTO和FETCH INTO语句以及RETURNING INTO子句中使用这些关键字。

       使用BULK COLLECT子句，INTO列表中的所有变量必须是集合。表的列可以是标量或组合值。

       如下：

DECLARE

   TYPE NumTab IS TABLE OF employees.employee_id%TYPE;

   TYPE NameTab IS TABLE OF employees.last_name%TYPE;

   enums NumTab;   -- No need to initialize the collections.

   names NameTab;  -- Values will be filled in by the SELECT INTO.

   PROCEDURE print_results IS

   BEGIN

     IF enums.COUNT = 0 THEN

       DBMS_OUTPUT.PUT_LINE('No results!');

     ELSE

       DBMS_OUTPUT.PUT_LINE('Results:');

       FOR i IN enums.FIRST .. enums.LAST

       LOOP

         DBMS_OUTPUT.PUT_LINE('  Employee #' || enums(i) || ': ' || names(i));

       END LOOP;

     END IF;

   END;

BEGIN

 -- Retrieve data for employees with Ids greater than 1000

   SELECT employee_id, last_name

     BULK COLLECT INTO enums, names FROM employees WHERE employee_id > 1000;

-- The data has all been brought into memory by BULK COLLECT

-- No need to FETCH each row from the result set

   print_results();

 -- Retrieve approximately 20% of all rows

   SELECT employee_id, last_name

      BULK COLLECT INTO enums, names FROM employees SAMPLE (20);

   print_results();

END;

/

       在这种情况下使用时，集合可以被自动初始化，不需要手工初始化。并且使用嵌套表和关联数组可以保持尽可能多的元素，它们会自动扩展。

       使用BULK COLLECT INTO同FETCH循环，不会抛出NO_DATA_FOUND异常，因此需要手工检查集合中是否有元素。

       为了防止结果集无限扩展，可以使用LIMIT或者ROWNUM序列限制处理的行数，或者使用SAMPLE提取随机的行数。如下：

DECLARE

   TYPE SalList IS TABLE OF employees.salary%TYPE;

   sals SalList;

BEGIN

-- Limit the number of rows to 50

   SELECT salary BULK COLLECT INTO sals FROM employees

      WHERE ROWNUM <= 50;

-- Retrieve 10% (approximately) of the rows in the table

   SELECT salary BULK COLLECT INTO sals FROM employees SAMPLE (10);

END;

/

       当然，对于很大的结果集，可以在游标中声明一次提取处理的行数，然后循环，如下：

DECLARE

  TYPE NameList IS TABLE OF employees.last_name%TYPE;

  TYPE SalList IS TABLE OF employees.salary%TYPE;

  CURSOR c1 IS

    SELECT last_name, salary FROM employees WHERE salary > 10000;

  names NameList;

  sals  SalList;

  TYPE RecList IS TABLE OF c1%ROWTYPE;

  recs    RecList;

  v_limit PLS_INTEGER := 10;

  PROCEDURE print_results IS

  BEGIN

    IF names IS NULL OR names.COUNT = 0 THEN

      -- check if collections are empty

      DBMS_OUTPUT.PUT_LINE('No results!');

    ELSE

      DBMS_OUTPUT.PUT_LINE('Results: ');

      FOR i IN names.FIRST .. names.LAST LOOP

        DBMS_OUTPUT.PUT_LINE('  Employee ' || names(i) || ': $' || sals(i));

      END LOOP;

    END IF;

  END;

BEGIN

  DBMS_OUTPUT.PUT_LINE('--- Processing all results at once ---');

  OPEN c1;

  FETCH c1 BULK COLLECT

    INTO names, sals;

  CLOSE c1;

  print_results();

  DBMS_OUTPUT.PUT_LINE('--- Processing ' || v_limit ||

                       ' rows at a time ---');

  OPEN c1;

  LOOP

    FETCH c1 BULK COLLECT

      INTO names, sals LIMIT v_limit;

    EXIT WHEN names.COUNT = 0;

    print_results();

  END LOOP;

  CLOSE c1;

  DBMS_OUTPUT.PUT_LINE('--- Fetching records rather than columns ---');

  OPEN c1;

  FETCH c1 BULK COLLECT

    INTO recs;

  FOR i IN recs.FIRST .. recs.LAST LOOP

    -- Now all the columns from the result set come from a single record

    DBMS_OUTPUT.PUT_LINE('  Employee ' || recs(i)

                         .last_name || ': $' || recs(i).salary);

  END LOOP;

END;

/

   

使用LIMIT 子句限制一个Bulk FETCH操作处理的行数

       需要注意，判断推出条件时需要使用RECORD.COUNT，而不能使用CURSOR%NOTFOUND。如下：

DECLARE

   TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

   CURSOR c1 IS SELECT employee_id FROM employees WHERE department_id = 80;

   empids    numtab;

   rows      PLS_INTEGER := 10;

BEGIN

  OPEN c1;

  LOOP -- the following statement fetches 10 rows or less in each iteration

    FETCH c1 BULK COLLECT INTO empids LIMIT rows;

    EXIT WHEN empids.COUNT = 0;

--  EXIT WHEN c1%NOTFOUND; -- incorrect, can omit some data

    DBMS_OUTPUT.PUT_LINE('------- Results from Each Bulk Fetch --------');

    FOR i IN 1..empids.COUNT LOOP

      DBMS_OUTPUT.PUT_LINE( 'Employee Id: ' || empids(i));

    END LOOP;

  END LOOP;

  CLOSE c1;

END;

       /

使用RETURNING INTO将DML结果集提取到集合中

CREATE TABLE emp_temp 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 emp_temp WHERE department_id = 30

      RETURNING employee_id, last_name BULK COLLECT INTO enums, names;

   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');

   FOR i IN enums.FIRST .. enums.LAST

   LOOP

      DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));

   END LOOP;

END;

/

结合使用FORALL和BULK COLLECT

       可以在FORALL语句中使用BULK COLLECT子句，输出的集合为FORALL的循环次数*平均每次DML处理的行。如下：

CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE

   TYPE NumList IS TABLE OF NUMBER;

   depts NumList := NumList(10,20,30);

   TYPE enum_t IS TABLE OF employees.employee_id%TYPE;

   TYPE dept_t IS TABLE OF employees.department_id%TYPE;

   e_ids enum_t;

   d_ids dept_t;

BEGIN

  FORALL j IN depts.FIRST..depts.LAST

    DELETE FROM emp_temp WHERE department_id = depts(j)

       RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;

  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');

  FOR i IN e_ids.FIRST .. e_ids.LAST

  LOOP

    DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));

  END LOOP;

END;

/

       每次执行的DML返回的行加到先前执行的结果集中。如果使用FOR，那么每次的结果集都会覆盖先前的结果集。但是在FOR中不能使用SELECT ... BULK COLLECT。