ASP.NET 調用 Oracle 存儲過程實現高性能分頁查詢

業務場景

Web 開發中分頁是剛需。本文展示 ASP.NET 通過調用 Oracle 存儲過程，利用游標與動態 SQL 實現通用、高效的分頁方案。

核心 Package 定義

CREATE OR REPLACE PACKAGE MaterialManage IS
  TYPE T_CURSOR IS REF CURSOR;
  PROCEDURE Per_QuickPage (
    TbName     IN VARCHAR2,   -- 表名
    FieldStr   IN VARCHAR2,   -- 字段列表
    RowFilter  IN VARCHAR2,   -- 過濾條件
    OrderField IN VARCHAR2,   -- 排序字段
    PageSize   IN NUMBER,     -- 每頁行數
    PageIndex  IN NUMBER,     -- 頁碼 (從1開始)
    TotalCount OUT NUMBER,    -- 總記錄數
    ResultSet  OUT T_CURSOR   -- 結果游標
  );
END MaterialManage;

Package Body 實現要點

1. 動態構建 SELECT COUNT(*) 獲取總記錄數。
2. 使用 Oracle 分頁經典寫法（ROWNUM 雙層嵌套或 12c+ OFFSET ... FETCH）。
3. 通過 OPEN ... FOR 動態 SQL 返回 SYS_REFCURSOR。
4. 參數化防注入，字段/表名需白名單校驗。

ASP.NET 調用示例

using (OracleConnection conn = new OracleConnection(connStr))
{
  using (OracleCommand cmd = new OracleCommand("MaterialManage.Per_QuickPage", conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("TbName", "PRODUCTS");
    cmd.Parameters.Add("FieldStr", "ID, NAME, PRICE");
    cmd.Parameters.Add("RowFilter", "STATUS = 1");
    cmd.Parameters.Add("OrderField", "CREATE_TIME DESC");
    cmd.Parameters.Add("PageSize", 20);
    cmd.Parameters.Add("PageIndex", 3);
    cmd.Parameters.Add("TotalCount", OracleDbType.Int32, ParameterDirection.Output);
    cmd.Parameters.Add("ResultSet", OracleDbType.RefCursor, ParameterDirection.Output);

    conn.Open();
    cmd.ExecuteNonQuery();

    int total = Convert.ToInt32(cmd.Parameters["TotalCount"].Value);
    using (OracleDataReader dr = (cmd.Parameters["ResultSet"].Value as OracleRefCursor).GetDataReader())
    {
      // 綁定 GridView / ListView 等控件
    }
  }
}

優化建議

• 針對高頻查詢表創建覆蓋索引，避免回表。
• 大數據量場景考慮物化視圖或分區表配合分頁。
• 前端配合 AJAX 無刷新分頁，提升用戶體驗。