Oracle BULK COLLECT 与 FORALL 详解

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

---

1. 概述

BULK COLLECT 和 FORALL 减少 SQL/PL/SQL 上下文切换[1]：

详细见：Oracle BULK COLLECT 与 FORALL。

---

2. BULK COLLECT

2.1 SELECT

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emp FROM employees;
  
  FOR i IN 1..v_emp.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp(i).name);
  END LOOP;
END;
/

2.2 多列

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  TYPE name_tab IS TABLE OF VARCHAR2(100);
  v_ids id_tab;
  v_names name_tab;
BEGIN
  SELECT id, name BULK COLLECT INTO v_ids, v_names FROM employees;
END;
/

2.3 FETCH

DECLARE
  CURSOR c IS SELECT * FROM employees;
  TYPE emp_tab IS TABLE OF c%ROWTYPE;
  v_emp emp_tab;
BEGIN
  OPEN c;
  FETCH c BULK COLLECT INTO v_emp;
  CLOSE c;
END;
/

2.4 LIMIT

DECLARE
  CURSOR c IS SELECT * FROM employees;
  TYPE emp_tab IS TABLE OF c%ROWTYPE;
  v_emp emp_tab;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_emp LIMIT 1000;
    EXIT WHEN v_emp.COUNT = 0;
    
    FOR i IN 1..v_emp.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_emp(i).name);
    END LOOP;
  END LOOP;
  CLOSE c;
END;
/

2.5 EXECUTE IMMEDIATE

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE dept_id = :d'
    BULK COLLECT INTO v_emp USING 10;
END;
/

详细见：Oracle PL/SQL 动态 SQL 详解。

---

3. FORALL

3.1 INSERT

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  TYPE name_tab IS TABLE OF VARCHAR2(100);
  v_ids id_tab := id_tab(1, 2, 3);
  v_names name_tab := name_tab('A', 'B', 'C');
BEGIN
  FORALL i IN 1..v_ids.COUNT
    INSERT INTO t (id, name) VALUES (v_ids(i), v_names(i));
END;
/

3.2 UPDATE

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  TYPE sal_tab IS TABLE OF NUMBER;
  v_ids id_tab;
  v_sals sal_tab;
BEGIN
  SELECT id, salary BULK COLLECT INTO v_ids, v_sals FROM employees;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = v_sals(i) * 1.1 WHERE id = v_ids(i);
END;
/

3.3 DELETE

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab := id_tab(1, 2, 3);
BEGIN
  FORALL i IN 1..v_ids.COUNT
    DELETE FROM employees WHERE id = v_ids(i);
END;
/

3.4 范围

-- 范围
FORALL i IN INDICES OF v_collection
  INSERT INTO t VALUES (v_collection(i));

-- 值
FORALL i IN VALUES OF v_indices
  INSERT INTO t VALUES (v_collection(i));

---

4. SAVE EXCEPTIONS

4.1 基本

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab := id_tab(1, 2, -3, 4, -5);
BEGIN
  FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
    INSERT INTO t VALUES (v_ids(i));
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -24381 THEN
      FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
          'Index ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
          ' Code ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
        );
      END LOOP;
    END IF;
END;
/

4.2 SQL%BULK_EXCEPTIONS

属性	说明
COUNT	异常数
(i).ERROR_INDEX	失败索引
(i).ERROR_CODE	错误码

---

5. INDICES OF / VALUES OF

5.1 INDICES OF

-- 稀疏集合
DECLARE
  TYPE id_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_ids id_tab;
BEGIN
  v_ids(1) := 10;
  v_ids(5) := 20;
  v_ids(10) := 30;
  
  FORALL i IN INDICES OF v_ids
    INSERT INTO t VALUES (v_ids(i));
END;
/

5.2 VALUES OF

-- 索引集合
DECLARE
  TYPE id_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE idx_tab IS TABLE OF PLS_INTEGER;
  v_ids id_tab;
  v_indices idx_tab;
BEGIN
  v_ids(1) := 10;
  v_ids(2) := 20;
  v_ids(3) := 30;
  
  -- 选择性
  v_indices := idx_tab(1, 3);
  
  FORALL i IN VALUES OF v_indices
    INSERT INTO t VALUES (v_ids(i));
END;
/

---

6. 性能对比

6.1 循环 DML

-- 慢（每行上下文切换）
FOR i IN 1..v_ids.COUNT LOOP
  INSERT INTO t VALUES (v_ids(i));
END LOOP;

6.2 FORALL

-- 快（批量）
FORALL i IN 1..v_ids.COUNT
  INSERT INTO t VALUES (v_ids(i));

6.3 性能提升

- 100 行：5-10 倍
- 1000 行：10-50 倍
- 10000 行：50-100 倍

详细见：Oracle PL/SQL 性能优化详解。

---

7. RETURNING

7.1 FORALL + RETURNING

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  TYPE sal_tab IS TABLE OF NUMBER;
  v_ids id_tab := id_tab(1, 2, 3);
  v_sals sal_tab;
BEGIN
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = salary * 1.1 
    WHERE id = v_ids(i)
    RETURNING salary BULK COLLECT INTO v_sals;
END;
/

7.2 DML + RETURNING

DELETE FROM employees WHERE dept_id = 10
  RETURNING id BULK COLLECT INTO v_ids;

---

8. 内存管理

8.1 LIMIT

- 大数据集分批
- 避免内存耗尽
- 推荐 1000-10000

8.2 TRIM

-- 释放
v_emp.TRIM(v_emp.COUNT);

8.3 DELETE

v_emp.DELETE;

---

9. 应用场景

9.1 批量加载

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emp FROM ext_employees;
  
  FORALL i IN 1..v_emp.COUNT
    INSERT INTO employees VALUES v_emp(i);
END;
/

9.2 批量更新

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  TYPE sal_tab IS TABLE OF NUMBER;
  v_ids id_tab;
  v_sals sal_tab;
BEGIN
  SELECT id, salary * 1.1 BULK COLLECT INTO v_ids, v_sals FROM employees;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = v_sals(i) WHERE id = v_ids(i);
END;
/

9.3 批量删除

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees WHERE status = 'INACTIVE';
  
  FORALL i IN 1..v_ids.COUNT
    DELETE FROM employees WHERE id = v_ids(i);
END;
/

9.4 ETL

-- Extract → Transform → Load
DECLARE
  TYPE src_tab IS TABLE OF src%ROWTYPE;
  v_src src_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_src FROM src WHERE ...;
  
  -- Transform
  FOR i IN 1..v_src.COUNT LOOP
    v_src(i).name := UPPER(v_src(i).name);
  END LOOP;
  
  -- Load
  FORALL i IN 1..v_src.COUNT
    INSERT INTO dst VALUES v_src(i);
END;
/

---

10. 限制

10.1 FORALL

- 单条语句
- 同一集合
- 不能是函数调用

10.2 BULK COLLECT

- 内存
- LIMIT
- 处理

---

11. 常见坑与排错

11.1 ORA-22160

- 集合索引超界
- 检查 COUNT

11.2 ORA-06533

- 子脚本超出
- VARRAY 扩展

11.3 内存溢出

- 大数据 BULK
- LIMIT
- 分批

---

12. 最佳实践

1. 批量操作：性能
2. LIMIT：内存
3. SAVE EXCEPTIONS：容错
4. INDICES/VALUES OF：稀疏
5. RETURNING BULK：返回
6. SQL%ROWCOUNT：影响
7. TRIM/DELETE：释放
8. EXCEPTION：完整
9. 测试：验证
10. 监控：内存

---

13. 参考资料

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