本文参考文档: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的性能。