第五章 PL/SQL集合与记录（3）

十一、避免集合异常

大多情况下，如果我们引用了一个集合中不存在的元素，PL/SQL就会抛出一个预定义异常。例如：

DECLARE
  TYPE numlist IS TABLE OF NUMBER;

  nums   numlist;   -- atomically null
BEGIN
  /* Assume execution continues despite the raised exceptions. */
  nums(1)       := 1;   -- raises COLLECTION_IS_NULL (1)
  nums          := numlist(1, 2);   -- initialize table
  nums(NULL)    := 3;   -- raises VALUE_ERROR (2)
  nums(0)       := 3;   -- raises SUBSCRIPT_OUTSIDE_LIMIT (3)
  nums(3)       := 3;   -- raises SUBSCRIPT_BEYOND_COUNT (4)
  nums.DELETE(1);   -- delete element 1
  IF nums(1) = 1 THEN 
    ... -- raises NO_DATA_FOUND (5)
END;

第一句，嵌套表是空的；第二句，下标为空；三四句，下标超出合法范围之外；第五句，下标指向了一个被删除的元素。下表是一些异常情况的说明：

集合异常发生时机

COLLECTION_IS_NULL	调用一个空集合的方法
NO_DATA_FOUND	下标索引指向一个被删除的元素，或是关联数组中不存在的元素
SUBSCRIPT_BEYOND_COUNT	下标索引值超过集合中的元素个数
SUBSCRIPT_OUTSIDE_LIMIT	下标索引超过允许范围之外
VALUE_ERROR	下标索引值为空，或是不能转换成正确的键类型。当键被定义在 PLS_INTEGER的范围内，而下标索引值超过这个范围就可能抛 出这个异常

在某些情况下，如果我们为一个方法传递了一个无效的下标，并不会抛出异常。例如在使用DELETE方法的时候，我们向它传递NULL，它只是什么都没做而已。同样，用新值替换被删除的元素也不会引起NO_DATA_FOUND异常，如下例：

DECLARE
  TYPE numlist IS TABLE OF NUMBER;

  nums   numlist := numlist(10, 20, 30);   -- initialize table
BEGIN
  nums.DELETE(-1);   -- does not raise SUBSCRIPT_OUTSIDE_LIMIT
  nums.DELETE(3);   -- delete 3rd element
  DBMS_OUTPUT.put_line(nums.COUNT);   -- prints 2
  nums(3)    := 30;   -- allowed; does not raise NO_DATA_FOUND
  DBMS_OUTPUT.put_line(nums.COUNT);   -- prints 3
END;

打包集合类型和本地集合类型总是不兼容的。假设我们想调用下面的打包过程：

CREATE PACKAGE pkg1 AS
  TYPE NumList IS VARRAY(25) OF NUMBER(4);
  
  PROCEDURE delete_emps (emp_list NumList);
END pkg1;

CREATE PACKAGE BODY pkg1 AS
  PROCEDURE delete_emps (emp_list NumList) IS ...
    ...
END pkg1;

在运行下面PL/SQL块时，第二个过程调用会因参数的数量或类型错误(wrong number or types of arguments error)而执行失败。这是因为打包VARRAY和本地VARRAY类型不兼容，虽然它们的定义形式都是一样的：

DECLARE
  TYPE numlist IS VARRAY(25) OF NUMBER(4);

  emps    pkg1.numlist := pkg1.numlist(7369, 7499);
  emps2   numlist      := numlist(7521, 7566);
BEGIN
  pkg1.delete_emps(emps);
  pkg1.delete_emps(emps2);   -- causes a compilation error
END;

十二、使用集合批量绑定减少循环开销

如下图所示，PL/SQL引擎会执行过程化语句，但它把SQL语句发送给SQL引擎处理，然后SQL引擎把处理的结果返回给PL/SQL引擎。

PL/SQL和SQL引擎间的频繁切换会大大降低效率。典型的情况就是在一个循环中反复执行SQL语句。例如，下面的DELETE语句就会在FOR循环中被多次发送到SQL引擎中去：

DECLARE
  TYPE numlist IS VARRAY(20) OF NUMBER;

  depts   numlist := numlist(10, 30, 70);   -- department numbers
BEGIN
  ...
  FOR i IN depts.FIRST .. depts.LAST LOOP
    DELETE FROM emp
          WHERE deptno = depts(i);
  END LOOP;
END;

 

这种情况下，如果SQL语句影响了四行或更多行时，使用批量绑定就会显著地提高性能。

 

1、批量绑定如何提高性能

用SQL语句中为PL/SQL变量赋值称为绑定，PL/SQL绑定操作可以分为三种：

1. 内绑定(in-bind)：用INSERT或UPDATE语句将PL/SQL变量或主变量保存到数据库。
2. 外绑定(out-bind)：通过INSERT、UPDATE或DELETE语句的RETURNING子句的返回值为PL/SQL变量或主变量赋值。
3. 定义(define)：使用SELECT或FETCH语句为PL/SQL变量或主变量赋值。

DML语句可以一次性传递集合中所有的元素，这个过程就是批量绑定。如果集合有20个元素，批量绑定的一次操作就相当于执行20次SELECT、 INSERT、UPDATE或DELETE语句。这项技术是靠减少PL/SQL和SQL引擎间的切换次数来提高性能的。要对INSERT、UPDATE和 DELETE语句使用批量绑定，就要用PL/SQL的FORALL语句。

如果要在SELECT语句中使用批量绑定，我们就要在SELECT语句后面加上一个BULK COLLECT子句来代替INTO子句。

• 例一：对DELETE语句应用批量绑定

下面的DELETE语句只往SQL引擎中发送一次，即使是执行了三次DELETE操作：

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 emp
          WHERE deptno = depts(i);
END;

• 例二：对INSERT语句应用批量绑定

下例中，我们把5000个零件编号和名称放到索引表中。所有的表元素都向数据库插入两次：第一次使用FOR循环，然后使用FORALL语句。实际上，FORALL版本的代码执行速度要比FOR语句版本的快得多。

SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
Table created.
SQL> GET test.sql
1 DECLARE
2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
4 pnums NumTab;
5 pnames NameTab;
6 t1 NUMBER(5);
7 t2 NUMBER(5);
8 t3 NUMBER(5);
9
10
11 BEGIN
12 FOR j IN 1..5000 LOOP   -- load index-by tables
13 pnums(j) := j;
14 pnames(j) := 'Part No. ' || TO_CHAR(j);
15 END LOOP;
16 t1 := dbms_utility.get_time;
17 FOR i IN 1..5000 LOOP   -- use FOR loop
18 INSERT INTO parts VALUES (pnums(i), pnames(i));
19 END LOOP;
20 t2 := dbms_utility.get_time;
21 FORALL i IN 1..5000   -- use FORALL statement
22 INSERT INTO parts VALUES (pnums(i), pnames(i));
23 get_time(t3);
24 dbms_output.put_line('Execution Time (secs)');
25 dbms_output.put_line('---------------------');
26 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
27 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 32
FORALL: 3

十三、使用FORALL语句

关键字FORALL能让PL/SQL引擎在将集合发送到SQL引擎之前，批量导入集合元素。虽然FORALL也包含了迭代的模式，但它并不是简单的FOR循环。它的使用语法如下：

FORALL index IN lower_bound..upper_bound
  sql_statement;

index只能在FORALL语句块内作为集合下标使用。SQL语句必须是引用了集合元素的INSERT、UPDATE或DELETE语句。bound的有效范围是连续的索引号。在这个范围内，SQL引擎为每个索引号执行一次SQL语句。

• 例一：使用FORALL操作集合的部分内容

如下例所示，FORALL循环的边界值可作用于集合的部分内容，不必是全部的元素：

DECLARE
  TYPE numlist IS VARRAY(10) OF NUMBER;

  depts   numlist := numlist(20, 30, 50, 55, 57, 60, 70, 75, 90, 92);
BEGIN
  FORALL j IN 4 .. 7   -- bulk-bind only part of varray
    UPDATE emp
       SET sal = sal * 1.10
     WHERE deptno = depts(j);
END;

• 例二：使用集合下标索引的批量绑定

SQL语句能引用一个以上的集合。但是PL/SQL引擎的批量绑定只能为一个集合添加下标索引。所以，在下面的例子中，对于传递给函数median的集合sals，并没有使用到批量绑定。

FORALL i IN 1..20
  INSERT INTO emp2 VALUES (enums(i), names(i), median(sals), ...);

• 例三：使用FORALL为对象表添加数据

除了关系表之外，FORALL语句还可以操作对象表，如下例所示：

CREATE TYPE pnum AS OBJECT(
  n   NUMBER
);
/

CREATE TABLE partno OF pnum;
DECLARE
  TYPE numtab IS TABLE OF NUMBER;

  nums    numtab  := numtab(1, 2, 3, 4);

  TYPE pnumtab IS TABLE OF pnum;

  pnums   pnumtab := pnumtab(pnum(1), pnum(2), pnum(3), pnum(4));
BEGIN
  FORALL i IN pnums.FIRST .. pnums.LAST
    INSERT INTO partno
         VALUES (pnums(i));
  FORALL i IN nums.FIRST .. nums.LAST
    DELETE FROM partno
          WHERE n = 2 * nums(i);
  FORALL i IN nums.FIRST .. nums.LAST
    INSERT INTO partno
         VALUES (100 + nums(i));
END;

1、FORALL语句对回滚的影响

在FORALL语句中，如果SQL语句引起了一个未捕获异常，以前对数据库的所有操作都会被回滚。但是，如果我们捕获到被抛出的异常并加以处理，此次之前的操作就不会被回滚。举一个例子，假设我们创建了数据表用来存储部门编号和职别：

CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));

下一步，为刚才建立的数据表添加一些记录：

INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(20, 'Bookkeeper');   -- 10-char job title
INSERT INTO emp2 VALUES(30, 'Analyst');
INSERT INTO emp2 VALUES(30, 'Analyst');

然后，我们用下面的UPDATE语句为特定的职称加上七位字符串' (temp)'：

DECLARE
  TYPE numlist IS TABLE OF NUMBER;

  depts   numlist := numlist(10, 20, 30);
BEGIN
  FORALL j IN depts.FIRST .. depts.LAST
    UPDATE emp2
       SET job = job || ' (temp)'
     WHERE deptno = depts(j);
  -- raises a "value too large" exception
EXCEPTION
  WHEN OTHERS THEN
    COMMIT;
END;

SQL引擎会执行UPDATE语句三次，第一次成功，但在第二次会因字符串值'Bookkeeper (temp)'太长而无法赋给job字段，所以就会执行失败。这种情况下，只有第二条语句回滚。

只要有SQL语句抛出异常，FORALL语句就会终止执行。在上面的例子中，第二个UPDATE语句抛出了异常，第三个语句就不会被执行了。

2、使用%BULK_ROWCOUNT属性来计算FORALL语句所影响到的行数

处理SQL数据操作语句时，SQL引擎会隐式地打开一个名为SQL的游标。这个游标的标量属性%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT，能够提供最近一次执行的SQL数据操作语句信息。

SQL游标还有一个专门为FORALL设计的复合属性%BULK_ROWCOUNT。这个属性有些像索引表。它的第i个元素保存了第i次的 INSERT或UPDATE或DELETE语句所影响到的行数。如果第i次操作没有行被影响，%BULK_ROWCOUNT(i)就返回零。下面来看一个例子：

DECLARE
  TYPE numlist IS TABLE OF NUMBER;

  depts   numlist := numlist(10, 20, 50);
BEGIN
  FORALL j IN depts.FIRST .. depts.LAST
    UPDATE emp
       SET sal = sal * 1.10
     WHERE deptno = depts(j);
  -- Did the 3rd UPDATE statement affect any rows?
  IF SQL%BULK_ROWCOUNT(3) = 0 THEN ...
END;

FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范围在5到10的话，那么% BULK_ROWCOUNT的也是。对于插入操作来说，%BULK_ROWCOUNT的结果一般是1，但是对于INSERT ... SELECT这样的结构来说，%BULK_ROWCOUNT的值就有可能大于1。例如，下面的FORALL语句在循环插入数据的过程中，每次插入的行的个数都是不固定的，%BULK_ROWCOUNT可以记录每次插入的行数：

SET SERVEROUTPUT ON;

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;

  deptnums   num_tab;
BEGIN
  SELECT deptno
  BULK COLLECT INTO deptnums
    FROM dept;

  FORALL i IN 1 .. deptnums.COUNT
    INSERT INTO emp_by_dept
      SELECT empno, deptno
        FROM emp
       WHERE deptno = 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;
/

我们还可以在批量绑定中使用标量属性%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。例如，%ROWCOUNT会返回所有的SQL语句处理的总行数。

%FOUND和%NOTFOUND只是针对最后一次SQL语句执行的结果。但是，我们可以利用%BULK_ROWCOUNT来推断出每个单独语句的处理结果。当%BULK_ROWCOUNT(i)为零时，%FOUND和%NOTFOUND就分别为FALSE和TRUE。

3、使用%BULK_EXCEPTIONS属性来控制FORALL异常

PL/SQL为FORALL语句提供了一个异常控制机制。这个机制能让使用批量绑定的操作保存异常信息并不中断地执行直至完成操作。

为了让批量绑定在错误发生时还能够继续执行，需要在FORALL语句中添加关键字SAVE EXCEPTIONS，语法如下：

FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt | update_stmt | delete_stmt}

执行时发生的所有的异常信息都会保存在新的游标属性%BULK_EXCEPTIONS中。%BULK_EXCEPTIONS是一个记录类型集合，每个记录有两个域，分别是ERROR_INDEX和ERROR_CODE，前者保存FORALL语句的"循环"索引，后者保存对应的Oracle错误编号。

存放在%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果相关，异常的个数存放在%BULK_EXCEPTIONS 的COUNT属性中，%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

我们不使用关键字SAVE EXCEPTIONS时，如果异常发生，FORALL语句就会停止执行，SQL%BULK_EXCEPTIONS.COUNT的值就是一，SQL% BULK_EXCEPTIONS中只包含一条记录；如果没有异常发生，SQL%BULK_EXCEPTIONS.COUNT的值就是零。下面的例子演示了 %BULK_EXCEPTIONS的一些用法：

DECLARE
  TYPE numlist IS TABLE OF NUMBER;

  num_tab      numlist  := numlist(10, 0, 11, 12, 30, 0, 20, 199, 2, 0, 9, 1);
  ERRORS       NUMBER;
  dml_errors   EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
  FORALL i IN num_tab.FIRST .. num_tab.LAST SAVE EXCEPTIONS
    DELETE FROM emp
          WHERE sal > 500000 / num_tab(i);
EXCEPTION
  WHEN dml_errors THEN
    ERRORS    := SQL%BULK_EXCEPTIONS.COUNT;
    DBMS_OUTPUT.put_line('Number of errors is ' || 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(   'Oracle error is '
                           || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
END;

例子中，PL/SQL在i等于2、6、10时会抛出预定义异常ZERO_DIVIDE。当批量绑定完成时，SQL% BULK_EXCEPTIONS.COUNT就会返回3，SQL%BULK_EXCEPTIONS的内容就是(2,1476)，(6,1476)和 (10,1476)。如果想得到错误消息，我们可以把SQL%BULK_EXCEPTIONS(i).ERROR_CODE传递给错误报告函数 SQLERRM，这样就能得到下面的输出结果：

Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero