DB / Oracle / SQL 开发

Oracle PL/SQL 集合与批量操作最佳实践

2026.09.06

Oracle PL/SQL 集合与批量操作最佳实践

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

1. 概述

PL/SQL 集合与批量操作最佳实践[1]：

详细见：Oracle PL/SQL 集合详解、Oracle BULK COLLECT 与 FORALL 详解。

2. 选择集合类型

2.1 决策树

内存临时（频繁查找）→ Associative Array
持久存储 → Nested Table
固定大小 → VARRAY

2.2 性能对比

类型内存持久查找
Associative Array最佳否O(1)
Nested Table中是顺序
VARRAY固定是顺序

3. BULK COLLECT 最佳实践

3.1 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
      -- 处理
    END LOOP;
  END LOOP;
  CLOSE c;
END;
/

3.2 LIMIT 选择

- 测试不同值
- 一般 1000-10000
- 平衡内存与性能

3.3 多列

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;
/

详细见：Oracle BULK COLLECT 与 FORALL 详解。

4. FORALL 最佳实践

4.1 SAVE EXCEPTIONS

FORALL i IN 1..v_data.COUNT SAVE EXCEPTIONS
  INSERT INTO target VALUES v_data(i);

EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      log_error(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX, 
                SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
    END LOOP;

4.2 INDICES OF

-- 稀疏集合
FORALL i IN INDICES OF v_sparse
  INSERT INTO t VALUES v_sparse(i);

4.3 VALUES OF

-- 索引集合
FORALL i IN VALUES OF v_indices
  INSERT INTO t VALUES v_data(i);

5. 内存管理

5.1 LIMIT

FETCH c BULK COLLECT INTO v_emp LIMIT 1000;

5.2 释放

-- 处理完释放
v_emp.TRIM(v_emp.COUNT);
-- 或
v_emp.DELETE;

5.3 监控

SELECT name, value FROM v$pgastat WHERE name LIKE '%PGA%';

6. 缓存

6.1 包级缓存

CREATE OR REPLACE PACKAGE cache_pkg AS
  TYPE emp_cache IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  v_cache emp_cache;
  
  FUNCTION get_emp(p_id NUMBER) RETURN employees%ROWTYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY cache_pkg AS
  FUNCTION get_emp(p_id NUMBER) RETURN employees%ROWTYPE IS
  BEGIN
    IF NOT v_cache.EXISTS(p_id) THEN
      SELECT * INTO v_cache(p_id) FROM employees WHERE id = p_id;
    END IF;
    RETURN v_cache(p_id);
  END;
END;
/

6.2 RESULT_CACHE

CREATE OR REPLACE FUNCTION get_dept_name(p_id NUMBER) RETURN VARCHAR2
  RESULT_CACHE RELIES_ON (departments)
IS
  v_name VARCHAR2(100);
BEGIN
  SELECT name INTO v_name FROM departments WHERE id = p_id;
  RETURN v_name;
END;
/

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

7. 批量操作

7.1 INSERT

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

7.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 * 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;
/

7.3 DELETE

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;
/

7.4 MERGE

FORALL i IN 1..v_ids.COUNT
  MERGE INTO target t
  USING (SELECT v_ids(i) AS id FROM dual) s
  ON (t.id = s.id)
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED THEN INSERT ...;

详细见：Oracle MERGE 语句详解。

8. SQL vs PL/SQL

8.1 SQL 优先

-- 差（PL/SQL 循环）
FOR rec IN (SELECT * FROM source) LOOP
  INSERT INTO target VALUES rec;
END LOOP;

-- 好（SQL）
INSERT INTO target SELECT * FROM source;

8.2 BULK

-- 中等（BULK）
DECLARE
  TYPE src_tab IS TABLE OF source%ROWTYPE;
  v_src src_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_src FROM source LIMIT 10000;
  FORALL i IN 1..v_src.COUNT
    INSERT INTO target VALUES v_src(i);
END;
/

8.3 选择

- 简单：SQL
- 复杂处理：BULK
- 测试比较

9. 集合操作

9.1 MULTISET

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_a num_tab := num_tab(1, 2, 3, 4, 5);
  v_b num_tab := num_tab(3, 4, 5, 6, 7);
  v_c num_tab;
BEGIN
  v_c := v_a MULTISET UNION v_b;
  v_c := v_a MULTISET UNION DISTINCT v_b;
  v_c := v_a MULTISET INTERSECT v_b;
  v_c := v_a MULTISET EXCEPT v_b;
END;
/

9.2 比较

IF v_a SUBMULTISET v_b THEN ...
IF v_a IS A SET THEN ...
IF v_a IS EMPTY THEN ...
IF v_a = v_b THEN ...

详细见：Oracle PL/SQL 集合操作详解。

10. 表达式

10.1 TABLE

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_tab(1, 2, 3);
BEGIN
  -- SQL 中使用
  SELECT e.name
  FROM employees e, TABLE(v_nums) n
  WHERE e.id = n.COLUMN_VALUE;
END;
/

10.2 参数

CREATE OR REPLACE PROCEDURE process_ids(p_ids SYS.ODCINUMBERLIST) IS
BEGIN
  FORALL i IN 1..p_ids.COUNT
    UPDATE t SET ... WHERE id = p_ids(i);
END;
/

EXEC process_ids(SYS.ODCINUMBERLIST(1, 2, 3));

11. 性能对比

11.1 循环 DML

- 每行 SQL 上下文切换
- 慢

11.2 FORALL

- 批量
- 10-100 倍

11.3 SQL

- 单语句
- 最快

12. 监控

12.1 时间

v_start := DBMS_UTILITY.GET_TIME;
-- 操作
v_end := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('Time: ' || (v_end - v_start) || ' hsec');

12.2 PGA

SELECT name, value FROM v$pgastat;

12.3 Profiler

EXEC DBMS_PROFILER.START_PROFILER('test');
-- 操作
EXEC DBMS_PROFILER.STOP_PROFILER;

详细见：Oracle PL/SQL 性能监控详解。

13. 常见坑与排错

13.1 内存

- 大集合 OOM
- LIMIT
- 释放

13.2 性能

- 循环 DML
- FORALL
- SQL

13.3 索引

- 越界
- EXISTS 检查

14. 最佳实践

  1. ** Associative Array**：内存查找
  2. BULK COLLECT：批量查询
  3. LIMIT：内存
  4. FORALL：批量 DML
  5. SAVE EXCEPTIONS：容错
  6. SQL 优先：简单
  7. 缓存：频繁
  8. RESULT_CACHE：函数
  9. 监控：性能
  10. 测试：验证

15. 参考资料

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