Oracle PL/SQL 集合与记录

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

---

1. 概述

PL/SQL 集合与记录[1]：

集合：

• Associative Array
• Nested Table
• VARRAY

记录：

• %ROWTYPE
• 自定义

详细见：Oracle PL/SQL 集合与记录。

---

2. Associative Array

2.1 声明

DECLARE
  TYPE emp_id_array IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  v_emps emp_id_array;
BEGIN
  v_emps(1) := 'Alice';
  v_emps(2) := 'Bob';
  v_emps(3) := 'Charlie';
  
  DBMS_OUTPUT.PUT_LINE(v_emps(1));
END;
/

2.2 字符串索引

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

2.3 遍历

DECLARE
  v_idx VARCHAR2(50);
BEGIN
  v_idx := v_salaries.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_idx || ': ' || v_salaries(v_idx));
    v_idx := v_salaries.NEXT(v_idx);
  END LOOP;
END;
/

---

3. Nested Table

3.1 声明

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

3.2 数据库存储

-- 创建类型
CREATE TYPE emails_type AS TABLE OF VARCHAR2(100);

-- 表
CREATE TABLE customers (
  id NUMBER PRIMARY KEY,
  emails emails_type
) NESTED TABLE emails STORE AS emails_tab;

-- 插入
INSERT INTO customers VALUES (1, emails_type('[email protected]', '[email protected]'));

-- 查询
SELECT * FROM customers c, TABLE(c.emails);

---

4. VARRAY

4.1 声明

DECLARE
  TYPE phone_array IS VARRAY(10) OF VARCHAR2(20);
  v_phones phone_array := phone_array('111', '222', '333');
BEGIN
  v_phones.EXTEND;
  v_phones(4) := '444';
  
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_phones.COUNT);
  DBMS_OUTPUT.PUT_LINE('Limit: ' || v_phones.LIMIT);
END;
/

4.2 数据库存储

CREATE TYPE phones_type AS VARRAY(10) OF VARCHAR2(20);

CREATE TABLE contacts (
  id NUMBER PRIMARY KEY,
  phones phones_type
);

INSERT INTO contacts VALUES (1, phones_type('111', '222'));

---

5. 集合方法

5.1 常用

COUNT    -- 元素数
FIRST    -- 第一个索引
LAST     -- 最后一个索引
NEXT(i)  -- 下一个
PRIOR(i) -- 前一个
EXISTS(i)-- 是否存在
LIMIT    -- 最大数（VARRAY）

5.2 修改

EXTEND       -- 添加 1 个 NULL
EXTEND(n)    -- 添加 n 个 NULL
EXTEND(n, i) -- 复制第 i 个 n 次
TRIM         -- 删除末尾 1 个
TRIM(n)      -- 删除末尾 n 个
DELETE       -- 删除全部
DELETE(i)    -- 删除第 i 个
DELETE(i, j) -- 删除 i-j 范围

---

6. 集合对比

特性	Associative Array	Nested Table	VARRAY
索引	数字/字符串	数字	数字
大小	无限	动态	固定上限
数据库存储	否	是	是
稀疏	是	是（DELETE 后）	否
顺序	无	无	有

---

7. 记录

7.1 %ROWTYPE

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 %TYPE

DECLARE
  v_name employees.name%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  SELECT name, salary INTO v_name, v_salary FROM employees WHERE id = 100;
END;
/

7.3 自定义记录

DECLARE
  TYPE emp_rec IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER,
    dept_name VARCHAR2(50)
  );
  v_emp emp_rec;
BEGIN
  SELECT e.id, e.name, e.salary, d.dept_name
  INTO v_emp
  FROM employees e, departments d
  WHERE e.dept_id = d.id AND e.id = 100;
  
  DBMS_OUTPUT.PUT_LINE(v_emp.name || ' - ' || v_emp.dept_name);
END;
/

---

8. 集合 + 记录

DECLARE
  TYPE emp_rec IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER
  );
  TYPE emp_array IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
  v_emps emp_array;
  v_idx NUMBER;
BEGIN
  -- BULK COLLECT
  SELECT id, name, salary BULK COLLECT INTO v_emps
  FROM employees WHERE dept_id = 10;
  
  -- 遍历
  v_idx := v_emps.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(v_idx).name);
    v_idx := v_emps.NEXT(v_idx);
  END LOOP;
END;
/

---

9. BULK COLLECT

9.1 基本

DECLARE
  TYPE id_array IS TABLE OF NUMBER;
  v_ids id_array;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_ids.COUNT);
END;
/

9.2 LIMIT

DECLARE
  CURSOR c IS SELECT id FROM employees;
  TYPE id_array IS TABLE OF NUMBER;
  v_ids id_array;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_ids LIMIT 1000;
    EXIT WHEN v_ids.COUNT = 0;
    
    DBMS_OUTPUT.PUT_LINE('Batch: ' || v_ids.COUNT);
  END LOOP;
  CLOSE c;
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

---

10. FORALL

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

---

11. TABLE 函数

11.1 集合转表

CREATE OR REPLACE TYPE num_array AS TABLE OF NUMBER;
/

DECLARE
  v_nums num_array := num_array(1, 2, 3, 4, 5);
BEGIN
  -- TABLE 函数
  FOR rec IN (SELECT column_value FROM TABLE(v_nums)) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.column_value);
  END LOOP;
END;
/

11.2 多行返回

CREATE OR REPLACE FUNCTION get_emp_ids(p_dept_id NUMBER) 
RETURN num_array IS
  v_ids num_array;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees WHERE dept_id = p_dept_id;
  RETURN v_ids;
END;
/

-- SQL 中使用
SELECT * FROM TABLE(get_emp_ids(10));

---

12. MULTISET 操作

12.1 Nested Table

DECLARE
  v_a num_array := num_array(1, 2, 3);
  v_b num_array := num_array(3, 4, 5);
  v_c num_array;
BEGIN
  -- 并集
  v_c := v_a MULTISET UNION v_b;  -- 1,2,3,3,4,5
  v_c := v_a MULTISET UNION DISTINCT v_b;  -- 1,2,3,4,5
  
  -- 交集
  v_c := v_a MULTISET INTERSECT v_b;  -- 3
  
  -- 差集
  v_c := v_a MULTISET EXCEPT v_b;  -- 1,2
END;
/

---

13. 性能

13.1 BULK COLLECT vs 单行

- BULK COLLECT 快 10-100x
- 减少 SQL/PLSQL 上下文切换

13.2 FORALL vs FOR

- FORALL 快 10x+
- 减少 DML 次数

13.3 LIMIT

- 1000-10000 平衡
- 内存 vs 性能

---

14. 常见坑与排错

14.1 ORA-06533

-- Subscript beyond count
-- EXTEND 先
v_nums.EXTEND;
v_nums(v_nums.COUNT) := 6;

14.2 ORA-06532

-- Subscript beyond limit
-- VARRAY 上限

14.3 ORA-6531

-- Reference to uninitialized collection
-- 初始化
v_nums := num_array();

---

15. 最佳实践

1. BULK COLLECT：批量
2. FORALL：DML 批量
3. LIMIT：内存
4. %TYPE/%ROWTYPE：解耦
5. Nested Table 数据库：灵活
6. VARRAY 固定上限：约束
7. Associative Array 内存：高效
8. TABLE 函数：SQL 集成
9. MULTISET：集合运算
10. 测试：性能

---

16. 参考资料

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