【转】ORACLE的BULKCOLLECT和FORALL

FORALL语句的一个关键性改进，它可大大简化代码，并且对于那些要在PL/SQL程序中更新很多行数据的程序来说，它可显着提高其性能。

　　1:用FORALL来增强DML的处理能力

　　Oracle为Oracle8i 中的PL/SQL引入了两个新的数据操纵语言（DML）语句：BULK COLLECT和FORALL。这两个语句在PL/SQL内部进行一种数组处理；BULK COLLECT提供对数据的高速检索，FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。Oracle数据库使用这些语句大大 减少了PL/SQL与SQL语句执行引擎的环境切换次数，从而使其性能有了显着提高。

　　使用BULK COLLECT，你可以将多个行引入一个或多个集合中，而不是单独变量或记录中。下面这个BULK COLLECT的实例是将标题中包含有"PL/SQL"的所有书籍检索出来并置于记录的一个关联数组中，它们都位于通向该数据库的单一通道中。

　　DECLARE

　　TYPE books_aat

　　IS TABLE OF book%ROWTYPE

　　INDEX BY PLS_INTEGER;

　　books books_aat;

　　BEGIN

　　SELECT *

　　BULK COLLECT INTO book

　　FROM books

　　WHERE title LIKE '%PL/SQL%';

　　...

　　END;

　　类似地，FORALL将数据从一个PL/SQL集合传送给指定的使用集合的表。下面的代码实例给出一个过程，即接收书籍信息的一个嵌套表，并将 该集合（绑定数组）的全部内容插入该书籍表中。注意，这个例子还利用了Oracle9i的FORALL的增强功能，可以将一条记录直接插入到表中。

　　BULK COLLECT和FORALL都非常有用，它们不仅提高了性能，而且还简化了为PL/SQL中的SQL操作所编写的代码。下面的多行FORALL INSERT相当清楚地说明了为什么PL/SQL被认为是Oracle数据库的最佳编程语言。

　　CREATE TYPE books_nt

　　IS TABLE OF book%ROWTYPE;

　　/

　　CREATE OR REPLACE PROCEDURE add_books (

　　books_in IN books_nt)

　　IS

　　BEGIN

　　FORALL book_index

　　IN books_in.FIRST .. books_in.LAST

　　INSERT INTO book

　　VALUES books_in(book_index);

　　...

　　END;

　　不过在Oracle数据库10g之前，以FORAll方式使用集合有一个重要的限制：该数据库从IN范围子句中的第一行到最后一行，依次读取集合的内容。如果在该范围内遇到一个未定义的行，Oracle数据库将引发ORA-22160异常事件：

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

　　对于FORALL的简单应用，这一规则不会引起任何麻烦。但是，如果想尽可能地充分利用FORALL，那么要求任意FORALL驱动数组都要依次填充可能会增加程序的复杂性并降低性能。

　　在Oracle数据库10g中，PL/SQL现在在FORALL语句中提供了两个新子句：INDICES OF与VALUES OF，它们使你能够仔细选择驱动数组中该由扩展DML语句来处理的行。

　　当绑定数组为稀疏数组或者包含有间隙时，INDICES OF会非常有用。该语句的语法结构为：

　　FORALL indx IN INDICES

　　OF sparse_collection

　　INSERT INTO my_table

　　VALUES sparse_collection (indx);

　　VALUES OF用于一种不同的情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。那么我就可以使用VALUES OF来指向我希望在DML操作中使用的值。该语句的语法结构为：

　　FORALL indx IN VALUES OF pointer_array

　　INSERT INTO my_table

　　VALUES binding_array (indx);

　　不用FOR循环而改用FORALL

　　假定我需要编写一个程序，对合格员工（由comp_analysis.is_eligible函数确定）加薪，编写关于不符合加薪条件的员工的报告并写入employee_history表。我在一个非常大的公司工作；我们的员工非常非常多。

　　对于一位PL/SQL开发人员来说，这并不是一项十分困难的工作。我甚至不需要使用BULK COLLECT或FORALL就可以完成这项工作，如清单 1所示，我使用一个CURSOR FOR循环和单独的INSERT及UPDATE语句。这样的代码简洁明了；不幸地是，我花了10分钟来运行此代码，我的"老式"方法要运行30分钟或更长 时间。

　　清单 1:

　　CREATE OR REPLACE PROCEDURE give_raises_in_department (

　　dept_in IN employee.department_id%TYPE

　　, newsal IN employee.salary%TYPE

　　)

　　IS

　　CURSOR emp_cur

　　IS

　　SELECT employee_id, salary, hire_date

　　FROM employee

　　WHERE department_id = dept_in;

　　BEGIN

　　FOR emp_rec IN emp_cur

　　LOOP

　　IF comp_analysis.is_eligible (emp_rec.employee_id)

　　THEN

　　UPDATE employee

　　SET salary = newsal

　　WHERE employee_id = emp_rec.employee_id;

　　ELSE

　　INSERT INTO employee_history

　　(employee_id, salary

　　, hire_date, activity

　　)

　　VALUES (emp_rec.employee_id, emp_rec.salary

　　, emp_rec.hire_date, 'RAISE DENIED'

　　);

　　END IF;

　　END LOOP;

　　END give_raises_in_department;

　好在我公司的数据库升级到了Oracle9i，而且更幸运的是，在最近的Oracle研讨会上（以及Oracle技术网站提供的非常不错的演示中）我了解到了批量处理方法。所以我决定使用集合与批量处理方法重新编写程序。写好的程序如清单 2所示。

　　清单 2:

　　1 CREATE OR REPLACE PROCEDURE give_raises_in_department (

　　2 dept_in IN employee.department_id%TYPE

　　3 , newsal IN employee.salary%TYPE

　　4 )

　　5 IS

　　6 TYPE employee_aat IS TABLE OF employee.employee_id%TYPE

　　7 INDEX BY PLS_INTEGER;

　　8 TYPE salary_aat IS TABLE OF employee.salary%TYPE

　　9 INDEX BY PLS_INTEGER;

　　10 TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE

　　1