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 集合（Collections）是复合数据类型[1]：

类型：

  • 联合数组（Associative Array）
  • 嵌套表（Nested Table）
  • VARRAY

详细见：Oracle PL/SQL 基础与块结构。

2. 联合数组（Index-by Table）

2.1 声明

DECLARE
  TYPE emp_table IS TABLE OF employees.name%TYPE 
    INDEX BY PLS_INTEGER;
  
  v_emp emp_table;
BEGIN
  v_emp(1) := 'Alice';
  v_emp(2) := 'Bob';
  
  DBMS_OUTPUT.PUT_LINE(v_emp(1));
END;
/

2.2 字符串索引

DECLARE
  TYPE name_salary IS TABLE OF NUMBER 
    INDEX BY VARCHAR2(50);
  
  v_sal name_salary;
BEGIN
  v_sal('Alice') := 5000;
  v_sal('Bob') := 6000;
  
  DBMS_OUTPUT.PUT_LINE(v_sal('Alice'));
END;
/

2.3 方法

COUNT       -- 元素数
FIRST       -- 第一个索引
LAST        -- 最后索引
NEXT(n)     -- 下一个
PRIOR(n)    -- 前一个
EXISTS(n)   -- 是否存在
DELETE      -- 删除
DELETE(n)   -- 删除指定
DELETE(a,b) -- 范围删除

3. 嵌套表

3.1 声明

DECLARE
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  v_emp emp_table;
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;
/

3.2 初始化

DECLARE
  TYPE num_table IS TABLE OF NUMBER;
  v_num num_table := num_table(1, 2, 3, 4, 5);
BEGIN
  FOR i IN 1..v_num.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_num(i));
  END LOOP;
END;
/

3.3 方法

COUNT
FIRST / LAST
NEXT / PRIOR
EXISTS(n)
EXTEND       -- 添加 1 个 null
EXTEND(n)    -- 添加 n 个
EXTEND(n, i) -- 复制 i 元素 n 个
TRIM         -- 删除末尾 1 个
TRIM(n)      -- 删除末尾 n 个
DELETE
DELETE(n)
DELETE(a, b)

3.4 数据库存储

CREATE TABLE depts (
  id NUMBER,
  names VARCHAR2_TABLE  -- 需先创建类型
);

CREATE OR REPLACE TYPE varchar2_table AS TABLE OF VARCHAR2(100);
/

CREATE TABLE depts (
  id NUMBER,
  names varchar2_table
) NESTED TABLE names STORE AS names_nt;

INSERT INTO depts VALUES (1, varchar2_table('Alice', 'Bob', 'Charlie'));

4. VARRAY

4.1 声明

DECLARE
  TYPE names_array IS VARRAY(10) OF VARCHAR2(50);
  v_names names_array := names_array('Alice', 'Bob');
BEGIN
  v_names.EXTEND;
  v_names(3) := 'Charlie';
  
  FOR i IN 1..v_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_names(i));
  END LOOP;
END;
/

4.2 方法

COUNT
LIMIT       -- 最大容量
FIRST / LAST
NEXT / PRIOR
EXISTS(n)
EXTEND / TRIM
DELETE（受限）

4.3 数据库存储

CREATE OR REPLACE TYPE phone_array AS VARRAY(5) OF VARCHAR2(20);
/

CREATE TABLE contacts (
  id NUMBER,
  phones phone_array
);

INSERT INTO contacts VALUES (1, phone_array('010-123', '139-000'));

5. BULK COLLECT

5.1 基本

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

5.2 LIMIT

DECLARE
  CURSOR c_emp IS SELECT * FROM employees;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp BULK COLLECT INTO v_emp LIMIT 100;
    EXIT WHEN v_emp.COUNT = 0;
    
    FORALL i IN 1..v_emp.COUNT
      INSERT INTO emp_copy VALUES v_emp(i);
  END LOOP;
  CLOSE c_emp;
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

6. FORALL

6.1 基本

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

6.2 SAVE EXCEPTIONS

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

6.3 INDICES OF

DECLARE
  TYPE num_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_num num_tab;
BEGIN
  v_num(1) := 10;
  v_num(3) := 30;
  v_num(5) := 50;
  -- v_num(2), v_num(4) 不存在
  
  FORALL i IN INDICES OF v_num
    INSERT INTO t VALUES (v_num(i));
END;
/

6.4 VALUES OF

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  TYPE idx_tab IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  v_num num_tab := num_tab(100, 200, 300, 400, 500);
  v_idx idx_tab;
BEGIN
  v_idx(1) := 1;
  v_idx(2) := 3;
  v_idx(3) := 5;
  
  FORALL i IN VALUES OF v_idx
    INSERT INTO t VALUES (v_num(i));
  -- 插入 100, 300, 500
END;
/

7. RECORD

7.1 表类型

DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM employees WHERE id = 100;
  DBMS_OUTPUT.PUT_LINE(v_emp.name);
END;
/

7.2 自定义

DECLARE
  TYPE emp_rec IS RECORD (
    id employees.id%TYPE,
    name employees.name%TYPE,
    salary NUMBER
  );
  v_emp emp_rec;
BEGIN
  v_emp.id := 100;
  v_emp.name := 'Alice';
  v_emp.salary := 5000;
END;
/

7.3 集合

DECLARE
  TYPE emp_rec IS RECORD (id NUMBER, name VARCHAR2(50));
  TYPE emp_tab IS TABLE OF emp_rec;
  v_emp emp_tab;
BEGIN
  SELECT id, name BULK COLLECT INTO v_emp FROM employees;
END;
/

8. 多维集合

DECLARE
  TYPE inner_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE outer_tab IS TABLE OF inner_tab INDEX BY PLS_INTEGER;
  v_matrix outer_tab;
BEGIN
  v_matrix(1)(1) := 10;
  v_matrix(1)(2) := 20;
  v_matrix(2)(1) := 30;
  v_matrix(2)(2) := 40;
  
  DBMS_OUTPUT.PUT_LINE(v_matrix(1)(1));  -- 10
  DBMS_OUTPUT.PUT_LINE(v_matrix(2)(2));  -- 40
END;
/

9. 集合操作

9.1 赋值

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v1 num_tab := num_tab(1, 2, 3);
  v2 num_tab;
BEGIN
  v2 := v1;  -- 拷贝
END;
/

9.2 比较

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v1 num_tab := num_tab(1, 2, 3);
  v2 num_tab := num_tab(1, 2, 3);
BEGIN
  IF v1 = v2 THEN  -- 元素相同
    DBMS_OUTPUT.PUT_LINE('Equal');
  END IF;
END;
/

9.3 MULTISET

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v1 num_tab := num_tab(1, 2, 3, 3);
  v2 num_tab := num_tab(2, 3, 4);
  v3 num_tab;
BEGIN
  v3 := v1 MULTISET UNION v2;          -- {1,2,3,3,2,3,4}
  v3 := v1 MULTISET UNION DISTINCT v2; -- {1,2,3,4}
  v3 := v1 MULTISET INTERSECT v2;      -- {2,3}
  v3 := v1 MULTISET EXCEPT v2;         -- {1,3}
END;
/

10. 表函数

10.1 PIPELINED

CREATE OR REPLACE FUNCTION get_employees 
  RETURN emp_tab PIPELINED
IS
  v_emp emp_rec;
  CURSOR c IS SELECT * FROM employees;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO v_emp;
    EXIT WHEN c%NOTFOUND;
    PIPE ROW(v_emp);
  END LOOP;
  CLOSE c;
  RETURN;
END;
/

SELECT * FROM TABLE(get_employees);

11. 性能

11.1 BULK 优势

- 减少 SQL 调用
- 批量操作
- 性能提升 10-100 倍

11.2 LIMIT

- 大数据分批
- 内存控制
- 推荐 100-1000

11.3 SAVE EXCEPTIONS

- 不因单错误停止
- 全部处理
- 异常收集

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

12. 常见坑与排错

12.1 ORA-06533

- VARRAY 超限
- LIMIT 检查

12.2 ORA-06502

- 索引越界
- 检查 COUNT

12.3 ORA-6531

- 未初始化
- 初始化构造函数

13. 最佳实践

  1. 联合数组：临时
  2. 嵌套表：数据库
  3. VARRAY：固定大小
  4. BULK COLLECT：性能
  5. FORALL：批量 DML
  6. LIMIT：内存
  7. SAVE EXCEPTIONS：容错
  8. PIPELINED：表函数
  9. RECORD：单行
  10. 测试：验证

14. 参考资料

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