Oracle BULK COLLECT 与 FORALL

适用版本：Oracle Database 8i / 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

BULK COLLECT 和 FORALL 是 PL/SQL 批量操作[1]，大幅提升性能：

核心优势：

• 减少 SQL/PL/SQL 上下文切换
• 性能提升 10-1000 倍
• 批量处理

---

2. BULK COLLECT

2.1 基本语法

-- 批量获取到集合
SELECT ... BULK COLLECT INTO collection_name
FROM ...;

FETCH cursor BULK COLLECT INTO collection_name;

2.2 示例

DECLARE
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  v_emps emp_table;
BEGIN
  -- 一次性获取所有数据
  SELECT * BULK COLLECT INTO v_emps
  FROM employees WHERE dept_id = 10;
  
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(i).last_name);
  END LOOP;
END;

2.3 多列

DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  TYPE name_table IS TABLE OF VARCHAR2(100);
  v_ids id_table;
  v_names name_table;
BEGIN
  SELECT employee_id, last_name 
  BULK COLLECT INTO v_ids, v_names
  FROM employees;
END;

2.4 LIMIT 子句

DECLARE
  CURSOR c_emp IS SELECT * FROM employees;
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  v_emps emp_table;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp BULK COLLECT INTO v_emps LIMIT 100;
    EXIT WHEN v_emps.COUNT = 0;
    
    -- 处理 100 行
    FOR i IN 1..v_emps.COUNT LOOP
      ...
    END LOOP;
  END LOOP;
  CLOSE c_emp;
END;

2.5 LIMIT 选择

数据量	LIMIT	内存
小（< 1000）	不限	低
中（1K-100K）	100-1000	中
大（> 100K）	100-500	低

---

3. FORALL

3.1 基本语法

FORALL index IN lower_bound..upper_bound
  SQL_statement;

3.2 INSERT 批量

DECLARE
  TYPE emp_rec IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER
  );
  TYPE emp_table IS TABLE OF emp_rec;
  v_emps emp_table;
BEGIN
  v_emps := emp_table(
    emp_rec(1, 'Alice', 5000),
    emp_rec(2, 'Bob', 6000),
    emp_rec(3, 'Charlie', 7000)
  );
  
  FORALL i IN 1..v_emps.COUNT
    INSERT INTO employees (employee_id, last_name, salary)
    VALUES (v_emps(i).id, v_emps(i).name, v_emps(i).salary);
END;

3.3 UPDATE 批量

DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  TYPE sal_table IS TABLE OF NUMBER;
  v_ids id_table;
  v_sals sal_table;
BEGIN
  SELECT employee_id, salary * 1.1 
  BULK COLLECT INTO v_ids, v_sals
  FROM employees WHERE dept_id = 10;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = v_sals(i) 
    WHERE employee_id = v_ids(i);
END;

3.4 DELETE 批量

DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  v_ids id_table;
BEGIN
  SELECT employee_id BULK COLLECT INTO v_ids
  FROM employees WHERE dept_id = 99;
  
  FORALL i IN 1..v_ids.COUNT
    DELETE FROM employees WHERE employee_id = v_ids(i);
END;

---

4. FORALL 选项

4.1 SAVE EXCEPTIONS

-- 跳过错误，继续执行
DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  v_ids id_table;
BEGIN
  v_ids := id_table(1, 2, 3, NULL, 5);  -- NULL 会报错
  
  FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
    UPDATE employees SET salary = 1000 WHERE employee_id = v_ids(i);
EXCEPTION
  WHEN OTHERS THEN
    FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'Error ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || 
        ': ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE
      );
    END LOOP;
END;

4.2 INDICES OF

-- 跳过 NULL 元素
DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  v_ids id_table;
BEGIN
  v_ids := id_table();
  v_ids.EXTEND(5);
  v_ids(1) := 100;
  v_ids(3) := 200;
  v_ids(5) := 300;
  -- v_ids(2), v_ids(4) 为 NULL
  
  FORALL i IN INDICES OF v_ids
    UPDATE employees SET salary = 5000 WHERE employee_id = v_ids(i);
END;

4.3 VALUES OF

-- 使用另一个集合的索引
DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  TYPE idx_table IS TABLE OF PLS_INTEGER;
  v_ids id_table := id_table(100, 200, 300, 400, 500);
  v_indices idx_table := idx_table(1, 3, 5);
BEGIN
  FORALL i IN VALUES OF v_indices
    UPDATE employees SET salary = 5000 WHERE employee_id = v_ids(i);
  -- 仅更新 100, 300, 500
END;

---

5. SQL%BULK_ROWCOUNT

5.1 查看每行影响数

DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  v_ids id_table := id_table(1, 2, 3);
BEGIN
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = 5000 WHERE employee_id = v_ids(i);
  
  FOR i IN 1..v_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      'ID ' || v_ids(i) || ': ' || SQL%BULK_ROWCOUNT(i) || ' rows'
    );
  END LOOP;
END;

---

6. 性能对比

6.1 循环 vs BULK

-- 慢：循环
FOR i IN 1..v_ids.COUNT LOOP
  UPDATE employees SET salary = 5000 WHERE employee_id = v_ids(i);
END LOOP;

-- 快：FORALL
FORALL i IN 1..v_ids.COUNT
  UPDATE employees SET salary = 5000 WHERE employee_id = v_ids(i);

6.2 性能数据

数据量	循环	FORALL	提升
100	0.5s	0.05s	10x
1000	5s	0.1s	50x
10000	50s	0.5s	100x
100000	500s	3s	167x

---

7. 完整示例

7.1 数据迁移

DECLARE
  CURSOR c_src IS SELECT * FROM old_employees;
  TYPE emp_table IS TABLE OF old_employees%ROWTYPE;
  v_emps emp_table;
  v_count NUMBER := 0;
BEGIN
  OPEN c_src;
  LOOP
    FETCH c_src BULK COLLECT INTO v_emps LIMIT 1000;
    EXIT WHEN v_emps.COUNT = 0;
    
    FORALL i IN 1..v_emps.COUNT
      INSERT INTO new_employees VALUES v_emps(i);
    
    v_count := v_count + v_emps.COUNT;
    COMMIT;
  END LOOP;
  CLOSE c_src;
  
  DBMS_OUTPUT.PUT_LINE('Migrated: ' || v_count);
END;

7.2 批量更新

CREATE OR REPLACE PROCEDURE batch_update_salary(
  p_dept_id NUMBER,
  p_increase NUMBER
) AS
  CURSOR c_emp IS 
    SELECT employee_id, salary 
    FROM employees 
    WHERE dept_id = p_dept_id;
  
  TYPE id_table IS TABLE OF NUMBER;
  TYPE sal_table IS TABLE OF NUMBER;
  v_ids id_table;
  v_sals sal_table;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp BULK COLLECT INTO v_ids, v_sals LIMIT 500;
    EXIT WHEN v_ids.COUNT = 0;
    
    FORALL i IN 1..v_ids.COUNT
      UPDATE employees 
      SET salary = v_sals(i) + p_increase 
      WHERE employee_id = v_ids(i);
    
    COMMIT;
  END LOOP;
  CLOSE c_emp;
END;
/

---

8. 常见坑与排错

8.1 内存不足

修复：

-- 使用 LIMIT 限制
FETCH c_emp BULK COLLECT INTO v_emps LIMIT 1000;

8.2 FORALL 中间失败

修复：

-- 使用 SAVE EXCEPTIONS
FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
  INSERT INTO ...;

8.3 类型不匹配

修复：

-- 确保集合类型与表一致
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_emps emp_table;

8.4 FORALL 不支持 SELECT

-- FORALL 仅支持 DML
-- SELECT 用 BULK COLLECT

---

9. 最佳实践

1. 批量操作用 BULK COLLECT + FORALL：性能
2. LIMIT 100-1000：平衡内存
3. SAVE EXCEPTIONS：跳过错误
4. 定期 COMMIT：避免长事务
5. 检查 COUNT：避免空集合
6. 使用 %ROWTYPE：类型匹配
7. 避免游标循环 DML：用 FORALL
8. 监控内存：v$process memory
9. 测试性能：对比验证
10. 结合 MERGE：批量 UPSERT

---

10. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Bulk SQL” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/bulk-sql.html