萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 8i 存儲過程返回結果集:SMS_PAY.SMSMAINT 包實現代理銀行流水查詢、全局臨時表緩存、動態 SQL 構建

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
動態 SQLEXECUTE IMMEDIATE 構建 DDL/DML 防注入:使用 USING 綁定變量,避免字符串拼接
異常處理統一捕獲返回錯誤碼 避免未處理異常導致游標未打開、調用端報錯

調用端模式 (Pro*C / OCI / JDBC)

-- 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;  -- 循環提取
copyright © 萬盛學電腦網 all rights reserved