FORALL 与 FOR 批量更新

FORALL与FOR是两种常用的批量更新数据的方法。FORALL是PL/SQL中的一个关键字，用于在循环中批量执行DML语句，可以大大提高数据更新的效率。而FOR是在SQL中使用的语句，用于批量更新数据。

sql
DECLARE
  TYPE emp_tab_type IS TABLE OF employees%ROWTYPE;
  l_emp_tab emp_tab_type;
BEGIN
  SELECT * BULK COLLECT INTO l_emp_tab FROM employees WHERE department_id = 10;
  FORALL i IN 1..l_emp_tab.COUNT
    UPDATE employees SET salary = l_emp_tab(i).salary * 1.1 WHERE employee_id = l_emp_tab(i).employee_id;
  COMMIT;
END;

sql
-- 创建一个测试表
CREATE TABLE employees (
  employee_id NUMBER,
  department_id NUMBER,
  salary NUMBER
);
-- 插入测试数据
INSERT INTO employees VALUES (1, 10, 1000);
INSERT INTO employees VALUES (2, 10, 2000);
INSERT INTO employees VALUES (3, 20, 1500);
INSERT INTO employees VALUES (4, 20, 2500);
COMMIT;
-- 使用FORALL进行批量更新
DECLARE
  TYPE emp_tab_type IS TABLE OF employees%ROWTYPE;
  l_emp_tab emp_tab_type;
BEGIN
  SELECT * BULK COLLECT INTO l_emp_tab FROM employees WHERE department_id = 10;
  FORALL i IN 1..l_emp_tab.COUNT
    UPDATE employees SET salary = l_emp_tab(i).salary * 1.1 WHERE employee_id = l_emp_tab(i).employee_id;
  COMMIT;
END;