Oracle PL/SQL 集合類型作為參數傳遞：索引表、VARRAY、巢狀表深度對比與最佳選型

為何需要集合參數？

標量參數 (NUMBER/VARCHAR2) 單值回傳，無法滿足批量資料回傳、二維結果集、複雜統計報表 需求。PL/SQL 三大集合類型提供陣列/集合操作能力。

三大集合類型對比總表

實作範例：三種類型完整程式碼

1. 索引表 (Index-by Table) - 僅限 Package + OCI

-- Package 定義 (必須在 Package Spec)
CREATE OR REPLACE PACKAGE pkg_demo AS
    TYPE idx_tab_t IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
    PROCEDURE get_names(p_deptno IN NUMBER, p_names OUT idx_tab_t);
END pkg_demo;
/

CREATE OR REPLACE PACKAGE BODY pkg_demo AS
    PROCEDURE get_names(p_deptno IN NUMBER, p_names OUT idx_tab_t) IS
        CURSOR c IS SELECT ename FROM emp WHERE deptno = p_deptno;
        v_idx BINARY_INTEGER := 1;
    BEGIN
        FOR r IN c LOOP
            p_names(v_idx) := r.ename;  -- 自動擴展，無需 EXTEND
            v_idx := v_idx + 1;
        END LOOP;
    END;
END pkg_demo;
/

2. VARRAY - 固定上限、連續記憶體

-- SQL 層級建立型別
CREATE OR REPLACE TYPE num_varray IS VARRAY(10) OF NUMBER(3);
/

-- Function 回傳
CREATE OR REPLACE FUNCTION get_seq RETURN num_varray IS
    v_arr num_varray := num_varray();  -- 必須初始化
BEGIN
    FOR i IN 1..5 LOOP
        v_arr.EXTEND;
        v_arr(i) := i * 10;
    END LOOP;
    RETURN v_arr;
END;
/
-- Java 端直接映射為 int[] 或 Array

3. 巢狀表 + REF CURSOR - 企業級標準作法

-- 1. 物件型別 (對應結果集欄位)
CREATE OR REPLACE TYPE stat_obj AS OBJECT (
    dept_name VARCHAR2(30),
    emp_count NUMBER,
    avg_sal   NUMBER,
    max_sal   NUMBER
);
/

-- 2. 巢狀表型別
CREATE OR REPLACE TYPE stat_tab IS TABLE OF stat_obj;
/

-- 3. Package 定義 REF CURSOR
CREATE OR REPLACE PACKAGE pkg_report AS
    TYPE ref_cur IS REF CURSOR;
    PROCEDURE dept_stats(p_cur OUT ref_cur);
END pkg_report;
/

-- 4. 實作：填充巢狀表 -> CAST -> OPEN CURSOR
CREATE OR REPLACE PACKAGE BODY pkg_report AS
    PROCEDURE dept_stats(p_cur OUT ref_cur) IS
        v_tab stat_tab := stat_tab();  -- 初始化
    BEGIN
        -- 模擬查詢填充
        v_tab.EXTEND;
        v_tab(1) := stat_obj('SALES', 10, 2500, 5000);
        v_tab.EXTEND;
        v_tab(2) := stat_obj('IT', 5, 4000, 8000);
        
        -- 關鍵：CAST 巢狀表為 SQL 集合 -> 開啟游標
        OPEN p_cur FOR SELECT * FROM TABLE(CAST(v_tab AS stat_tab));
    END;
END pkg_report;
/

選型決策樹

1. 需在 SQL 中操作集合 (JOIN、WHERE IN、TABLE()) → 巢狀表
2. 回傳大量資料給 Java/.NET 客戶端 → 巢狀表 + REF CURSOR (分批抓取、低記憶體)
3. 小量資料、簡單陣列映射、追求效能 → VARRAY
4. 純 PL/SQL 內部運算、臨時快取、不需跨 SQL 引擎 → 索引表 (最靈活、自動擴展)
5. 需持久化儲存於資料表欄位 → VARRAY 或巢狀表
6. JDBC Thin 驅動環境 → 避免索引表 (需 OCI)

大資料量效能關鍵：游標 vs 陣列

• 陣列回傳：PL/SQL 一次配置所有記憶體 → 網路傳輸所有資料 → 客戶端一次配置所有記憶體，**記憶體峰值 3x 資料量**
• REF CURSOR 回傳：僅傳回指標 → 客戶端分批 `FETCH` (可設定 `row-prefetch-size`) → **記憶體平穩、網路流控、支援中斷續傳**
• 建議：超過 1000 筆或含 LOB 欄位必須用 REF CURSOR

常見陷阱

• ❌ 索引表作為 OUT 參數但用 JDBC Thin → `ClassNotFoundException: ocijdbc`
• ❌ VARRAY 未 `EXTEND` 直接指派 → `ORA-06532: Subscript outside of limit`
• ❌ 巢狀表未初始化直接 `EXTEND` → `ORA-06531: Collection not initialized`
• ❌ `CAST` 型別不匹配 → `ORA-22905: cannot access rows from a non-nested table item`
• ❌ 大量資料用陣列回傳導致 OOM → 改用 REF CURSOR 分頁