ASP.NET 呼叫 Oracle 預存程序實現高效分頁查詢

前言：Web 開發中的分頁挑戰

在 Web 應用開發中，分頁查詢是核心且必須解決的效能議題。本文將詳細示範如何在 ASP.NET 環境下，透過呼叫 Oracle 預存程序 實現高效率的分頁邏輯，並提供完整的 PL/SQL 包定義、包主體實作與 C# 呼叫代碼供參考。

Oracle 端實作：動態 SQL 與 REF CURSOR

套件規格

CREATE OR REPLACE PACKAGE MaterialManage IS
  TYPE T_CURSOR IS REF CURSOR;
  PROCEDURE Per_QuickPage (
    TbName        IN  VARCHAR2,  -- 表名
    FieldStr      IN  VARCHAR2,  -- 欄位集合
    RowFilter     IN  VARCHAR2,  -- 過濾條件
    SortStr       IN  VARCHAR2,  -- 排序欄位
    RownumFieldStr IN VARCHAR2,  -- 分頁條件
    TotalCount    OUT NUMBER,    -- 總筆數
    Cur_ReturnCur OUT T_CURSOR   -- 回傳游標
  );
END MaterialManage;

套件主體：利用 ROWNUM 多層巢狀查詢

CREATE OR REPLACE PACKAGE BODY MaterialManage IS
  PROCEDURE Per_QuickPage (
    TbName      IN  VARCHAR2,
    FieldStr    IN  VARCHAR2,
    RowFilter   IN  VARCHAR2,
    SortStr     IN  VARCHAR2,
    MinRowNum   IN  NUMBER,    -- 分頁下限
    MaxRowNum   IN  NUMBER,    -- 分頁上限
    TotalCount  OUT NUMBER,
    Cur_ReturnCur OUT T_CURSOR
  ) IS
    v_SourceTb1 VARCHAR2(3000);
    v_SourceTb2 VARCHAR2(3000);
    v_SourceTb3 VARCHAR2(3000);
    v_SourceTb4 VARCHAR2(3000);
    v_TotalCount VARCHAR2(50);
    v_sql       VARCHAR2(3000);
  BEGIN
    -- 第 1 層：選取指定欄位
    v_SourceTb1 := '(SELECT ' || FieldStr || ' FROM ' || TbName || ') SourceTb1';
    -- 第 2 層：套用過濾與排序
    v_SourceTb2 := '(SELECT * FROM ' || v_SourceTb1 || ' WHERE ' || RowFilter || ' ' || SortStr || ') SourceTb2';
    -- 第 3 層：加上 ROWNUM 並截取上限
    v_SourceTb3 := '(SELECT ROWNUM AS RowIndex, SourceTb2.* FROM ' || v_SourceTb2 || ' WHERE ROWNUM <= ' || MaxRowNum || ') SourceTb3';
    -- 第 4 層：計算總筆數（不分頁）
    v_SourceTb4 := '(SELECT * FROM ' || v_SourceTb1 || ' WHERE ' || RowFilter || ') SourceTb4';

    v_sql := 'SELECT COUNT(*) AS TotalCount FROM ' || v_SourceTb4;
    EXECUTE IMMEDIATE v_sql INTO v_TotalCount;
    TotalCount := v_TotalCount;

    -- 最後依 RowIndex 篩選分頁區間
    v_sql := 'SELECT * FROM ' || v_SourceTb3 || ' WHERE RowIndex >= ' || MinRowNum;
    OPEN Cur_ReturnCur FOR v_sql;
  END Per_QuickPage;
END MaterialManage;

原理說明： Oracle 的 ROWNUM 偽欄位特性使得分頁成為可能。上述邏輯先查總筆數，再透過多層內聯視圖疊加 ROWNUM，最後在外層依 RowIndex 篩選目標頁面區間。回傳兩個參數：TotalCount（總筆數）與 Cur_ReturnCur（結果集游標）。

作者註：實測上述分頁效能並非最優，若有更佳寫法歡迎分享交流。

ASP.NET (C#) 呼叫端代碼

/// 

/// 呼叫預存程序實現快速分頁
/// 
public DataTable QuickPage(
    string TbName, string FieldStr, string RowFilter, string SortStr,
    int MinRowNum, int MaxRowNum, ref int RecordCount)
{
    using (OracleConnection conn = new OracleConnection(
        ConfigurationSettings.AppSettings["OracleConnstr"].ToString()))
    using (OracleCommand cmd = new OracleCommand("MaterialManage.Per_QuickPage", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("TbName", OracleType.VarChar, 50).Value = TbName;
        cmd.Parameters["TbName"].Direction = ParameterDirection.Input;

        cmd.Parameters.Add("FieldStr", OracleType.VarChar, 3000).Value = FieldStr;
        cmd.Parameters["FieldStr"].Direction = ParameterDirection.Input;

        cmd.Parameters.Add("RowFilter", OracleType.VarChar, 3000).Value = RowFilter;
        cmd.Parameters["RowFilter"].Direction = ParameterDirection.Input;

        cmd.Parameters.Add("SortStr", OracleType.VarChar, 3000).Value = SortStr;
        cmd.Parameters["SortStr"].Direction = ParameterDirection.Input;

        cmd.Parameters.Add("MinRowNum", OracleType.Number).Value = MinRowNum;
        cmd.Parameters["MinRowNum"].Direction = ParameterDirection.Input;

        cmd.Parameters.Add("MaxRowNum", OracleType.Number).Value = MaxRowNum;
        cmd.Parameters["MaxRowNum"].Direction = ParameterDirection.Input;

        cmd.Parameters.Add("TotalCount", OracleType.Number).Direction = ParameterDirection.Output;
        cmd.Parameters["TotalCount"].Value = 0;

        cmd.Parameters.Add("Cur_ReturnCur", OracleType.Cursor).Direction = ParameterDirection.Output;

        DataSet ds = new DataSet();
        using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
        {
            adapter.Fill(ds);
        }

        RecordCount = int.Parse(cmd.Parameters["TotalCount"].Value.ToString());
        return ds.Tables[0];
    }
}

關鍵優化提示

• 確保 SortStr 欄位有索引，避免全表排序。
• 大數據量場景建議改用 ROW_NUMBER() OVER() 分析函數（Oracle 10g+），效能優於 ROWNUM 多層巢狀。
• 參數化查詢已內建，防止 SQL 注入。
• 連線採 using 區塊自動釋放，避免連線洩漏。