批量绑定（2.1）— FORALL

从 Oracle8i 开始，出现了 FORALL 语句，可以帮助我们更快地执行 DML 语句。它可以将一个或多个集合中的元素绑定到 DML 中，由 PL/SQL 引擎将它们批量发送给 SQL 引擎，从而降低 PL/SQL 引擎到 SQL 引擎的上下文交换（context switch）次数。

 

我们先来看一下 PL/SQL 块的执行过程：当 PL/SQL 运行时引擎处理一块代码时，它使用自己的引擎来执行过程化的代码，而将 SQL 语句发送给 SQL 引擎来执行；SQL 引擎执行完毕后，将结果再返回给 PL/SQL 引擎。PL/SQL 和 SQL 之间的交互，称为上下文交换（context switch）。每发生一次交换，就会带来一点额外的开销。下面是一个示意图：

 

图 1

 

从 Oracle 8i 开始，PL/SQL 得到了两点增强，可以将 PL/SQL 引擎和 SQL 引擎之间的多次上下文交换压缩为一次交换：

（1）FORALL，用于增强 PL/SQL 引擎到 SQL 引擎的交换。

（2）BULK COLLECT，用于增强 SQL 引擎到 PL/SQL 引擎的交换。（前面我们已经介绍过了）

 

使用 FORALL，可以将多个 DML 批量发送给 SQL 引擎来执行，最大限度地减少上下文交互所带来的开销。下面是 FORALL 的一个示意图：

 

图 2

 

1. 语法

 

FORALL index_row IN
   [ lower_bound ... upper_bound |
     INDICES OF indexing_collection |
     VALUES OF indexing_collection
   ]
   [ SAVE EXCEPTIONS ]
   sql_statement;

 

注意它不是 FOR，没有 LOOP 和 END LOOP。

 

index_row：Is the specified collection through which the FORALL will iterate

 

lower_bound：Is the starting index number (row or collection element) for the operation

 

upper_bound：Is the ending index number (row or collection element) for the operation

 

sql_statement：Is the SQL statement to be performed on each collection element

 

indexing_collection：Is the PL/SQL collection used to select the indices in the bind array referenced in the sql_statement; the INDICES OF and VALUES_OF alternatives are available starting in Oracle Database 10g Release 1

 

SAVE EXCEPTIONS：Is an optional clause that tells FORALL to process all rows, saving any exceptions that occur

 

使用 FORALL 时，应该遵循如下规则：

 

（1）FORALL 语句的执行体，必须是一个单独的 DML 语句，比如 INSERT, UPDATE, 或 DELETE。

 

（2）不要显示定义 index_row，它被 PL/SQL 引擎隐式定义为 PLS_INTEGER 类型，并且它的作用域也仅仅是 FORALL。

 

（3）这个 DML 语句必须与一个集合的元素相关，并且使用 FORALL 中的 index_row 来索引。注意不要因为 index_row 导致集合下标越界。

 

（4）lower_bound 和 upper_bound 之间是按照步进 1 来递增的，所以你的 FORALL 中的集合必须是紧密的，稀疏的集合将导致如下错误：

 

ORA-22160: element at index [3] does not exist

 

不过从 Oracle10g 开始，在 FORALL 中提供了使用稀疏集合的方法：INDICES OF 和 VALUES OF。

 

（5）在 sql_statement 中，不能单独地引用集合中的元素，只能批量地使用集合。比如，像下面这样使用集合，就会出现错误：

 

DECLARE
   TYPE employee_aat IS TABLE OF employee%ROWTYPE
      INDEX BY PLS_INTEGER;
   l_employees   employee_aat;
BEGIN
   FORALL l_index IN l_employees.FIRST .. l_employees.LAST
      INSERT INTO employee (employee_id, last_name)
        VALUES (l_employees (l_index).employee_id, l_employees (l_index).last_name);
END;

 

产生的错误如下：

 

PLS-00436: implementation restriction: cannot reference fields
of BULK In-BIND table of records

 

（6）在 sql_statement 中使用的集合，下标不能使用表达式。比如下面这种使用方式，就会出现错误：

 

DECLARE
   names name_varray := name_varray (  );
BEGIN
   FORALL indx IN names.FIRST .. names.LAST
      DELETE FROM emp WHERE ename = names(indx+10);
END;

 

产生的错误如下：

 

PLS-00430: FORALL iteration variable INDX is not allowed in this context

 

2. 上下文过度交换的问题

 

在学习 FORALL 之前，我们先看一个上下文过度交换的例子，这是当你处理集合中的信息时，可能会产生的问题。

 

我需要更新表 books 中的字段 page_count，并且书名和新的页码数据已经保存在两个集合中了（一个集合保存的是要更新的书名，另一个集合保存的是对应书名的新页码）。在 Oracle8i 之前，我只能这样写代码来实现：

 

CREATE OR REPLACE PROCEDURE order_books (
   isbn_in IN name_varray,
   new_count_in IN number_varray)
IS
BEGIN
   FOR indx IN isbn_in.FIRST .. isbn_in.LAST
   LOOP
      UPDATE books
         SET page_count = new_count_in (indx)
       WHERE isbn = isbn_in (indx);
   END LOOP;
END;

 

如果我需要更新100行数据，那么就需要100次的上下文交换，而在 Orace8i 之前，这是不可避免的。下面是这个多次上下文交换的示意图：

 

图 3

 

同样道理，从一个游标中获取多行记录，并保存到集合中，也是需要多次的上下文交换，这就是在出现 BULK COLLECT 之前，无法避免的情况：

 

DECLARE
   CURSOR major_polluters IS
      SELECT name, mileage
        FROM cars_and_trucks
       WHERE vehicle_type IN ('SUV', 'PICKUP');
   names name_varray := name_varray(  );
   mileages number_varray := number_varray(  );
BEGIN
   FOR bad_car IN major_polluters
   LOOP
      names.EXTEND;
      names (major_polluters%ROWCOUNT) := bad_car.name;
      mileages.EXTEND;
      mileages (major_polluters%ROWCOUNT) := bad_car.mileage;
   END LOOP;

 

   ... now work with data in the arrays ...
END;

 

如果你发现自己正在编写类似上面两种情况的代码，你应该考虑使用批量绑定，来减少上下文交换的次数。所以，应该注意你的程序中这样的代码：

 

* 在循环（一般都是 FOR 循环）中反复执行的 SQL 语句。

* 一些可以封装成绑定变量的参数（可以把这些变量保存到集合中，使用 FORALL）。

 

3. FORALL 举例

 

（1）我们重写一下（2）中的更新页码的例子：

 

CREATE OR REPLACE PROCEDURE order_books (
   isbn_in IN name_varray,
   new_count_in IN number_varray)
IS
BEGIN
   FORALL indx IN isbn_in.FIRST .. isbn_in.LAST
      UPDATE books
         SET page_count = new_count_in (indx)
       WHERE isbn = isbn_in (indx);
END;

 

比较一下，就会发现这里只是把 FOR 修改为 FORALL，然后去掉了 LOOP 和 END LOOP。图2 和 图3 展示了改变前后的上下文交互情况。

 

（2）三个不同的集合构成的插入操作。前两个集合带下标，第三个没有下标：

 

FORALL indx IN denial.FIRST .. denial.LAST
   INSERT INTO health_coverage
      VALUES (denial(indx), patient_name(indx), illnesses);

 

由于 PL/SQL 引擎仅会批量绑定带有下标的集合，对于第三个集合 illnesses，每条 INSERT 语句插入的都是相同的 illnesses 值。

 

（3）带有 RETURNING 的批量 DELETE。注意 FORALL 中的 RETURNING 必须使用 BULK COLLECT INTO：

 

CREATE OR REPLACE FUNCTION remove_emps_by_dept (deptlist dlist_t)
   RETURN enolist_t
IS
   enolist enolist_t;
BEGIN
   FORALL aDept IN deptlist.FIRST..deptlist.LAST
      DELETE FROM emp WHERE deptno IN deptlist(aDept)
         RETURNING empno BULK COLLECT INTO enolist;
   RETURN enolist;
END;

 

（4）在动态 INSERT 中，使用一个集合的索引来确定 sql_statement 中的另一个集合的元素：

 

FORALL indx IN INDICES OF l_top_employees
   EXECUTE IMMEDIATE 'INSERT INTO ' || l_table || ' VALUES (:emp_pky, :new_salary)
      USING l_new_salaries(indx).employee_id, l_new_salaries(indx).salary;