DB / Oracle / SQL 开发

Oracle PL/SQL Record 与 TABLE 类型详解

2026.09.06

Oracle PL/SQL Record 与 TABLE 类型详解

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

1. 概述

PL/SQL Record 和 TABLE 类型是复合数据结构[1]：

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

2. RECORD

2.1 定义

DECLARE
  TYPE emp_rec IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER(10, 2),
    hire_date DATE
  );
  
  v_emp emp_rec;
BEGIN
  v_emp.id := 1;
  v_emp.name := 'Alice';
  v_emp.salary := 5000;
  v_emp.hire_date := SYSDATE;
  
  DBMS_OUTPUT.PUT_LINE(v_emp.name || ': ' || v_emp.salary);
END;
/

2.2 %TYPE

DECLARE
  TYPE emp_rec IS RECORD (
    id employees.id%TYPE,
    name employees.name%TYPE,
    salary employees.salary%TYPE
  );
  v_emp emp_rec;
BEGIN
  ...
END;

2.3 %ROWTYPE

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

2.4 嵌套

DECLARE
  TYPE address_rec IS RECORD (
    street VARCHAR2(100),
    city VARCHAR2(50),
    zip VARCHAR2(10)
  );
  
  TYPE emp_rec IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    address address_rec
  );
  
  v_emp emp_rec;
BEGIN
  v_emp.id := 1;
  v_emp.name := 'Alice';
  v_emp.address.street := 'Main St';
  v_emp.address.city := 'NY';
  
  DBMS_OUTPUT.PUT_LINE(v_emp.name || ', ' || v_emp.address.city);
END;
/

3. RECORD 操作

3.1 赋值

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

3.2 比较

-- 不能直接比较
IF v_emp1 = v_emp2 THEN ...  -- ERROR

-- 逐字段
IF v_emp1.id = v_emp2.id AND v_emp1.name = v_emp2.name THEN ...

3.3 INSERT

DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  v_emp.id := 1;
  v_emp.name := 'Alice';
  v_emp.salary := 5000;
  
  INSERT INTO employees VALUES v_emp;
END;
/

3.4 UPDATE

DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM employees WHERE id = 1;
  v_emp.salary := v_emp.salary * 1.1;
  
  UPDATE employees SET ROW = v_emp WHERE id = 1;
END;
/

4. TABLE 类型

4.1 Associative Array

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  v_emps emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emps FROM employees;
  
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(i).name);
  END LOOP;
END;
/

4.2 Nested Table

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

4.3 VARRAY

DECLARE
  TYPE num_array IS VARRAY(10) OF NUMBER;
  v_nums num_array := num_array(1, 2, 3);
BEGIN
  ...
END;
/

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

5. RECORD of TABLE

DECLARE
  TYPE name_list IS TABLE OF VARCHAR2(100);
  
  TYPE dept_rec IS RECORD (
    id NUMBER,
    dept_name VARCHAR2(100),
    emp_names name_list
  );
  
  v_dept dept_rec;
BEGIN
  v_dept.id := 10;
  v_dept.dept_name := 'IT';
  v_dept.emp_names := name_list('Alice', 'Bob', 'Charlie');
  
  FOR i IN 1..v_dept.emp_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_dept.emp_names(i));
  END LOOP;
END;
/

6. TABLE of RECORD

DECLARE
  TYPE emp_rec IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER
  );
  
  TYPE emp_tab IS TABLE OF emp_rec;
  v_emps emp_tab := emp_tab();
BEGIN
  v_emps.EXTEND(3);
  
  v_emps(1).id := 1;
  v_emps(1).name := 'Alice';
  v_emps(1).salary := 5000;
  
  v_emps(2).id := 2;
  v_emps(2).name := 'Bob';
  v_emps(2).salary := 6000;
  
  v_emps(3).id := 3;
  v_emps(3).name := 'Charlie';
  v_emps(3).salary := 7000;
  
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(i).name || ': ' || v_emps(i).salary);
  END LOOP;
END;
/

7. SQL 类型

7.1 RECORD

-- 不能直接 CREATE TYPE RECORD
-- 使用 OBJECT 替代
CREATE TYPE emp_obj AS OBJECT (
  id NUMBER,
  name VARCHAR2(100),
  salary NUMBER
);
/

7.2 TABLE

CREATE TYPE num_list AS TABLE OF NUMBER;
CREATE TYPE emp_list AS TABLE OF emp_obj;
/

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

INSERT INTO departments VALUES (10, 'IT', 
  emp_list(
    emp_obj(1, 'Alice', 5000),
    emp_obj(2, 'Bob', 6000)
  )
);

8. 集合方法

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_tab(10, 20, 30, 40, 50);
BEGIN
  DBMS_OUTPUT.PUT_LINE('COUNT: ' || v_nums.COUNT);       -- 5
  DBMS_OUTPUT.PUT_LINE('FIRST: ' || v_nums.FIRST);       -- 1
  DBMS_OUTPUT.PUT_LINE('LAST: ' || v_nums.LAST);         -- 5
  DBMS_OUTPUT.PUT_LINE('NEXT(2): ' || v_nums.NEXT(2));   -- 3
  DBMS_OUTPUT.PUT_LINE('PRIOR(2): ' || v_nums.PRIOR(2)); -- 1
  
  IF v_nums.EXISTS(3) THEN
    DBMS_OUTPUT.PUT_LINE('Index 3 exists');
  END IF;
  
  v_nums.EXTEND(2);
  v_nums(6) := 60;
  v_nums(7) := 70;
  
  v_nums.TRIM(1);
  v_nums.DELETE(1);
  
  DBMS_OUTPUT.PUT_LINE('Final COUNT: ' || v_nums.COUNT);
END;
/

9. BULK COLLECT

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emps emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emps FROM employees;
  
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(i).name);
  END LOOP;
END;
/

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

10. FORALL

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

11. 应用场景

11.1 临时存储

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

11.2 批量处理

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emps emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emps FROM employees;
  
  FORALL i IN 1..v_emps.COUNT
    UPDATE employees SET salary = v_emps(i).salary * 1.1 WHERE id = v_emps(i).id;
END;
/

11.3 复杂结构

DECLARE
  TYPE address_rec IS RECORD (...);
  TYPE emp_rec IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    addr address_rec
  );
  TYPE emp_tab IS TABLE OF emp_rec;
  v_emps emp_tab;
BEGIN
  ...
END;
/

11.4 表参数

CREATE OR REPLACE PROCEDURE process_emps(p_emps IN emp_list) IS
BEGIN
  FORALL i IN 1..p_emps.COUNT
    INSERT INTO emp VALUES p_emps(i);
END;
/

12. 性能

12.1 BULK

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

12.2 内存

- 大集合占用
- LIMIT
- 释放

12.3 索引

- Associative Array：O(1) 查找
- Nested Table：顺序
- VARRAY：固定

13. 常见坑与排错

13.1 ORA-06533

- VARRAY 满
- EXTEND

13.2 ORA-22160

- 索引超界
- EXISTS

13.3 赋值

- RECORD 整体赋值
- 同类型

14. 最佳实践

  1. %ROWTYPE：行类型
  2. BULK COLLECT：批量
  3. LIMIT：内存
  4. EXISTS：检查
  5. EXTEND：扩展
  6. CACHE：临时
  7. FORALL：DML
  8. 类型选择：场景
  9. SQL 类型：持久
  10. 测试：验证

15. 参考资料

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