PL/SQL中的BULK COLLECT/FORALL

本文参考文档：bulk collect and forall

我们通常会在PL/SQL中同时使用PL/SQL和SQL statements。PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine. 而这降低了效率。BULK COLLECT和FORALL之所以能优化批量操作的性能，原因在于使用它们可以避免the context switching between PL/SQL and SQL by doing as much of the work as possible within SQL.

BULK COLLECT:  SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval

FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly

下面是使用两者的例子：

  CREATE OR REPLACE PROCEDURE increase_salary (
      department_id_in   IN employees.department_id%TYPE,
      increase_pct_in    IN NUMBER)
   IS
      TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE
              INDEX BY PLS_INTEGER; 
    l_employee_ids   employee_ids_t;
    l_eligible_ids   employee_ids_t;

     l_eligible       BOOLEAN;
  BEGIN
     SELECT employee_id
       BULK COLLECT INTO l_employee_ids -- 重点
       FROM employees
      WHERE department_id = increase_salary.department_id_in;

     FOR indx IN 1 .. l_employee_ids.COUNT
     LOOP
        check_eligibility (l_employee_ids (indx),
                           increase_pct_in,
                           l_eligible);

        IF l_eligible
        THEN
           l_eligible_ids (l_eligible_ids.COUNT + 1) :=
              l_employee_ids (indx);
        END IF;
     END LOOP;

     FORALL indx IN 1 .. l_eligible_ids.COUNT -- 重点
        UPDATE employees emp
           SET emp.salary =
                    emp.salary
                  + emp.salary * increase_salary.increase_pct_in
         WHERE emp.employee_id = l_eligible_ids (indx);
  END increase_salary;

另外我们在使用游标读取数据时，Oracle会自动使用类似BULK COLLECT查询来优化FOR LOOP的性能。