Oracle 函數回傳物件表的三種實作方法效能比較

前言

在 PL/SQL 開發中，常需撰寫「回傳集合」的函數供 SQL 陳述式以 TABLE() 運算子查詢。本文以 EMP 表為例，示範三種回傳 t_test_table (集合型別) 的寫法，並說明各自優缺點與適用情境。

一、前置型別定義

所有方法皆需先建立 Schema 層級 的物件型別與集合型別（不可在 PL/SQL 區塊內部宣告）：

CREATE OR REPLACE TYPE t_test AS OBJECT (
    empno NUMBER(4),
    ename VARCHAR2(10),
    job   VARCHAR2(9),
    sal   NUMBER(7,2)
);
/
CREATE OR REPLACE TYPE t_test_table AS TABLE OF t_test;
/

關鍵點：欄位型別必須為 OBJECT，不能使用 RECORD；否則 SQL 引擎無法識別。

二、方法一：陣列擴充 (Array Extend)

CREATE OR REPLACE FUNCTION f_test_array (v_deptno IN NUMBER DEFAULT NULL)
RETURN t_test_table IS
    v_test t_test_table := t_test_table();  -- 初始化空集合
    CURSOR cur IS
        SELECT empno, ename, job, sal FROM emp WHERE deptno = v_deptno;
BEGIN
    FOR c IN cur LOOP
        v_test.EXTEND;                              -- 擴充一個元素
        v_test(v_test.COUNT) := t_test(c.empno, c.ename, c.job, c.sal);
    END LOOP;
    RETURN v_test;
END;
/

特性：

• 需顯式管理集合大小 (EXTEND/COUNT)。
• 適合邏輯複雜、需逐列處理或條件式塞入的情境。
• 效能較差：每次迴圈皆產生集合擴充與物件建構的額外負擔。

三、方法二：PIPELINED 管線函數

CREATE OR REPLACE FUNCTION f_test_pipe (v_deptno IN NUMBER DEFAULT NULL)
RETURN t_test_table PIPELINED IS
    CURSOR cur IS
        SELECT empno, ename, job, sal FROM emp WHERE deptno = v_deptno;
BEGIN
    FOR c IN cur LOOP
        PIPE ROW (t_test(c.empno, c.ename, c.job, c.sal));  -- 直接輸出一列
    END LOOP;
    RETURN;  -- 不需回傳集合變數
END;
/

特性：

• 關鍵字 PIPELINED + PIPE ROW 即可「串流」輸出，不需在記憶體中累積完整集合。
• 記憶體佔用低、首筆資料回應快，適合大量資料或 ETL 轉換管線。
• 無法在函數內使用 COMMIT/ROLLBACK，且不支援平行查詢 (Parallel Query)。

四、方法三：BULK COLLECT 整批收集

CREATE OR REPLACE FUNCTION f_test_collect (v_deptno IN NUMBER DEFAULT NULL)
RETURN t_test_table IS
    v_test t_test_table;
BEGIN
    SELECT t_test(empno, ename, job, sal)  -- 必須先「物件化」投影
    BULK COLLECT INTO v_test
    FROM emp
    WHERE deptno = v_deptno;
    RETURN v_test;
END;
/

關鍵細節：

• SELECT 清單必須寫成 t_test(empno, ename, job, sal)，將多欄位「包裝」成單一物件；否則會噴 ORA-00947: not enough values (多欄位) 或 ORA-00932: inconsistent datatypes (單欄位)。
• 在純 PL/SQL 區塊中 DECLARE 區域型別時則不需此步驟，僅限 CREATE FUNCTION 回傳 Schema 型別時必要。
• 程式碼最精簡、可讀性佳，效能介於方法一與二之間；資料量極大時記憶體壓力較管線函數大。

五、驗證與效能觀察

三種函數皆可用相同語法測試：

SELECT * FROM TABLE(f_test_array(30));
SELECT * FROM TABLE(f_test_pipe(30));
SELECT * FROM TABLE(f_test_collect(30));

六、選擇建議

結語

掌握三種回傳集合的寫法，能依據業務需求在「開發效率」、「記憶體使用」與「回應時間」間取得最佳平衡。實務上若無特殊邏輯，優先考慮 BULK COLLECT；涉及大量資料轉換或 ETL 管線則首選 PIPELINED。