您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

PL/SQL 複合資料類型宣告部完整指南：記錄、索引表、巢狀表、VARRAY 定義與操作實戰

編輯：Oracle 資料庫基礎

複合類型分類與作用域

記錄：異質欄位組合，類似結構體/表列。
集合：同質元素一維陣列，可嵌套形成多維。三種：索引表、巢狀表、VARRAY。
作用域：區塊/子程序中進入生效、退出失效；套件中首次引用生效、會話結束失效。

一、記錄

定義與宣告


TYPE my_record IS RECORD (
  name VARCHAR2(10),
  age  SMALLINT
);
v_rec my_record;

四大使用模式範例

手動賦值： v_rec.name := 'zhangsan'; v_rec.age := 20;
SELECT INTO 完整行對應： 定義欄位對應表列，SELECT deptno,dname,loc INTO v_rec FROM dept WHERE deptno=30;
SELECT INTO 部分列對應 + 手動補齊： SELECT dname,loc INTO v_rec.name,v_rec.city FROM dept ...; v_rec.no := 30;
%ROWTYPE 簡易定義： v_rec dept%ROWTYPE; SELECT * INTO v_rec FROM dept WHERE deptno=30; (欄位自動對應表結構)

二、索引表 (關聯陣列 / Index-by Table)

語法


TYPE my_tab_type IS TABLE OF element_type [NOT NULL]
INDEX BY BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size);
v_tab my_tab_type;

特性

鍵可為整數 (含負數) 或字串 (9i+)。
稀疏：下標可不連續 (如 -3, -1, 100)。
無元素數量上限。
僅存在記憶體，不可持久化至資料庫。
元素型別無限制 (可用 PL/SQL 專有型別如 BOOLEAN)。

範例：整數下標與字串下標


-- 整數下標
TYPE t1 IS TABLE OF dept.dname%TYPE NOT NULL INDEX BY BINARY_INTEGER;
v1 t1;
v1(-3) := 'RESEARCH'; v1(-1) := 'OPERATIONS';

-- 字串下標
TYPE t2 IS TABLE OF NUMBER NOT NULL INDEX BY VARCHAR2(20);
v2 t2;
v2('China') := 1; v2('Japan') := 2; v2('USA') := 3;
DBMS_OUTPUT.PUT_LINE(v2.FIRST);  -- China
DBMS_OUTPUT.PUT_LINE(v2.LAST);   -- USA

三、巢狀表

定義語法


TYPE nested_type IS TABLE OF element_type [NOT NULL];

下標為正整數，從 1 開始，預設緊密。
刪除元素後可變稀疏。
可持久化為資料庫欄位 (需 CREATE TYPE 全域定義)。
元素型別限制：全域型別不可用 BINARY_INTEGER, PLS_INTEGER, BOOLEAN, LONG, LONG RAW, NATURAL, POSITIVE, REF CURSOR, SIGNTYPE, STRING。

初始化 (構造函數)


v_nt nested_type := nested_type('A','B','C');  -- 長度 3
v_nt := nested_type();  -- 空集合 (非 NULL)
v_nt := nested_type('A', NULL, 'C');  -- 允許 NULL 元素

未初始化狀態為 原子性 NULL (整個集合 NULL)，不可直接引用元素，需先 IF v_nt IS NULL THEN v_nt := nested_type(); END IF;。

資料庫欄位應用範例


-- 全域型別
CREATE OR REPLACE TYPE phone_type IS TABLE OF VARCHAR2(20);
/
CREATE TABLE person (
  id   NUMBER,
  name VARCHAR2(10),
  sal  NUMBER(6,2),
  phone phone_type
) NESTED TABLE phone STORE AS phone_table;

-- 插入
INSERT INTO person VALUES (1, 'SCOTT', 800, phone_type('13256789876','031198765432'));

-- PL/SQL 讀取
DECLARE
  v_phones phone_type;
BEGIN
  SELECT phone INTO v_phones FROM person WHERE name='SCOTT';
  FOR i IN 1..v_phones.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Phone '||i||': '||v_phones(i));
  END LOOP;
END;

-- 更新整組集合
DECLARE
  v_phones phone_type := phone_type('034223455432','13888888888');
BEGIN
  UPDATE person SET phone = v_phones WHERE name='SCOTT';
END;

四、VARRAY (可變長陣列)

語法


TYPE varray_type IS VARRAY(max_size) OF element_type [NOT NULL];
v_arr varray_type := varray_type('A','B','C');  -- 最多 max_size 元素

特性對比

特性	巢狀表	VARRAY
最大元素數	無上限	固定 max_size
儲存方式	獨立儲存表	與主表同一資料區塊 (Inline)
順序保證	無序 (需 ORDER BY)	保持插入順序
適用場景	大型、頻繁增刪	小型、固定上限、順序敏感

範例


DECLARE
  TYPE vtype IS VARRAY(10) OF dept.dname%TYPE NOT NULL;
  v_arr vtype := vtype('X','Y','Z');
BEGIN
  SELECT dname INTO v_arr(1) FROM dept WHERE deptno=20;  -- RESEARCH
  SELECT dname INTO v_arr(2) FROM dept WHERE deptno=30;  -- SALES
  DBMS_OUTPUT.PUT_LINE(v_arr(1));  -- RESEARCH
  DBMS_OUTPUT.PUT_LINE(v_arr(2));  -- SALES
  DBMS_OUTPUT.PUT_LINE(v_arr(3));  -- Z (原始值)
END;