Oracle 通用分頁存儲過程 + ASP.NET AspNetPager 控制項整合實戰

Oracle 無原生 LIMIT/OFFSET，傳統分頁依賴 ROWNUM 雙層嵌套。本文提供封裝良好的 PL/SQL 分頁套件，並示範 ASP.NET AspNetPager 控制項無縫整合，實現高效伺服器端分頁。

PL/SQL 分頁套件

套件規格

CREATE OR REPLACE PACKAGE JT_P_page IS
  TYPE type_cur IS REF CURSOR;  -- 回傳游標型別
  PROCEDURE Pagination (
    Pindex    IN  NUMBER,      -- 頁索引（從 0 開始）
    Psql      IN  VARCHAR2,    -- 查詢 SQL（不含 ORDER BY）
    Psize     IN  NUMBER,      -- 每頁筆數
    Pcount    OUT NUMBER,      -- 總頁數
    Prowcount OUT NUMBER,      -- 總筆數
    v_cur     OUT type_cur     -- 分頁結果游標
  );
END JT_P_page;

套件主體

CREATE OR REPLACE PACKAGE BODY JT_P_page IS
  PROCEDURE Pagination (Pindex IN NUMBER, Psql IN VARCHAR2, Psize IN NUMBER,
                        Pcount OUT NUMBER, Prowcount OUT NUMBER, v_cur OUT type_cur) AS
    v_sql     VARCHAR2(32767);
    v_pbegin  NUMBER;
    v_pend    NUMBER;
  BEGIN
    -- 1. 計算總筆數
    v_sql := 'SELECT COUNT(*) FROM (' || Psql || ')';
    EXECUTE IMMEDIATE v_sql INTO Prowcount;

    -- 2. 計算總頁數
    Pcount := CEIL(Prowcount / Psize);

    -- 3. 計算當頁 ROWNUM 範圍
    v_pend   := (Pindex + 1) * Psize;
    v_pbegin := v_pend - Psize + 1;

    -- 4. 動態產生分頁查詢
    v_sql := 'SELECT * FROM (SELECT a.*, ROWNUM rn FROM (' || Psql || ') a) WHERE rn BETWEEN ' || v_pbegin || ' AND ' || v_pend;

    DBMS_OUTPUT.PUT_LINE(v_sql);  -- 除錯用
    OPEN v_cur FOR v_sql;
  END Pagination;
END JT_P_page;

ASP.NET 後端整合

資料綁定方法

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack) BindGridView(0, "DD_Demo", AspNetPager1.PageSize);
}

private void BindGridView(int index, string sql, int pageSize)
{
    int totalCount = 0, pageCount = 0;
    DataTable dt = ReturnDataTable(index, sql, pageSize, out totalCount, out pageCount);
    GridView1.DataSource = dt;
    GridView1.DataBind();

    AspNetPager1.RecordCount = totalCount;
    AspNetPager1.PageSize = pageSize;
    AspNetPager1.CustomInfoHTML = $" 共{totalCount}條記錄" +
                                  $" 當前第{AspNetPager1.CurrentPageIndex}頁" +
                                  $"/共{pageCount}頁";
}

public static DataTable ReturnDataTable(int index, string sql, int pageSize,
                                        out int totalCount, out int pageCount)
{
    var dt = new DataTable();
    try
    {
        var param = new OracleParameter[]
        {
            new OracleParameter("Pindex", OracleType.Number) { Value = index, Direction = ParameterDirection.Input },
            new OracleParameter("Psql", OracleType.VarChar) { Value = sql, Direction = ParameterDirection.Input },
            new OracleParameter("Psize", OracleType.Number) { Value = pageSize, Direction = ParameterDirection.Input },
            new OracleParameter("Pcount", OracleType.Number) { Direction = ParameterDirection.Output },
            new OracleParameter("Prowcount", OracleType.Number) { Direction = ParameterDirection.Output },
            new OracleParameter("v_cur", OracleType.Cursor) { Direction = ParameterDirection.Output }
        };

        // 假設 DbHelperOra.RunProcedure 封裝了執行存儲過程並回傳 DataSet
        dt = Maticsoft.DBUtility.DbHelperOra.RunProcedure("JT_P_page.Pagination", param, "queryTable").Tables["queryTable"];

        pageCount = int.Parse(param[3].Value.ToString());
        totalCount = int.Parse(param[4].Value.ToString());
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    return dt;
}

protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
    BindGridView(AspNetPager1.CurrentPageIndex - 1, "DD_Demo", AspNetPager1.PageSize);
}

關鍵設計要點

• REF CURSOR 回傳：避免將大量資料集複製到 PL/SQL 陣列再傳輸，直接串流給客戶端，記憶體效率高。
• 動態 SQL + EXECUTE IMMEDIATE：支援任意查詢 SQL（含 JOIN、WHERE、ORDER BY），通用性強。
• ROWNUM BETWEEN：Oracle 12c 前標準分頁寫法；12c+ 可改用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY 簡化邏輯。
• 參數方向：IN 輸入、OUT 輸出、IN OUT 雙向；游標必須 OUT。

效能優化建議

• 查詢 SQL 應包含 ORDER BY 確保分頁順序穩定。
• 大表分頁確保 WHERE 條件欄位有索引，避免 Full Table Scan 計算 COUNT(*)。可考慮維護摘要表或使用近似計數。
• 若分頁查詢固定，建議將 Psql 固化在套件內部，僅傳遞參數值，防止 SQL 注入。