【转，改】forall与bulk collect

来源：互联网发布：金蝶软件成都分公司编辑：程序博客网时间：2024/06/05 06:22

FORALL语句

一、FORALL与 BULK COLLECT介绍

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

用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)

BEGIN

FORALL book_index

IN books_in.FIRST .. books_in.LAST

INSERT INTO book

VALUES books_in(book_index);

...

END;

二、10G版本FORALL 的改进

不过在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

)

CURSOR emp_cur

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;

13 employee_ids employee_aat;

14 salaries salary_aat;

15 hire_dates hire_date_aat;

17 approved_employee_ids employee_aat;

19 denied_employee_ids employee_aat;

20 denied_salaries salary_aat;

21 denied_hire_dates hire_date_aat;

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;

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;

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;

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 就会清楚地认识到：改用集合和批量处理方法将增加代码量和复杂性。但是，如果你需要大幅度提升性能，这还是值得的。

清单3，indices of和values of

CREATE OR REPLACE PROCEDURE give_raises_in_department (
     dept_in IN emp.deptno%TYPE
   , newsal IN emp.sal%TYPE
)
IS
     TYPE emp_aat IS TABLE OF emp.empno%TYPE
        INDEX BY PLS_INTEGER;
     TYPE sal_aat IS TABLE OF emp.sal%TYPE
        INDEX BY PLS_INTEGER;
     TYPE hiredate_aat IS TABLE OF emp.hiredate%TYPE
        INDEX BY PLS_INTEGER;

     TYPE emp_indx IS TABLE OF PLS_INTEGER
        INDEX BY PLS_INTEGER;

     empnos emp_aat;
     salaries sal_aat;
     hiredates hiredate_aat;

     approved_empnos emp_indx;

     denied_empnos emp_aat;
     denied_salaries sal_aat;
     denied_hiredates hiredate_aat;

     PROCEDURE retrieve_emp_info
     IS
     BEGIN
        SELECT empno, sal, hiredate
        BULK COLLECT INTO empnos, salaries, hiredates
          FROM emp
         WHERE deptno = dept_in;
     END;

     PROCEDURE partition_by_eligibility
     IS
     BEGIN
        FOR indx IN empnos.FIRST .. empnos.LAST
        LOOP
           IF salaries (indx)<newsal
           THEN
              approved_empnos (indx) := indx;
           ELSE
              denied_empnos (indx) := empnos (indx);
              denied_salaries (indx) := salaries (indx);
              denied_hiredates (indx) := hiredates (indx);
           END IF;
        END LOOP;
     END;

     PROCEDURE add_to_history
     IS
     BEGIN
        FORALL indx IN indices of denied_empnos
           INSERT INTO emp_history
                       (empno
                      , sal
                      , hiredate
                       )

                VALUES (denied_empnos (indx)
                      , denied_salaries (indx)
                      , denied_hiredates (indx)
                       );
     END;

     PROCEDURE give_the_raise
     IS
     BEGIN
        FORALL indx IN values of approved_empnos
           UPDATE emp
              SET sal = newsal
            WHERE empno = empnos (indx);
     END;


BEGIN
     retrieve_emp_info;
     partition_by_eligibility;
     add_to_history;
     give_the_raise;
END give_raises_in_department;