Oracle 動態游標實作：REF CURSOR 搭配 PIPELINED FUNCTION 回傳集合型別

動態 SQL + REF CURSOR + PIPELINED TABLE FUNCTION 完整範例

需求：統計查詢條件動態變化 (WHERE/Order By 子句不固定)，不使用暫存表，直接以函數回傳結構化結果集。本文示範建立 REF CURSOR 型別、物件型別、巢狀表型別，並在 Pipelined Function 中動態 OPEN FOR、FETCH、PIPE ROW 回傳資料。

1. 定義 REF CURSOR 型別 (Package Spec)

CREATE OR REPLACE PACKAGE RefCursor IS
    TYPE T_RefCursor IS REF CURSOR;
END RefCursor;
/

2. 定義回傳資料的物件型別

CREATE OR REPLACE TYPE TableType AS OBJECT (
    FIELD1 VARCHAR2(50),
    FIELD2 VARCHAR2(50),
    FIELD3 VARCHAR(50),
    FIELD4 VARCHAR2(50),
    FIELD5 VARCHAR2(20),
    FIELD6 FLOAT,
    FIELD7 FLOAT
);
/

3. 定義巢狀表型別 (集合)

CREATE OR REPLACE TYPE TableTypeList AS TABLE OF TableType;
/

4. Pipelined Function 實作動態游標

CREATE OR REPLACE FUNCTION "TEST" RETURN TableTypeList PIPELINED AS
    v_Cur           RefCursor.T_RefCursor;
    v_SQLStatement  VARCHAR2(10000);
    v_Table         TableType;
    -- 為了 FETCH INTO 方便，宣告對應變數
    tmp1  TARGET_TABLE.FIELD1%TYPE;
    tmp2  TARGET_TABLE.FIELD2%TYPE;
    tmp3  TARGET_TABLE.FIELD3%TYPE;
    tmp4  TARGET_TABLE.FIELD4%TYPE;
    tmp5  TARGET_TABLE.FIELD5%TYPE;
    tmp6  TARGET_TABLE.FIELD6%TYPE;
    tmp7  TARGET_TABLE.FIELD7%TYPE;
BEGIN
    -- 動態組裝 SQL (範例：條件與排序皆可變)
    v_SQLStatement := 'SELECT * FROM TARGET_TABLE WHERE FIELD1 = ''1'' ORDER BY FIELD1';
    
    OPEN v_Cur FOR v_SQLStatement;
    
    LOOP
        FETCH v_Cur INTO tmp1, tmp2, tmp3, tmp4, tmp5, tmp6, tmp7;
        EXIT WHEN v_Cur%NOTFOUND;
        
        -- 這裡可加入統計/轉換/過濾等商業邏輯
        -- 範例直接組裝物件並 PIPE ROW 回傳
        v_Table := TableType(tmp1, tmp2, tmp3, tmp4, tmp5, tmp6, tmp7);
        PIPE ROW(v_Table);
    END LOOP;
    
    CLOSE v_Cur;
    RETURN;
END;
/

5. 呼叫測試

-- SQL*Plus / SQL Developer
SELECT * FROM TABLE(TEST());

-- 或在 PL/SQL 區塊中
DECLARE
    v_Ret TableTypeList;
BEGIN
    v_Ret := TEST();
    FOR i IN 1..v_Ret.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_Ret(i).FIELD1 || ' - ' || v_Ret(i).FIELD2);
    END LOOP;
END;
/

關鍵技術點解析

進階應用建議

• 參數化動態 SQL：使用 USING 綁定變數防注入，避免字串拼接。
• 弱型別 REF CURSOR 回傳：若不想定義 OBJECT TYPE，函數可直接回傳 SYS_REFCURSOR，但呼叫端需處理游標讀取。
• 並行查詢：在動態 SQL 加入 /*+ PARALLEL */ Hint 加速大表掃描。
• 錯誤處理：在 FETCH 迴圈包裹 EXCEPTION WHEN OTHERS 記錄錯誤行並繼續。

版本相容性

• Oracle 9i 以上支援 PIPELINED FUNCTION
• 11g 起建議改用 DBMS_SQL 或 EXECUTE IMMEDIATE ... BULK COLLECT 簡化程式碼
• 12c 可搭配 DBMS_SQL.RETURN_RESULT 直接回傳隱性游標給用戶端