PL/SQL程序调优五

PL/SQL程序调优五（使用BULK SQL减少DML语句的循环负载）

       PL/SQL发送SQL语句到SQL引擎执行，然后SQL返回结果到PL/SQL。可以通过使用PL/SQL的bulk SQL特征最小化PL/SQL和SQL之间通信的负载。FORALL以成批的方式发送INSERT, UPDATE，DELETE到SQL，而不是一次一个语句。BULK COLLECT则以批处理方式取回SQL的结果。

       在SQL中将值赋给PL/SQL变量的方式称为绑定，PL/SQL绑定分为三类：

       ·向内绑定：PL/SQL变量和主机变量通过INSERT或UPDATE被存储到数据库中；

       ·向外绑定：通过INSERT, UPDATE, DELETE语句的RETURNING子句将数据库的值赋予PL/SQL变量或主机变量；

       ·定义：通过SELECT或FETCH将值赋予PL/SQL变量或主机变量；

       Bulk SQL使用PL/SQL集合，一次性传递大量数据到变量。

       将循环中的INSERT, UPDATE, DELETE语句包含在PL/SQL FORALL中以提高性能；

       将SELECT中的INTO使用BULK COLLECT INTO替代以提高性能；

使用FORALL语句

       FORALL允许很高效的运行多个DML语句，其只能重复单独的DML语句，不像单独的LOOP循环。

       通常边界声明连续索引号的范围，如果索引号是不连续的，如删除了某些元素，可以使用INDICES OF或VALUES OF子句仅仅迭代那些真正存在的索引值。

       INDICES OF迭代集合中的所有索引值，或者边界内的所有索引值。

       VALUES OF引用一个由BINARY_INTEGER或PLS_INTEGER索引并且它们的元素类型是BINARY_INTEGER或PLS_INTEGER的元素。如下：

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE

   TYPE NumList IS VARRAY(20) OF NUMBER;

   depts NumList := NumList(10, 30, 70);  -- department numbers

BEGIN

   FORALL i IN depts.FIRST..depts.LAST

      DELETE FROM employees_temp WHERE department_id = depts(i);

   COMMIT;

END;

/

       通过使用FOR循环和FORALL可以进行比较：

SQL> CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));

 

Table created

 

SQL> CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));

 

Table created

 

SQL> DECLARE

  2    TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;

  3    TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;

  4    pnums  NumTab;

  5    pnames NameTab;

  6    iterations CONSTANT PLS_INTEGER := 500;

  7    t1 INTEGER;

  8    t2 INTEGER;

  9    t3 INTEGER;

 10  BEGIN

 11    FOR j IN 1..iterations LOOP  -- load index-by tables

 12       pnums(j) := j;

 13       pnames(j) := 'Part No. ' || TO_CHAR(j);

 14    END LOOP;

 15    t1 := DBMS_UTILITY.get_time;

 16    FOR i IN 1..iterations LOOP  -- use FOR loop

 17       INSERT INTO parts1 VALUES (pnums(i), pnames(i));

 18    END LOOP;

 19    t2 := DBMS_UTILITY.get_time;

 20    FORALL i IN 1..iterations  -- use FORALL statement

 21       INSERT INTO parts2 VALUES (pnums(i), pnames(i));

 22    t3 := DBMS_UTILITY.get_time;

 23    DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');

 24    DBMS_OUTPUT.PUT_LINE('---------------------');

 25    DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));

 26    DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));

 27    COMMIT;

 28  END;

 29  /

 

Execution Time (secs)

---------------------

FOR loop: .06

FORALL:   .01

 

PL/SQL procedure successfully completed

      

       FORALL也可以仅仅循环一部分，不需要循环全部的元素。如下：

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE

   TYPE NumList IS VARRAY(10) OF NUMBER;

   depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);

BEGIN

   FORALL j IN 4..7  -- use only part of varray

      DELETE FROM employees_temp WHERE department_id = depts(j);

   COMMIT;

END;

/

       有可能在使用FORALL前需要删除某些元素，INDICES OF子句通过循环仅剩余的元素处理稀疏集合。

       某时候也可能保留原来的集合不变，仅仅处理某些元素，以不同顺序处理，或者处理某些元素多次。在这种情况下可以使用VALUES OF子句建立一个简单的集合，其元素作为指针执行原来的集合。注意：在默认情况下，FORALL不能在稀疏矩阵上工作。

       如下：

-- Create empty tables to hold order details

CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));

CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;

CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;

 

DECLARE

  -- Make collections to hold a set of customer names and order amounts.

  SUBTYPE cust_name IS valid_orders.cust_name%TYPE;

  TYPE cust_typ IS TABLE OF cust_name;

  cust_tab cust_typ;

  SUBTYPE order_amount IS valid_orders.amount%TYPE;

  TYPE amount_typ IS TABLE OF NUMBER;

  amount_tab amount_typ;

  -- Make other collections to point into the CUST_TAB collection.

  TYPE index_pointer_t IS TABLE OF PLS_INTEGER;

  big_order_tab      index_pointer_t := index_pointer_t();

  rejected_order_tab index_pointer_t := index_pointer_t();

  PROCEDURE setup_data IS

  BEGIN

    -- Set up sample order data, including some invalid orders and some 'big' orders.

    cust_tab   := cust_typ('Company1',

                           'Company2',

                           'Company3',

                           'Company4',

                           'Company5');

    amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);

  END;

BEGIN

  setup_data();

  DBMS_OUTPUT.PUT_LINE('--- Original order data ---');

  FOR i IN 1 .. cust_tab.LAST LOOP

    DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||

                         amount_tab(i));

  END LOOP;

  -- Delete invalid orders (where amount is null or 0).

  FOR i IN 1 .. cust_tab.LAST LOOP

    IF amount_tab(i) is null or amount_tab(i) = 0 THEN

      cust_tab.delete(i);

      amount_tab.delete(i);

    END IF;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('--- Data with invalid orders deleted ---');

  FOR i IN 1 .. cust_tab.LAST LOOP

    IF cust_tab.EXISTS(i) THEN

      DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) ||

                           ': $' || amount_tab(i));

    END IF;

  END LOOP;

  -- Because the subscripts of the collections are not consecutive, use

  -- FORALL...INDICES OF to iterate through the actual subscripts,

  -- rather than 1..COUNT

  FORALL i IN INDICES OF cust_tab

    INSERT INTO valid_orders

      (cust_name, amount)

    VALUES

      (cust_tab(i), amount_tab(i));

  -- Now process the order data differently

  -- Extract 2 subsets and store each subset in a different table

  setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.

  FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP

    IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN

      rejected_order_tab.EXTEND; -- Add a new element to this collection

      -- Record the subscript from the original collection

      rejected_order_tab(rejected_order_tab.LAST) := i;

    END IF;

    IF amount_tab(i) > 2000 THEN

      big_order_tab.EXTEND; -- Add a new element to this collection

      -- Record the subscript from the original collection

      big_order_tab(big_order_tab.LAST) := i;

    END IF;

  END LOOP;

  -- Now it's easy to run one DML statement on one subset of elements,

  -- and another DML statement on a different subset.

  FORALL i IN VALUES OF rejected_order_tab

    INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));

  FORALL i IN VALUES OF big_order_tab

    INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));

  COMMIT;

END;

/

FORALL对ROLLBACK的影响

       在一个FORALL中，如果任何SQL语句抛出未处理的异常，所有先前所作的改变都会被回滚。但是如果抛出的异常被捕获了并且处理了，语句将被回滚到每个SQL语句执行前的隐示保存点或者回滚，依赖于编程。如下：

       CREATE TABLE emp_temp (deptno NUMBER(2), job VARCHAR2(18));

DECLARE

  TYPE NumList IS TABLE OF NUMBER;

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

BEGIN

  INSERT INTO emp_temp VALUES (10, 'Clerk');

  -- Lengthening this job title causes an exception

  INSERT INTO emp_temp VALUES (20, 'Bookkeeper');

  INSERT INTO emp_temp VALUES (30, 'Analyst');

  COMMIT;

  FORALL j IN depts.FIRST .. depts.LAST -- Run 3 UPDATE statements.

    UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j);

  -- raises a "value too large" exception

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Problem in the FORALL statement.');

    COMMIT; -- Commit results of successful updates.

END;

/

使用%BULK_ROWCOUNT属性确定FORALL影响的行数

       SQL游标的复合属性，%BULK_ROWCOUNT，与FORALL一起使用。这个属性的工作类似于关联数组：SQL%BULK_ROWCOUNT(i)代表ith执行的dml处理的行数。

CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE

   TYPE NumList IS TABLE OF NUMBER;

   depts NumList := NumList(30, 50, 60);

BEGIN

   FORALL j IN depts.FIRST..depts.LAST

      DELETE FROM emp_temp WHERE department_id = depts(j);

-- How many rows were affected by each DELETE statement?

   FOR i IN depts.FIRST..depts.LAST

   LOOP

      DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' ||

         SQL%BULK_ROWCOUNT(i) || ' rows.');

   END LOOP;

END;

/

       FORALL语句和%BULK_ROWCOUNT属性使用相同的索引，如FORALL使用的范围从5..10，那么%BULK_ROWCOUNT也使用这些索引。如果FORALL使用INDICES OF处理稀疏集合，%BULK_ROWCOUNT将具有相应的索引。如果使用VALUES OF处理一部分元素，%BULK_ROWCOUNT也将具有相应的索引。如果索引集合中包含重复的值，某些DML可能会使用相同的索引执行多次，那么相应的%BULK_ROWCOUNT索引代表的值将是这些使用相同的索引执行的DML影响的总行数。

       通常情况下，插入的%BULK_ROWCOUNT为1，如果为INSERT ... SELECT可能更多。

       如下：

CREATE TABLE emp_by_dept AS SELECT employee_id, department_id

   FROM employees WHERE 1 = 0;

DECLARE

  TYPE dept_tab IS TABLE OF departments.department_id%TYPE;

  deptnums dept_tab;

BEGIN

  SELECT department_id BULK COLLECT INTO deptnums FROM departments;

  FORALL i IN 1..deptnums.COUNT

     INSERT INTO emp_by_dept

        SELECT employee_id, department_id FROM employees

           WHERE department_id = deptnums(i);

  FOR i IN 1..deptnums.COUNT LOOP

-- Count how many rows were inserted for each department; that is,

-- how many employees are in each department.

   DBMS_OUTPUT.PUT_LINE('Dept '||deptnums(i)||': inserted '||

                          SQL%BULK_ROWCOUNT(i)||' records');

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Total records inserted: ' || SQL%ROWCOUNT);

END;

/

       在执行FORALL后，还可以使用%FOUND, %NOTFOUND，%ROWCOUNT，需要注意，%FOUND和%NOTFOUND仅仅引用最后执行的SQL的影响。如，当%BULK_ROWCOUNT(i)为0时，%FOUND和%NOTFOUND分别为FALSE和TRUE。

使用%BULK_EXCEPTIONS属性处理FORALL异常

       PL/SQL提供了一种机制处理FORALL执行期间发生的异常，该机制允许批绑定操作保存关于异常的信息然后继续处理。

       为了让批绑定操作在发生错误时继续处理，在绑定后，DML前，增加SAVE EXCEPTIONS关键字到FORALL中，不过编程者也应该提供一个异常处理器跟踪发生的异常。

       执行期间发生的所有异常保存在%BULK_EXCEPTIONS中，以存储为一个集合的记录，每个纪录有两个元素：

       %BULK_EXCEPTIONS(i).ERROR_INDEX：错误的位置；

       %BULK_EXCEPTIONS(i).ERROR_CODE：相应的错误码；

       异常的数量存储在%BULK_EXCEPTIONS.COUNT中。注意，%BULK_EXCEPTIONS存储最后执行的FORALL语句的异常。但是其中并没有保存单独的错误消息，可以通过SQLERRM与ERROR_CODE查看具体的消息。

       如果忽略了SAVE EXCEPTIONS关键字，FORALL将停止执行，在这种情况下，SQL%BULK_EXCEPTIONS.COUNT返回1，SQL%BULK_EXCEPTIONS也包含一条记录；如果没有发生异常，SQL%BULK_EXCEPTIONS.COUNT将返回0。

       如下：

-- create a temporary table for this example

CREATE TABLE emp_temp AS SELECT * FROM employees;

 

DECLARE

   TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;

   emp_sr empid_tab;

-- create an exception handler for ORA-24381

   errors NUMBER;

   dml_errors EXCEPTION;

   PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN

   SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp

         WHERE hire_date < '30-DEC-94';

-- add '_SR' to the job_id of the most senior employees

     FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS

       UPDATE emp_temp SET job_id = job_id || '_SR'

          WHERE emp_sr(i) = emp_temp.employee_id;

-- If any errors occurred during the FORALL SAVE EXCEPTIONS,

-- a single exception is raised when the statement completes.

 

EXCEPTION

  WHEN dml_errors THEN -- Now we figure out what failed and why.

   errors := SQL%BULK_EXCEPTIONS.COUNT;

   DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || errors);

   FOR i IN 1..errors LOOP

      DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||

         'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

          DBMS_OUTPUT.PUT_LINE('Error message is ' ||

          SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

   END LOOP;

END;

/