Oracle 8i 存儲過程透過 REF CURSOR 返回記錄集完整範例

核心概念

Oracle 8i 無隱性結果集，**必須使用 REF CURSOR (弱類型/強類型游標) 作為 OUT 參數** 返回查詢結果給呼叫端 (Java/VFP/.NET 等)。

套件規格 (SPEC)

CREATE OR REPLACE PACKAGE SMSMAINT AS
    TYPE T_CURSOR IS REF CURSOR;  -- 弱類型游標
    PROCEDURE GetAgtBankFlow(
        AreaCode       IN VARCHAR2,
        KeyWord        IN VARCHAR2,
        StartTradeDate IN VARCHAR2,
        EndTradeDate   IN VARCHAR2,
        Re_CURSOR      OUT T_CURSOR,
        Ret            OUT NUMBER);
    PROCEDURE GetAgtBalance(
        AreaCode  IN VARCHAR2,
        KeyWord   IN VARCHAR2,
        Re_CURSOR OUT T_CURSOR,
        Ret       OUT NUMBER);
END SMSMAINT;

套件主體 (BODY) 關鍵實作

1. 動態 SQL + 全域暫存表 (GTT) 暫存中間結果

-- 檢查 GTT 是否存在，不存在則建立 (ON COMMIT PRESERVE ROWS 保留跨 Commit 可見)
SELECT COUNT(*) INTO IsExists FROM ALL_TABLES WHERE TABLE_NAME = 'TEMPAGTBANKFLOW';
IF IsExists = 0 THEN
    EXECUTE IMMEDIATE q'[
        CREATE GLOBAL TEMPORARY TABLE SMS_PAY.TEMPAGTBANKFLOW (
            AGTID VARCHAR2(13), BANK_GLIDE NUMBER(12), ...
        ) ON COMMIT PRESERVE ROWS]';
ELSE
    EXECUTE IMMEDIATE 'TRUNCATE TABLE SMS_PAY.TEMPAGTBANKFLOW';
END IF;

2. 依條件動態組裝 INSERT SQL 填充 GTT

strSQL := 'INSERT INTO TEMPAGTBANKFLOW SELECT ... FROM ... WHERE 1=1';
IF AreaCode != '0' THEN strSQL := strSQL || ' AND TRIM(C.AREACODE) = ''' || AreaCode || ''''; END IF;
IF StartTradeDate IS NOT NULL THEN ... END IF;
IF KeyWord IS NOT NULL THEN ... END IF;
EXECUTE IMMEDIATE strSQL;

3. 開啟 REF CURSOR 返回結果

OPEN Re_CURSOR FOR 'SELECT * FROM SMS_PAY.TEMPAGTBANKFLOW ORDER BY AGTID';
Ret := 0;
RETURN;

4. 異常處理

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        Ret := 999;
        RETURN;

第二個程序：GetAgtBalance (雙層游標巢狀處理)

1. 開啟一級代理游標 FstCursor 依條件查詢。
2. 迴圈 FETCH 每筆一級代理，寫入 GTT TEMPAGTBALANCE。
3. 同步查詢該一級代理下的二級代理，批次寫入 GTT。
4. 更新一級代理的下級筆數欄位。
5. 最後 OPEN Re_CURSOR FOR SELECT * FROM TEMPAGTBALANCE 返回。

呼叫端關鍵點

• Java (JDBC)：**註冊 OUT 參數為 OracleTypes.CURSOR**，執行後 ((OracleCallableStatement)cstmt).getCursor(n) 取得 ResultSet。
• .NET (ODP.NET)：**OracleParameter.Direction = ParameterDirection.Output, OracleDbType.RefCursor**，執行後 ((OracleRefCursor)param.Value).GetDataReader()。
• VFP：SQLEXEC(nh, "{call pkg.proc(?, ?)}", "cursor_name") 直接映射到 VFP 游標。

最佳實踐提醒

1. GTT 定義 **ON COMMIT PRESERVE ROWS**，否則 Commit 後資料消失導致 ORA-08103。
2. 動態 SQL 使用 q'[...]' 替代引號雙重轉義，提升可讀性。
3. 大量資料考慮分頁或管道函數 (PIPELINED) 替代 GTT，減少 Temp 表空間壓力。
4. 嚴格檢查輸入參數防 SQL 注入，或改用 DBMS_ASSERT。