PL/SQL学习: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_ntIS TABLE OF book%ROWTYPE;/CREATE OR REPLACE PROCEDURE add_books (books_in IN books_nt)ISBEGINFORALL 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%TYPE3     , newsal IN employee.salary%TYPE4       )5    IS6       TYPE employee_aat IS TABLE OF employee.employee_id%TYPE7          INDEX BY PLS_INTEGER;8       TYPE salary_aat IS TABLE OF employee.salary%TYPE9          INDEX BY PLS_INTEGER;10      TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE11        INDEX BY PLS_INTEGER;1213      employee_ids employee_aat;14      salaries salary_aat;15      hire_dates hire_date_aat;1617      approved_employee_ids employee_aat;1819     denied_employee_ids employee_aat;20     denied_salaries salary_aat;21     denied_hire_dates hire_date_aat;2223     PROCEDURE retrieve_employee_info24     IS25     BEGIN26        SELECT employee_id, salary, hire_date27        BULK COLLECT INTO employee_ids, salaries, hire_dates28            FROM employee29         WHERE department_id = dept_in;30     END;3132     PROCEDURE partition_by_eligibility33     IS34     BEGIN35        FOR indx IN employee_ids.FIRST .. employee_ids.LAST36        LOOP37           IF comp_analysis.is_eligible (employee_ids (indx))38           THEN39              approved_employee_ids (indx) := employee_ids (indx);40           ELSE41              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;4748     PROCEDURE add_to_history49     IS50     BEGIN51        FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST52           INSERT INTO employee_history53                       (employee_id54                      , salary55                      , hire_date, activity56                       )57                VALUES (denied_employee_ids (indx)58                      , denied_salaries (indx)59                      , denied_hire_dates (indx), 'RAISE DENIED'60                       );61     END;6263     PROCEDURE give_the_raise64     IS65     BEGIN66        FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST67           UPDATE employee68              SET salary = newsal69            WHERE employee_id = approved_employee_ids (indx);70     END;71 BEGIN72     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.00
表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就是这种特性的一个很好的例子