在PL/SQL编程中，`FORALL` 是一种强大的批量操作语句，用于提高性能和效率。与传统的循环相比，`FORALL` 可以一次性执行大量数据库操作，如插入、更新或删除数据，从而显著减少执行时间。
什么是 `FORALL`？
`FORALL` 是一个声明性控制结构，允许用户将多个SQL语句组合成一个单一的操作。它通过预处理语句来减少网络往返次数，因此非常适合需要处理大量数据的情况。
基本语法
```sql
FORALL index IN lower_bound..upper_bound [SAVE EXCEPTIONS]
SQL_statement;
```
- index：是索引变量，通常是一个整数类型的变量。
- lower_bound..upper_bound：定义了索引的范围。
- SAVE EXCEPTIONS：可选参数，用于捕获并存储失败的记录。
示例 1：批量插入数据
假设我们有一个员工表 `EMPLOYEES`，现在需要向该表中插入一批新员工信息。我们可以使用 `FORALL` 来实现这一操作。
```sql
DECLARE
TYPE emp_array IS TABLE OF EMPLOYEES%ROWTYPE;
new_employees emp_array := emp_array();
BEGIN
-- 添加员工数据
new_employees.EXTEND(3);
new_employees(1) := EMPLOYEES%ROWTYPE{100, 'John Doe', 'Manager'};
new_employees(2) := EMPLOYEES%ROWTYPE{101, 'Jane Smith', 'Developer'};
new_employees(3) := EMPLOYEES%ROWTYPE{102, 'Emily Johnson', 'Designer'};
-- 批量插入
FORALL i IN new_employees.FIRST .. new_employees.LAST
INSERT INTO EMPLOYEES VALUES new_employees(i);
END;
/
```
在这个例子中，我们首先定义了一个 `emp_array` 类型的集合，然后向其中添加了三个员工记录。最后，通过 `FORALL` 将这些记录一次性插入到 `EMPLOYEES` 表中。
示例 2：批量更新数据
同样地，我们也可以使用 `FORALL` 来更新数据库中的记录。例如，我们需要根据员工ID更新他们的职位。
```sql
DECLARE
TYPE emp_id_array IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE;
TYPE emp_title_array IS TABLE OF EMPLOYEES.TITLE%TYPE;
emp_ids emp_id_array := emp_id_array();
emp_titles emp_title_array := emp_title_array();
BEGIN
-- 添加员工ID和职位
emp_ids.EXTEND(2);
emp_titles.EXTEND(2);
emp_ids(1) := 100;
emp_titles(1) := 'Senior Manager';
emp_ids(2) := 101;
emp_titles(2) := 'Lead Developer';
-- 批量更新
FORALL i IN emp_ids.FIRST .. emp_ids.LAST
UPDATE EMPLOYEES
SET TITLE = emp_titles(i)
WHERE EMPLOYEE_ID = emp_ids(i);
END;
/
```
这里，我们首先定义了两个集合，分别存储员工ID和对应的职位。然后，使用 `FORALL` 语句将这些更新操作一次性执行。
示例 3：捕获异常
如果某些更新操作失败，我们可以使用 `SAVE EXCEPTIONS` 参数来捕获这些异常，并在后续处理它们。
```sql
DECLARE
TYPE emp_id_array IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE;
TYPE emp_title_array IS TABLE OF EMPLOYEES.TITLE%TYPE;
emp_ids emp_id_array := emp_id_array();
emp_titles emp_title_array := emp_title_array();
errors EXCEPTION;
PRAGMA EXCEPTION_INIT(errors, -24381);
BEGIN
-- 添加员工ID和职位
emp_ids.EXTEND(2);
emp_titles.EXTEND(2);
emp_ids(1) := 100;
emp_titles(1) := 'Senior Manager';
emp_ids(2) := 101;
emp_titles(2) := 'Lead Developer';
-- 批量更新并捕获异常
FORALL i IN emp_ids.FIRST .. emp_ids.LAST SAVE EXCEPTIONS
UPDATE EMPLOYEES
SET TITLE = emp_titles(i)
WHERE EMPLOYEE_ID = emp_ids(i);
-- 处理捕获的异常
FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Error occurred at row ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
END LOOP;
END;
/
```
在这个例子中，我们使用了 `SAVE EXCEPTIONS` 参数来捕获可能发生的错误，并在循环中输出具体的错误信息。
总结
`FORALL` 是 PL/SQL 中非常实用的一个特性，特别是在处理大批量数据时，能够大幅提高程序的执行效率。通过合理使用 `FORALL` 和相关选项，可以编写出高效且健壮的数据库应用程序。
