Oracle PL/SQL 集合操作详解

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

---

1. 概述

PL/SQL 集合是数组/列表数据结构[1]：

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

---

2. Associative Array（索引表）

2.1 基本

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;
  v_sal('Charlie') := 7000;
  
  DBMS_OUTPUT.PUT_LINE('Alice salary: ' || v_sal('Alice'));
END;
/

2.2 数字索引

DECLARE
  TYPE num_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_nums num_tab;
BEGIN
  FOR i IN 1..10 LOOP
    v_nums(i) := i * 10;
  END LOOP;
  
  FOR i IN 1..v_nums.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_nums(i));
  END LOOP;
END;
/

2.3 遍历

DECLARE
  TYPE name_tab IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  v_names name_tab;
  v_idx PLS_INTEGER;
BEGIN
  v_names(1) := 'Alice';
  v_names(5) := 'Bob';
  v_names(10) := 'Charlie';
  
  v_idx := v_names.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_idx || ': ' || v_names(v_idx));
    v_idx := v_names.NEXT(v_idx);
  END LOOP;
END;
/

---

3. Nested Table

3.1 基本

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

3.2 SQL 类型

CREATE TYPE num_list AS TABLE OF NUMBER;
/

DECLARE
  v_nums num_list := num_list(1, 2, 3);
BEGIN
  ...
END;
/

-- 表列
CREATE TABLE departments (
  id NUMBER,
  name VARCHAR2(50),
  employees num_list
) NESTED TABLE employees STORE AS dept_employees;

3.3 操作

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_tab(1, 2, 3);
BEGIN
  -- EXTEND
  v_nums.EXTEND(2);
  v_nums(4) := 4;
  v_nums(5) := 5;
  
  -- TRIM
  v_nums.TRIM(1);  -- 删除最后
  
  -- DELETE
  v_nums.DELETE(2);  -- 删除指定
  
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT);
END;
/

---

4. VARRAY

4.1 基本

DECLARE
  TYPE num_array IS VARRAY(10) OF NUMBER;
  v_nums num_array := num_array(1, 2, 3);
BEGIN
  FOR i IN 1..v_nums.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_nums(i));
  END LOOP;
END;
/

4.2 SQL 类型

CREATE TYPE phone_list AS VARRAY(5) OF VARCHAR2(20);
/

CREATE TABLE contacts (
  id NUMBER,
  phones phone_list
);

INSERT INTO contacts VALUES (1, phone_list('123', '456', '789'));

---

5. 集合方法

方法	说明
COUNT	元素数
FIRST	第一个索引
LAST	最后一个索引
NEXT(n)	下一个
PRIOR(n)	上一个
EXISTS(n)	存在
EXTEND	扩展（Nested/VARRAY）
EXTEND(n)	扩展 n 个
EXTEND(n, i)	复制
TRIM	删除最后
TRIM(n)	删除 n 个
DELETE	全部删除
DELETE(n)	删除指定
DELETE(m, n)	范围删除

5.1 示例

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_list();
BEGIN
  v_nums.EXTEND(5);
  v_nums(1) := 10;
  v_nums(2) := 20;
  v_nums(3) := 30;
  v_nums(4) := 40;
  v_nums(5) := 50;
  
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT);
  DBMS_OUTPUT.PUT_LINE('First: ' || v_nums(v_nums.FIRST));
  DBMS_OUTPUT.PUT_LINE('Last: ' || v_nums(v_nums.LAST));
  
  IF v_nums.EXISTS(3) THEN
    DBMS_OUTPUT.PUT_LINE('Index 3: ' || v_nums(3));
  END IF;
  
  v_nums.DELETE(2);
  DBMS_OUTPUT.PUT_LINE('After delete: ' || v_nums.COUNT);
END;
/

---

6. BULK COLLECT

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

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

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

---

7. FORALL

7.1 基本

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

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

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

---

8. 集合操作

8.1 赋值

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_a num_tab := num_tab(1, 2, 3);
  v_b num_tab;
BEGIN
  v_b := v_a;  -- 复制
END;
/

8.2 比较

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_a num_tab := num_tab(1, 2, 3);
  v_b num_tab := num_tab(1, 2, 3);
BEGIN
  IF v_a = v_b THEN  -- 仅 Nested（相同声明）
    DBMS_OUTPUT.PUT_LINE('Equal');
  END IF;
END;
/

8.3 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);
  v_c num_tab;
BEGIN
  v_c := v_a MULTISET UNION v_b;          -- 1,2,3,4,5,3,4
  v_c := v_a MULTISET UNION DISTINCT v_b; -- 1,2,3,4,5
  v_c := v_a MULTISET INTERSECT v_b;      -- 3,4
  v_c := v_a MULTISET EXCEPT v_b;         -- 1,2,5
  
  IF v_a SUBMULTISET v_b THEN ...
  IF v_a IS A SET THEN ...  -- 无重复
  IF v_a IS EMPTY THEN ...
END;
/

---

9. RECORD

9.1 基本

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

9.2 %ROWTYPE

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

9.3 集合 of RECORD

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

---

10. 表列

10.1 Nested Table

CREATE TYPE phone_list AS TABLE OF VARCHAR2(20);
/

CREATE TABLE contacts (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  phones phone_list
) NESTED TABLE phones STORE AS contacts_phones;

INSERT INTO contacts VALUES (1, 'Alice', phone_list('123', '456'));

SELECT * FROM contacts;
SELECT * FROM TABLE(SELECT phones FROM contacts WHERE id = 1);

UPDATE contacts SET phones = phone_list('789') WHERE id = 1;

10.2 VARRAY

CREATE TYPE score_list AS VARRAY(5) OF NUMBER;
/

CREATE TABLE students (
  id NUMBER,
  scores score_list
);

INSERT INTO students VALUES (1, score_list(80, 90, 85));

---

11. 应用场景

11.1 批量处理

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

11.2 缓存

DECLARE
  TYPE emp_cache IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  v_cache emp_cache;
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * BULK COLLECT INTO v_cache FROM employees;
  
  -- 查找
  IF v_cache.EXISTS(100) THEN
    v_emp := v_cache(100);
  END IF;
END;
/

11.3 多值参数

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

EXEC process_ids(num_list(1, 2, 3));

---

12. 性能

12.1 BULK

- 减少 SQL/PL/SQL 切换
- LIMIT
- 高效

12.2 内存

- 大集合占用
- LIMIT
- 释放

12.3 选择

- Associative Array：内存，索引
- Nested Table：持久，灵活
- VARRAY：固定大小

---

13. 常见坑与排错

13.1 ORA-06533

- VARRAY 满
- EXTEND

13.2 ORA-06502

- 索引超界
- EXISTS 检查

13.3 ORA-22160

- 索引不存在
- 检查

---

14. 最佳实践

1. Associative Array：内存
2. Nested Table：持久
3. VARRAY：固定
4. BULK COLLECT：批量
5. LIMIT：内存
6. EXISTS：检查
7. MULTISET：集合运算
8. %ROWTYPE：行类型
9. FORALL：DML
10. 测试：验证

---

15. 参考资料

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