DB / Oracle / SQL 开发

Oracle 集合（Collection）与记录（Record）

2026.09.06

Oracle 集合（Collection）与记录（Record）

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

1. 概述

PL/SQL 复合数据类型[1]：

类型说明
Record记录（异构字段）
Associative Array索引表（键值）
Nested Table嵌套表
VARRAY可变数组

2. 记录（Record）

2.1 表基记录

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

2.2 列基记录

DECLARE
  v_name employees.last_name%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  SELECT last_name, salary INTO v_name, v_salary
  FROM employees WHERE employee_id = 100;
END;

2.3 自定义记录

DECLARE
  TYPE emp_record IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER := 0,
    hire_date DATE DEFAULT SYSDATE
  );
  
  v_emp emp_record;
BEGIN
  v_emp.id := 100;
  v_emp.name := 'Smith';
  DBMS_OUTPUT.PUT_LINE(v_emp.name || ': ' || v_emp.salary);
END;

2.4 记录赋值

DECLARE
  v_emp1 employees%ROWTYPE;
  v_emp2 employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp1 FROM employees WHERE employee_id = 100;
  v_emp2 := v_emp1;  -- 整体赋值
END;

3. 索引表（Associative Array）

3.1 基本用法

DECLARE
  TYPE name_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  v_names name_table;
BEGIN
  v_names(1) := 'Alice';
  v_names(2) := 'Bob';
  v_names(3) := 'Charlie';
  
  DBMS_OUTPUT.PUT_LINE(v_names(2));  -- Bob
  
  IF v_names.EXISTS(1) THEN
    DBMS_OUTPUT.PUT_LINE('Exists');
  END IF;
END;

3.2 字符串索引

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

3.3 遍历

DECLARE
  TYPE name_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  v_names name_table;
  v_idx PLS_INTEGER;
BEGIN
  v_names(1) := 'Alice';
  v_names(3) := 'Bob';
  v_names(5) := '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;

4. 嵌套表（Nested Table）

4.1 声明与初始化

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

4.2 操作

DECLARE
  TYPE num_table IS TABLE OF NUMBER;
  v_nums num_table := num_table(1, 2, 3);
BEGIN
  -- 添加
  v_nums.EXTEND;  -- 添加 1 个 NULL
  v_nums(4) := 4;
  
  v_nums.EXTEND(2);  -- 添加 2 个 NULL
  
  v_nums.EXTEND(3, 1);  -- 复制 v_nums(1) 3 次
  
  -- 删除
  v_nums.TRIM;  -- 删除末尾 1 个
  v_nums.TRIM(2);  -- 删除末尾 2 个
  
  v_nums.DELETE(2);  -- 删除索引 2
  v_nums.DELETE(1, 5);  -- 删除范围
  v_nums.DELETE;  -- 删除所有
  
  -- 大小
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT);
END;

4.3 数据库存储

-- 创建表
CREATE TABLE dept_emps (
  dept_id NUMBER,
  emp_names TAB_VARCHAR2  -- 嵌套表列
) NESTED TABLE emp_names STORE AS emp_names_tab;

-- 创建类型
CREATE OR REPLACE TYPE tab_varchar2 IS TABLE OF VARCHAR2(100);
/

-- 插入
INSERT INTO dept_emps VALUES (10, tab_varchar2('Alice', 'Bob', 'Charlie'));

5. VARRAY

5.1 声明

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);
  DBMS_OUTPUT.PUT_LINE('Limit: ' || v_nums.LIMIT);  -- 10
  
  v_nums.EXTEND;
  v_nums(4) := 4;
  
  FOR i IN 1..v_nums.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_nums(i));
  END LOOP;
END;

5.2 特点

  • 固定上限
  • 有序
  • 紧凑存储
  • 适合小数据

6. 集合方法

方法说明
COUNT元素数量
FIRST第一个索引
LAST最后一个索引
NEXT(n)下一个索引
PRIOR(n)前一个索引
EXISTS(n)是否存在
EXTEND添加元素（嵌套表/VARRAY）
TRIM删除末尾元素
DELETE删除元素
LIMIT最大容量（VARRAY）

7. 集合比较

类型索引大小数据库存储有序
索引表任意不限否否
嵌套表数字不限是否
VARRAY数字有限是是

8. 批量操作

8.1 BULK COLLECT

DECLARE
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  v_emps emp_table;
BEGIN
  SELECT * BULK COLLECT INTO v_emps
  FROM employees WHERE dept_id = 10;
  
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(i).last_name);
  END LOOP;
END;

8.2 FORALL

DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  v_ids id_table := id_table(1, 2, 3);
BEGIN
  FORALL i IN 1..v_ids.COUNT
    DELETE FROM employees WHERE employee_id = v_ids(i);
END;

详细见：Oracle BULK COLLECT 与 FORALL。

9. 记录与集合组合

DECLARE
  TYPE emp_record IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER
  );
  TYPE emp_table IS TABLE OF emp_record;
  v_emps emp_table;
BEGIN
  SELECT employee_id, last_name, salary 
  BULK COLLECT INTO v_emps
  FROM employees WHERE dept_id = 10;
  
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      v_emps(i).id || ': ' || v_emps(i).name || ' (' || v_emps(i).salary || ')'
    );
  END LOOP;
END;

10. 常见坑与排错

10.1 ORA-06533: 下标超出数量

-- 嵌套表/VARRAY 未 EXTEND
v_nums(1) := 100;  -- 错误

-- 修复
v_nums.EXTEND;
v_nums(1) := 100;

10.2 ORA-06502: 无效下标

-- 索引表未初始化
v_names(1) := 'Alice';  -- 索引表可以直接赋值

-- 嵌套表必须初始化
v_nums := num_table();  -- 初始化
v_nums.EXTEND;
v_nums(1) := 100;

10.3 ORA-06532: 下标超出限制

-- VARRAY 超过 LIMIT
DECLARE
  TYPE arr IS VARRAY(5) OF NUMBER;
  v_arr arr := arr(1,2,3,4,5);
BEGIN
  v_arr.EXTEND;  -- 错误：超过 LIMIT
END;

10.4 遍历 NULL 元素

-- 删除后索引不连续
v_nums.DELETE(3);
-- v_nums.COUNT 减少
-- 遍历时跳过 NULL

FOR i IN 1..v_nums.COUNT LOOP  -- 错误
  ...
END LOOP;

-- 修复
v_idx := v_nums.FIRST;
WHILE v_idx IS NOT NULL LOOP
  ...
  v_idx := v_nums.NEXT(v_idx);
END LOOP;

11. 最佳实践

  1. 索引表用于临时：内存高效
  2. 嵌套表用于存储：持久化
  3. VARRAY 用于固定：有序小数据
  4. %ROWTYPE 简化：跟随表
  5. BULK COLLECT 批量：性能
  6. 检查 EXISTS：避免异常
  7. 使用 FIRST/LAST/NEXT：安全遍历
  8. EXTEND 后赋值：嵌套表
  9. LIMIT 检查：VARRAY
  10. 批量操作：FORALL

12. 参考资料

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