.NET 呼叫 Oracle 儲存程序返回游標結果集完整指南

前言

在 Oracle 資料庫開發中，儲存程序返回結果集是常見需求。關鍵在於使用 REF CURSOR（游標變數）。不同於靜態游標綁定特定查詢，游標變數可為任何類型相容的查詢開啟，極大提升了資料提取的靈活性。本文將完整示範從資料庫端建立套件、儲存程序，到 .NET 端呼叫的完整流程。

資料庫端實作

1. 建立測試資料表

CREATE TABLE LIHUAN.BILL_POINTS (
  POINTS_ID     NUMBER(10,0) NOT NULL,
  CUSTOMER_ID   NUMBER(10,0) NOT NULL,
  BILL_POINT_NO NUMBER(2,0)  DEFAULT 1 NOT NULL,
  CONSTRAINT PK_BILL_POINTS PRIMARY KEY (POINTS_ID)
);

2. 建立套件規格

定義 REF CURSOR 型別與程序介面：

CREATE OR REPLACE PACKAGE LIHUAN.YY_PKG_BILL_POINT_NO IS
  -- 取得用戶的所有計費點序號
  TYPE T_CURSOR IS REF CURSOR;
  PROCEDURE BILL_POINT_NO(
    P_CUSTOMER_ID IN  BILL_POINTS.CUSTOMER_ID%TYPE,
    Re_CURSOR     OUT T_CURSOR
  );
END;
/

3. 建立套件主體

實作邏輯：開啟游標變數並指派給輸出參數：

CREATE OR REPLACE PACKAGE BODY LIHUAN.YY_PKG_BILL_POINT_NO IS
  PROCEDURE BILL_POINT_NO(
    P_CUSTOMER_ID IN  BILL_POINTS.CUSTOMER_ID%TYPE,
    Re_CURSOR     OUT T_CURSOR
  ) IS
    V_CURSOR T_CURSOR;
  BEGIN
    OPEN V_CURSOR FOR
      SELECT Bill_Point_No
      FROM   BILL_POINTS
      WHERE  CUSTOMER_ID = P_CUSTOMER_ID;
    Re_CURSOR := V_CURSOR;
  END;
END;
/

.NET 端呼叫實作

使用 System.Data.OracleClient 呼叫帶 REF CURSOR 輸出參數的儲存程序，並透過 OracleDataAdapter 填充 DataSet。

public DataSet Bill_Point_No(string customerId)
{
    DataSet dataset = new DataSet();
    Hashtable ht = new Hashtable();
    ht.Add("P_CUSTOMER_ID", customerId);

    if (RunProcedure("Re_CURSOR", OracleType.Cursor, ref dataset, ht,
        "bmsOracleUser.YY_PKG_BILL_POINT_NO.BILL_POINT_NO",
        bmsOracleConnectionString))
    {
        // 成功，dataset 已填充
    }
    else
    {
        dataset = null;
    }
    return dataset;
}

public bool RunProcedure(string returnParameter, OracleType paramType,
    ref DataSet dataset, Hashtable ht, string procedureName, string connectionString)
{
    using (var conn = new System.Data.OracleClient.OracleConnection(connectionString))
    using (var cmd = new System.Data.OracleClient.OracleCommand(procedureName, conn))
    {
        conn.Open();
        cmd.CommandType = CommandType.StoredProcedure;

        // 註冊 REF CURSOR 輸出參數
        var outParam = cmd.Parameters.Add(new OracleParameter(returnParameter, paramType));
        outParam.Direction = ParameterDirection.Output;

        // 註冊輸入參數
        foreach (DictionaryEntry entry in ht)
        {
            cmd.Parameters.Add(new OracleParameter(entry.Key.ToString(), entry.Value));
        }

        using (var adapter = new OracleDataAdapter(cmd))
        {
            try
            {
                adapter.Fill(dataset);
                return true;
            }
            catch (Exception ex)
            {
                // 記錄錯誤日誌
                return false;
            }
        }
    }
}

關鍵點總結

• Oracle 端必須定義 TYPE ... IS REF CURSOR，程序參數型別為該型別並標記 OUT。
• .NET 端 OracleParameter 型別設為 OracleType.Cursor，Direction = ParameterDirection.Output。
• OracleDataAdapter.Fill() 會自動讀取 REF CURSOR 並填充 DataSet。
• 使用 using 區塊確保連線、命令、適配器正確釋放資源。