Oracle 8i 時代無 SYS_REFCURSOR,需自定義 弱類型 REF CURSOR 返回結果集。本文展示 SMS_PAY.SMSMAINT 包完整實現:動態構建查詢 → 全局臨時表緩存中間結果 → 打開游標返回,含 ON COMMIT PRESERVE ROWS 關鍵選項避免 ORA-08103。
CREATE OR REPLACE PACKAGE SMS_PAY.SMSMAINT AS
TYPE t_cursor IS REF CURSOR; -- 弱類型游標
PROCEDURE GetAgtBankFlow (
p_areacode IN VARCHAR2,
p_keyword IN VARCHAR2,
p_start_tradedate IN VARCHAR2,
p_end_tradedate IN VARCHAR2,
p_rc OUT t_cursor,
p_ret OUT NUMBER
);
END SMSMAINT;
/
CREATE OR REPLACE PACKAGE BODY SMS_PAY.SMSMAINT IS
PROCEDURE GetAgtBankFlow (...) IS
v_exists NUMBER;
v_sql VARCHAR2(2048);
BEGIN
-- 1. 檢查/創建全局臨時表(關鍵:ON COMMIT PRESERVE ROWS)
SELECT COUNT(*) INTO v_exists FROM all_tables WHERE table_name = 'TEMPAGTBANKFLOW';
IF v_exists = 0 THEN
v_sql := 'CREATE GLOBAL TEMPORARY TABLE SMS_PAY.TEMPAGTBANKFLOW (
AGTID VARCHAR2(13), BANK_GLIDE NUMBER(12), TRADETYPE VARCHAR2(20),
TRADE_MONEY NUMBER(14,2), TRADEDATE VARCHAR2(10), TRADETIME VARCHAR2(8),
BANKNAME VARCHAR2(30), AREACODE VARCHAR2(30), AREANAME VARCHAR2(30),
STATE VARCHAR2(30), CHECKFLAG NUMBER(1), STATUSFLAG NUMBER(1), SUCCFLAG NUMBER(1)
) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE v_sql;
ELSE
EXECUTE IMMEDIATE 'TRUNCATE TABLE SMS_PAY.TEMPAGTBANKFLOW';
END IF;
-- 2. 動態構建查詢並填充臨時表(省略具體業務 SQL)
-- v_sql := 'INSERT INTO TEMPAGTBANKFLOW SELECT ... FROM ... WHERE ...';
-- EXECUTE IMMEDIATE v_sql;
-- 3. 打開游標返回臨時表數據
OPEN p_rc FOR SELECT * FROM SMS_PAY.TEMPAGTBANKFLOW;
p_ret := 0;
EXCEPTION
WHEN OTHERS THEN
p_ret := SQLCODE;
-- 記錄錯誤日志
END GetAgtBankFlow;
END SMSMAINT;
/
| 技術點 | 說明 | 避坑指南 |
|---|---|---|
| 弱類型 REF CURSOR | TYPE t_cursor IS REF CURSOR; 無返回結構定義 |
調用端需知曉列結構,或用 DBMS_SQL.DESCRIBE_COLUMNS 動態描述 |
| 全局臨時表 | 會話/事務級私有數據,提交/結束自動清理 | 必須 指定 ON COMMIT PRESERVE ROWS,否則游標打開後提交導致數據消失 → ORA-08103 |
| 動態 SQL | EXECUTE IMMEDIATE 構建 DDL/DML |
防注入:使用 USING 綁定變量,避免字符串拼接 |
| 異常處理 | 統一捕獲返回錯誤碼 | 避免未處理異常導致游標未打開、調用端報錯 |
-- Pro*C 範例
EXEC SQL BEGIN DECLARE SECTION;
sql_cursor cursor_var;
int ret;
EXEC SQL END DECLARE SECTION;
EXEC SQL EXECUTE
BEGIN
SMS_PAY.SMSMAINT.GETAGTBANKFLOW(:areacode, :keyword, :sdate, :edate, :cursor_var, :ret);
END;
EXEC SQL FETCH :cursor_var INTO :host_vars; -- 循環提取