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

Oracle PL/SQL 集合類型完全指南：關聯陣列、巢狀表、VARRAY 差異與實戰

編輯：Oracle 資料庫相關知識

Oracle 提供三種集合型別：**關聯陣列**、**巢狀表**、**VARRAY**。三者在儲存方式、長度限制、資料庫持久化、索引型別、適用場景上差異巨大。本文透過完整範例碼拆解三者定義、初始化、操作、遍歷差異，並給出選型決策表。

三大集合型別定義語法

DECLARE
    -- 1. 關聯陣列：索引為 BINARY_INTEGER/PLS_INTEGER，可負數、不連續，僅 PL/SQL 可用，不可存資料庫
    TYPE t_assoc IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    -- 2. 巢狀表：索引為正整數 1..N，可擴展，可存資料庫欄位
    TYPE t_nested IS TABLE OF NUMBER;

    -- 3. VARRAY：固定最大長度，索引 1..N，可存資料庫欄位，須指定上限
    TYPE t_varray IS VARRAY(10) OF NUMBER;

    v_assoc  t_assoc;
    v_nested t_nested;
    v_varray t_varray;
BEGIN
    -- 關聯陣列：直接指定索引賦值，無需 EXTEND
    v_assoc(1)   := 1;
    v_assoc(2)   := 2;
    v_assoc(-5)  := 99;  -- 索引可為負數、不連續

    -- 巢狀表：需先初始化，再 EXTEND 再賦值
    v_nested := t_nested(1,2,3,4,5);  -- 建構函數一次初始化 5 元素
    -- 或逐步
    v_nested := t_nested();
    v_nested.EXTEND; v_nested(1) := 10;

    -- VARRAY：建構函數指定元素，不可超過上限 10
    v_varray := t_varray(1,2);
END;
/

-- 關聯陣列遍歷：FIRST/NEXT 迭代索引
DECLARE
    TYPE t_arr IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
    v t_arr;
    idx NUMBER;
BEGIN
    v(1) := 'a1'; v(2) := 'a2'; v(-1) := 'a-1';
    idx := v.FIRST;
    LOOP
        EXIT WHEN idx IS NULL;
        DBMS_OUTPUT.PUT_LINE(v(idx));
        idx := v.NEXT(idx);
    END LOOP;
END;
/

-- 巢狀表遍歷：可用 FIRST/NEXT 或 1..COUNT 迴圈
DECLARE
    TYPE t_arr IS TABLE OF VARCHAR2(100);
    v t_arr := t_arr();
BEGIN
    v.EXTEND; v(1) := 'a1';
    v.EXTEND; v(2) := 'a2';
    v.EXTEND; v(3) := 'a3';
    FOR i IN 1 .. v.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v(i));
    END LOOP;
END;
/

核心差異對照表

特性	關聯陣列	巢狀表	VARRAY
索引型別	BINARY_INTEGER/PLS_INTEGER（可負、不連續）	正整數 1..N（連續）	正整數 1..N（連續）
長度限制	無（受限於記憶體）	無（受限於記憶體）	固定上限（CREATE TYPE 時指定）
資料庫持久化	不可存資料表欄位	可存資料表欄位	可存資料表欄位
初始化	宣告即可用，無建構函數	需建構函數 `t_nested()` 或 `t_nested(v1,v2...)`	需建構函數 `t_varray(v1,v2...)`
擴展方式	直接指定新索引賦值	`EXTEND` 後賦值	`EXTEND` 後賦值（不可超過上限）
刪除元素	`DELETE(idx)`、`DELETE` 全刪	`DELETE(idx)`、`TRIM` 尾部刪	`TRIM` 尾部刪
適用場景	PL/SQL 內部暫存、查找表、稀疏索引	大量資料、需持久化、資料庫欄位	小量固定集合、需保持順序、資料庫欄位
官方建議	PL/SQL 內部首選，效能最佳	大集合、分批存取	小集合、整體存取

進階：關聯陣列作為查找表

DECLARE
    TYPE t_dept IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
    v_dept t_dept;
BEGIN
    v_dept(10) := 'ACCOUNTING';
    v_dept(20) := 'RESEARCH';
    v_dept(30) := 'SALES';
    v_dept(40) := 'OPERATIONS';

    -- O(1) 查找
    IF v_dept.EXISTS(20) THEN
        DBMS_OUTPUT.PUT_LINE('Dept 20: ' || v_dept(20));
    END IF;
END;
/

進階：巢狀表存資料庫欄位

CREATE TYPE t_num_tab AS TABLE OF NUMBER;
/

CREATE TABLE dept_scores (
    deptno NUMBER,
    scores t_num_tab
) NESTED TABLE scores STORE AS scores_nt;

INSERT INTO dept_scores VALUES (10, t_num_tab(85,90,78));
INSERT INTO dept_scores VALUES (20, t_num_tab(92,88,95,80));

SELECT d.deptno, nt.COLUMN_VALUE AS score
FROM dept_scores d, TABLE(d.scores) nt;