ORACLE的BULK COLLECT和FORALL

本文转自：http://blog.sina.com.cn/s/blog_4c044e910100dlw6.html

oracle 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
11 INDEX BY PLS_INTEGER;
12
13 employee_ids employee_aat;
14 salaries salary_aat;
15 hire_dates hire_date_aat;
16
17 approved_employee_ids employee_aat;
18
19 denied_employee_ids employee_aat;
20 denied_salaries salary_aat;
21 denied_hire_dates hire_date_aat;
22
23 PROCEDURE retrieve_employee_info
24 IS
25 BEGIN
26 SELECT employee_id, salary, hire_date
27 BULK COLLECT INTO employee_ids, salaries, hire_dates
28 FROM employee
29 WHERE department_id = dept_in;
30 END;
31
32 PROCEDURE partition_by_eligibility
33 IS
34 BEGIN
35 FOR indx IN employee_ids.FIRST .. employee_ids.LAST
36 LOOP
37 IF comp_analysis.is_eligible (employee_ids (indx))
38 THEN
39 approved_employee_ids (indx) := employee_ids (indx);
40 ELSE
41 denied_employee_ids (indx) := employee_ids (indx);
42 denied_salaries (indx) := salaries (indx);
43 denied_hire_dates (indx) := hire_dates (indx);
44 END IF;
45 END LOOP;
46 END;
47
48 PROCEDURE add_to_history
49 IS
50 BEGIN
51 FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST
52 INSERT INTO employee_history
53 (employee_id
54 , salary
55 , hire_date, activity
56 )
57 VALUES (denied_employee_ids (indx)
58 , denied_salaries (indx)
59 , denied_hire_dates (indx), 'RAISE DENIED'
60 );
61 END;
62
63 PROCEDURE give_the_raise
64 IS
65 BEGIN
66 FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST
67 UPDATE employee
68 SET salary = newsal
69 WHERE employee_id = approved_employee_ids (indx);
70 END;
71 BEGIN
72 retrieve_employee_info;
73 partition_by_eligibility;
74 add_to_history;
75 give_the_raise;
76 END give_raises_in_department;

扫一眼清单1 和清单2 就会清楚地认识到：改用集合和批量处理方法将增加代码量和复杂性。但是，如果你需要大幅度提升性能，这还是值得

的。下面，我们不看这些代码，我们来看一看当使用FORALL时，用什么来处理CURSOR FOR循环内的条件逻辑。

定义集合类型与集合

在清单 2中，声明段的第一部分（第6行至第11行）定义了几种不同的集合类型，与我将从员工表检索出的列相对应。我更喜欢基于employee%

ROWTYPE来声明一个集合类型，但是FORALL还不支持对某些记录集合的操作，在这样的记录中，我将引用个别字段。所以，我还必须为员工ID、

薪金和雇用日期分别声明其各自的集合。

接下来为每一列声明所需的集合（第13行至第21行）。首先定义与所查询列相对应的集合（第13行至第15行）：


employee_ids employee_aat;
salaries salary_aat;
hire_dates hire_date_aat;


然后我需要一个新的集合，用于存放已被批准加薪的员工的ID（第17行）：


approved_employee_ids employee_aat;


最后，我再为每一列声明一个集合（第19行至第21行），用于记录没有加薪资格的员工：


denied_employee_ids employee_aat;
denied_salaries salary_aat;
denied_hire_dates hire_date_aat;

深入了解代码

数据结构确定后，我们现在跳过该程序的执行部分（第72行至第75行），了解如何使用这些集合来加速进程。


retrieve_employee_info;
partition_by_eligibility;
add_to_history;
give_the_raise;


我编写此程序使用了逐步细化法（也被称为"自顶向下设计"）。所以执行部分不是很长，也不难理解，只有四行，按名称对过程中的每一步进

行了描述。首先检索员工信息（指定部门的所有员工）。然后进行划分，将要加薪和不予加薪的员工区分出来。完成之后，我就可以将那些不

予加薪的员工添加至员工历史表中，对其他员工进行加薪。

以这种方式编写代码使最终结果的可读性大大增强。因而我可以深入到该程序中对我有意义的任何部分。

有了已声明的集合，我现在就可以使用BULK COLLECT来检索员工信息（第23行至第30行）。这一部分有效地替代了CURSOR FOR循环。至此，数

据被加载到集合中。

划分逻辑（第32行至第46行）要求对刚刚填充的集合中的每一行进行检查，看其是否符合加薪条件。如果符合，我就将该员工ID从查询填充的

集合复制到符合条件的员工的集合。如果不符合，则复制该员工ID、薪金和雇用日期，因为这些都需要插入到employee_history表中。

初始数据现在已被分为两个集合，可以将其分别用作两个不同的FORALL语句（分别从第51行和第66行开始）的驱动器。我将不合格员工的集合

中的数据批量插入到employee_history（add_to_history）表中，并通过give_the_raise过程，在employee表中批量更新合格员工的信息。

最后再仔细地看一看add_to_history（第48行至第61行），以此来结束对这个重新编写的程序的分析。FORALL语句（第51行）包含一个IN子句

，它指定了要用于批量INSERT的行号范围。在对程序进行第二次重写的说明中，我将把用于定义范围的集合称为"驱动集合"。但在

add_to_history的这一版本中，我简单地假定：使用在denied_employee_ids中定义的所有行。在INSERT自身内部，关于不合格员工的三个集

合都会被用到；我将把这些集合称为"数据集合"。可以看到，驱动集合与数据集合无需匹配。在学习Oracle数据库10g的新特性时，这是一个关

键点。

结果，清单 2 的行数大约是清单 1行数的2倍，但是清单 2 中的代码会在要求的时间内运行。在使用Oracle数据库10g之前，在这种情况下，

我只会对能够在这一时间内运行代码并开始下一个任务这一点感到高兴。

不过，有了Oracle数据库10g中最新版的PL/SQL，现在我就可以在性能、可读性和代码量方面作出更多的改进。
将VALUES OF用于此过程

在Oracle数据库10g中，可以指定FORALL语句使用的驱动集合中的行的子集。可以使用以下两种方法之一来定义该子集：


将数据集合中的行号与驱动集合中的行号进行匹配。你需要使用INDICES OF子句。
将数据集合中的行号与驱动集合中所定义行中找到的值进行匹配。这需要使用VALUES OF子句。
在对give_raises_in_department进行第二次和最后一次改写中我将使用VALUES OF子句。清单 3 包含这个版本的全部代码。我将略过这一程序

中与前一版本相同的部分。

从声明集合开始，请注意我不再另外定义集合来存放合格的和不合格的员工信息，而是在清单 3 （第17行至第21行）中声明两个"引导"集合：

一个用于符合加薪要求的员工，另一个用于不符合加薪要求的员工。这两个集合的数据类型都是布尔型；不久将会看到，这些集合的数据类型

与FORALL语句毫无关系。FORALL语句只关心定义了哪些行。 在员工表中拥有50 000行信息的give_raises_in_department的三种执行方法的占

用时间 执行方法 用时
CURSOR FOR循环 00:00:38.01
Oracle数据库10g之前的批量处理 00:00:06.09
Oracle数据库10g的批量处理 00:00:02.06

在员工表中拥有100,000行数据的give_raises_in_department的三种执行方法的占用时间 执行方法用时
CURSOR FOR循环 00:00:58.01
Oracle数据库10g之前的批量处理 00:00:12.00
Oracle数据库10g的批量处理 00:00:05.05


表1：处理50,000行和100,000行数据的用时测试结果

retrieve_employee_info子程序与前面的相同，但是对数据进行划分的方式完全不同（第32行至第44行）。我没有将记录从一个集合复制到另

一个集合（这个操作相对较慢），而只是确定与员工ID集合中的行号相匹配的相应引导集合中的行（通过为其指定一个TRUE值）。

现在可以在两个不同FORALL语句（由第49行和第65行开始）中，将approved_list和denied_list集合用作驱动集合。

为了插入到employee_history表中，我使用了如下语句：


FORALL indx IN VALUES OF denied_list


为了进行更新（给员工进行加薪），我使用这一格式：


FORALL indx IN VALUES OF approved_list


在这两个DML语句中，数据集合是在BULK COLLECT 检索步骤中填充的最初的集合；没有进行过复制。利用VALUES OF，Oracle数据库在这些数据

集合的行中进行筛选，仅使用行号与驱动集合中行号相匹配的行
利用本程序中的VALUES OF，可以避免复制对全部记录进行复制，而是用行号的一个简单列表来替换它们。对于大型数组，进行这些复制的开销

是非常可观的。为了测试Oracle数据库10g的优越性，我装入employee表并对50,000行和100,000行的数据运行测试。为了模拟更多的现实情况

，我将Oracle数据库10g之前的批量处理的执行方法作了修改以进行集合内容的多次复制。然后我使用SQL*Plus SET TIMING ON来显示运行各个

不同的执行方法所用的时间。表 1 给出了结果。

从这些时间测定得到的结论非常清楚：由单个DML语句变为批量处理将大幅缩短耗用时间，数据为50,000行时的用时由38秒减为6秒，数据为

100,000行时的用时由58秒减为12秒。而且，通过使用VALUES OF来避免复制数据，我可以将用时缩短一半左右。

即使没有性能上的改进，VALUES OF及其同类子句--INDICES OF也提高了PL/SQL语言的灵活性，使开发人员能够更轻松地编写出更直观和更容易

维护的代码。

在产品寿命这一点上，PL/SQL是一种成熟且功能强大的语言。因而，其很多新特性都是逐渐增加和改进而成的。不过，这些新特性还是使应用

程序的性能和开发人员的开发效率有了重大改变。VALUES OF就是这种特性的一个很好的例子