Oracle PL/SQL 集合方法详解

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

---

1. 概述

PL/SQL 集合方法操作集合[1]：

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

---

2. COUNT

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_tab(10, 20, 30, 40, 50);
  TYPE str_tab IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  v_strs str_tab;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT);  -- 5
  
  v_strs(1) := 'A';
  v_strs(5) := 'B';
  v_strs(10) := 'C';
  DBMS_OUTPUT.PUT_LINE('Sparse count: ' || v_strs.COUNT);  -- 3
END;
/

---

3. FIRST / LAST

DECLARE
  TYPE str_tab IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  v_strs str_tab;
BEGIN
  v_strs(1) := 'A';
  v_strs(5) := 'B';
  v_strs(10) := 'C';
  
  DBMS_OUTPUT.PUT_LINE('First: ' || v_strs.FIRST);  -- 1
  DBMS_OUTPUT.PUT_LINE('Last: ' || v_strs.LAST);    -- 10
  DBMS_OUTPUT.PUT_LINE('Value at first: ' || v_strs(v_strs.FIRST));  -- A
END;
/

---

4. NEXT / PRIOR

DECLARE
  TYPE str_tab IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  v_strs str_tab;
  v_idx PLS_INTEGER;
BEGIN
  v_strs(1) := 'A';
  v_strs(5) := 'B';
  v_strs(10) := 'C';
  
  v_idx := v_strs.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_idx || ': ' || v_strs(v_idx));
    v_idx := v_strs.NEXT(v_idx);
  END LOOP;
  
  -- 反向
  v_idx := v_strs.LAST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_idx || ': ' || v_strs(v_idx));
    v_idx := v_strs.PRIOR(v_idx);
  END LOOP;
END;
/

---

5. EXISTS

DECLARE
  TYPE num_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_nums num_tab;
BEGIN
  v_nums(1) := 10;
  v_nums(5) := 50;
  
  IF v_nums.EXISTS(1) THEN
    DBMS_OUTPUT.PUT_LINE('Index 1 exists: ' || v_nums(1));
  END IF;
  
  IF NOT v_nums.EXISTS(3) THEN
    DBMS_OUTPUT.PUT_LINE('Index 3 does not exist');
  END IF;
END;
/

---

6. EXTEND

6.1 Nested Table / VARRAY

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_tab();
BEGIN
  -- EXTEND 1
  v_nums.EXTEND;
  v_nums(1) := 10;
  
  -- EXTEND n
  v_nums.EXTEND(3);
  v_nums(2) := 20;
  v_nums(3) := 30;
  v_nums(4) := 40;
  
  -- EXTEND(n, i) 复制
  v_nums.EXTEND(2, 1);  -- 复制 v_nums(1) 2 次
  
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT);  -- 6
END;
/

6.2 限制

- Associative Array 不支持
- VARRAY 不能超 MAX

---

7. TRIM

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_tab(1, 2, 3, 4, 5);
BEGIN
  -- TRIM 1
  v_nums.TRIM;
  DBMS_OUTPUT.PUT_LINE('After trim 1: ' || v_nums.COUNT);  -- 4
  
  -- TRIM n
  v_nums.TRIM(2);
  DBMS_OUTPUT.PUT_LINE('After trim 2: ' || v_nums.COUNT);  -- 2
END;
/

---

8. DELETE

8.1 全部

v_nums.DELETE;

8.2 指定

v_nums.DELETE(3);

8.3 范围

v_nums.DELETE(3, 7);  -- 删除 3-7

8.4 示例

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;
  
  v_nums.DELETE(3);          -- 删除 3
  v_nums.DELETE(6, 8);       -- 删除 6-8
  
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT);  -- 6
END;
/

---

9. LIMIT

DECLARE
  TYPE num_array IS VARRAY(10) OF NUMBER;
  v_nums num_array := num_array(1, 2, 3);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT);  -- 3
  DBMS_OUTPUT.PUT_LINE('Limit: ' || v_nums.LIMIT);  -- 10
  
  IF v_nums.COUNT < v_nums.LIMIT THEN
    v_nums.EXTEND;
    v_nums(4) := 4;
  END IF;
END;
/

---

10. 遍历

10.1 顺序

v_idx := v_nums.FIRST;
WHILE v_idx IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE(v_nums(v_idx));
  v_idx := v_nums.NEXT(v_idx);
END LOOP;

10.2 反向

v_idx := v_nums.LAST;
WHILE v_idx IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE(v_nums(v_idx));
  v_idx := v_nums.PRIOR(v_idx);
END LOOP;

10.3 FOR（密集）

FOR i IN 1..v_nums.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE(v_nums(i));
END LOOP;

---

11. 字符串索引

DECLARE
  TYPE name_salary IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
  v_sal name_salary;
  v_idx VARCHAR2(50);
BEGIN
  v_sal('Alice') := 5000;
  v_sal('Bob') := 6000;
  v_sal('Charlie') := 7000;
  
  v_idx := v_sal.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_idx || ': ' || v_sal(v_idx));
    v_idx := v_sal.NEXT(v_idx);
  END LOOP;
END;
/

---

12. MULTISET

12.1 操作

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;          -- 1,2,3,4,5,3,4,5,6,7
  v_c := v_a MULTISET UNION DISTINCT v_b; -- 1,2,3,4,5,6,7
  v_c := v_a MULTISET INTERSECT v_b;      -- 3,4,5
  v_c := v_a MULTISET EXCEPT v_b;         -- 1,2
END;
/

12.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 ...

---

13. 应用场景

13.1 缓存

TYPE emp_cache IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
v_cache emp_cache;

IF v_cache.EXISTS(p_id) THEN
  RETURN v_cache(p_id);
END IF;

13.2 批量

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
    ...
  END LOOP;
END;
/

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

13.3 动态

v_list.EXTEND;
v_list(v_list.LAST) := new_value;

---

14. 性能

14.1 EXISTS

- O(1) 查找
- 安全访问

14.2 EXTEND

- 批量 EXTEND
- 减少调用

14.3 LIMIT

- VARRAY 边界
- 安全

---

15. 常见坑与排错

15.1 ORA-06533

- VARRAY 满
- EXTEND 失败
- 检查 LIMIT

15.2 ORA-22160

- 索引不存在
- EXISTS 检查

15.3 ORA-06502

- NULL 索引
- 检查

---

16. 最佳实践

1. EXISTS：检查
2. EXTEND 批量：性能
3. LIMIT：VARRAY
4. NEXT/PRIOR：稀疏
5. FIRST/LAST：边界
6. TRIM/DELETE：释放
7. MULTISET：集合运算
8. 遍历：顺序/反向
9. 字符串索引：查找
10. 测试：验证

---

17. 参考资料

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