Oracle 存儲過程參數配置實戰：從 IN/OUT 參數到分頁查詢完整示例

前言

Oracle 存儲過程參數的正確配置是 .NET/Java 應用高效交互的關鍵。本文通過一個完整的分頁查詢案例，詳細講解 OracleParameter 的方向、類型映射、命名規範，以及前端 AspNetPager 控件的整合技巧。

一、 參數定義核心規範

1. 名稱一致性：C# 代碼中 new OracleParameter("Pindex", ...) 的參數名必須與存儲過程定義 PROCEDURE Pagination(Pindex IN NUMBER...) 完全一致，否則拋錯。
2. 方向顯式聲明：ParameterDirection.Input / Output / InputOutput 必須顯式設置，切勿依賴默認值。
3. 類型映射：OracleType.Number 對應 NUMBER，VarChar 對應 VARCHAR2，Cursor 對應 REF CURSOR。

二、 後端存儲過程接口設計（建議簽名）

PROCEDURE Pagination (
  Pindex     IN  NUMBER,      -- 當前頁碼
  Psql       IN  VARCHAR2,    -- 動態查詢條件 SQL 片段
  Psize      IN  NUMBER,      -- 頁大小
  Pcount     OUT NUMBER,      -- 總頁數
  Prowcount  OUT NUMBER,      -- 總行數
  v_cur      OUT SYS_REFCURSOR -- 結果集游標
);

三、 C# 數據訪問層完整實現

public static DataTable ReturnDataTable(int index, string sql, int pageSize)
{
    DataTable dt = new DataTable();
    try
    {
        // 1. 定義參數陣列（順序與存儲過程一致）
        OracleParameter[] param = new OracleParameter[]
        {
            new OracleParameter("Pindex", OracleType.Number),
            new OracleParameter("Psql", OracleType.VarChar),
            new OracleParameter("Psize", OracleType.Number),
            new OracleParameter("Pcount", OracleType.Number),
            new OracleParameter("Prowcount", OracleType.Number),
            new OracleParameter("v_cur", OracleType.Cursor)
        };

        // 2. 賦值與方向設置
        param[0].Value = index;        param[0].Direction = ParameterDirection.Input;
        param[1].Value = sql;          param[1].Direction = ParameterDirection.Input;
        param[2].Value = pageSize;     param[2].Direction = ParameterDirection.Input;
        param[3].Direction = ParameterDirection.Output;  -- Pcount
        param[4].Direction = ParameterDirection.Output;  -- Prowcount
        param[5].Direction = ParameterDirection.Output;  -- v_cur

        // 3. 執行存儲過程
        dt = OracleHelper.ReturnDataTable(
            OracleHelper.dbCon,
            CommandType.StoredProcedure,
            "JT_P_page.Pagination",
            param
        );

        // 4. 獲取輸出參數（轉換為靜態屬性供前端分頁控件使用）
        Help._rowCount = int.Parse(param[4].Value.ToString());
    }
    catch (OracleException ex)
    {
        // 建議：記錄日志後拋出自定義異常，避免直接 throw ex 丟失堆棧
        throw new DataAccessException("分頁查詢失敗", ex);
    }
    return dt;
}

四、 前端 AspNetPager 整合要點

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView(0, "union_view", _pageSize);
        // 關鍵：從靜態屬性獲取總行數賦給分頁控件
        ASPNetPager1.RecordCount = (Help._rowCount >= 0) ? Help._rowCount : 0;
    }
}

private void BindGridView(int index, string sql, int pageSize)
{
    DataTable dt;
    if (!string.IsNullOrEmpty(sql))
    {
        ViewState["IsProcedure"] = "yes";
        dt = Help.ReturnDataTable(index, sql, pageSize);
    }
    else
    {
        ViewState["IsProcedure"] = "no";
        dt = Help.ReturnDataTableByCondition(...); // 另一查詢路徑
    }
    iagentView.DataSource = dt;
    DataBind();
    
    // 動態設置頁面大小與總記錄數
    ASPNetPager1.PageSize = pageSize;
    ASPNetPager1.RecordCount = (sql != "") ? Help._rowCount : Help.ReturnRows(...);
}

五、 避坑指南與最佳實踐

• 游標處理：OracleType.Cursor 返回的 OracleDataReader 需在 DataTable.Load() 後自動關閉，確保 OracleHelper 內部正確處理。
• 參數順序：嚴格按存儲過程定義順序排列陣列，param[5] 必須是游標輸出參數。
• 異常處理：捕獲 OracleException 後建議包裝業務異常，記錄完整 SQL 與參數值便於排查。
• 性能優化：動態 SQL (Psql) 若頻繁變化會導致硬解析，建議改用 SYS_REFCURSOR + 靜態 SQL + 綁定變量。

六、 總結

掌握 OracleParameter 的方向、類型、命名三要素，結合 OracleHelper 封裝與 AspNetPager 雙向綁定，即可構建穩健高效的分頁查詢方案。實戰中請嚴守「參數名與存儲過程完全一致」這一鐵律，避免 90% 的調用失敗。