Oracle 分頁存儲過程實戰與 .NET 呼叫完整範例

設計思路

Oracle 無法像 SQL Server 直接回傳結果集，需透過 REF CURSOR（游標變數）輸出。分頁邏輯採用經典 ROWNUM 雙層查詢：

1. 最內層：原始查詢 + ROWNUM rn。
2. 中間層：`WHERE rn <= :high`。
3. 最外層：`WHERE rn >= :low`。

Package 定義

CREATE OR REPLACE PACKAGE DotNet AS
    TYPE ref_cursor IS REF CURSOR;

    -- 分頁主程序
    PROCEDURE DotNetPagination(
        p_index   IN  NUMBER,      -- 頁碼（從 0 開始）
        p_sql     IN  VARCHAR2,    -- 含 ROWNUM 的基礎查詢
        p_size    IN  NUMBER,      -- 頁大小
        p_count   OUT NUMBER,      -- 總頁數
        v_cur     OUT ref_cursor   -- 當前頁資料游標
    );

    -- 獨立取得總筆數（供首次載入計算 VirtualItemCount）
    PROCEDURE DotNetPageRecordsCount(
        p_sqlcount IN VARCHAR2,
        p_rcount   OUT NUMBER
    );
END DotNet;

Package Body 實作

CREATE OR REPLACE PACKAGE BODY DotNet AS

PROCEDURE DotNetPagination(p_index IN NUMBER, p_sql IN VARCHAR2,
                           p_size IN NUMBER, p_count OUT NUMBER,
                           v_cur OUT ref_cursor) AS
    v_sql   VARCHAR2(1000);
    v_total NUMBER;
    v_low   NUMBER;
    v_high  NUMBER;
BEGIN
    -- 1. 計算總筆數與總頁數
    v_sql := 'SELECT COUNT(*) FROM (' || p_sql || ')';
    EXECUTE IMMEDIATE v_sql INTO v_total;
    p_count := CEIL(v_total / p_size);

    -- 2. 計算當前頁 ROWNUM 範圍
    v_high := (p_index + 1) * p_size;      -- 例：page 0, size 10 → high=10
    v_low  := v_high - p_size + 1;         -- low=1

    -- 3. 組裝分頁查詢並開啟游標
    v_sql := 'SELECT * FROM (' || p_sql || ') WHERE rn BETWEEN ' || v_low || ' AND ' || v_high;
    OPEN v_cur FOR v_sql;
END DotNetPagination;

PROCEDURE DotNetPageRecordsCount(p_sqlcount IN VARCHAR2, p_rcount OUT NUMBER) AS
    v_sql VARCHAR2(1000);
    v_cnt NUMBER;
BEGIN
    v_sql := 'SELECT COUNT(*) FROM (' || p_sqlcount || ')';
    EXECUTE IMMEDIATE v_sql INTO v_cnt;
    p_rcount := v_cnt;
END DotNetPageRecordsCount;

END DotNet;

.NET (VB.NET) 呼叫範例

Imports Oracle.DataAccess.Client
Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

    Private Sub gridbind(ByVal pindex As Integer, ByVal psql As String, Optional ByVal psize As Integer = 10)
        Using conn As New OracleConnection("Password=gzdlgis;User ID=gzdlgis;Data Source=gzgis"),
              cmd As New OracleCommand()

            cmd.Connection = conn
            cmd.CommandType = CommandType.StoredProcedure
            conn.Open()

            '--- 1. 取得總筆數，設定 DataGrid 虛擬項目數 ---
            cmd.CommandText = "DotNet.DotNetPageRecordsCount"
            cmd.Parameters.Clear()
            cmd.Parameters.Add("p_sqlcount", OracleDbType.Varchar2).Value = psql
            cmd.Parameters.Add("p_rcount", OracleDbType.Int32).Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()

            DataGrid1.AllowPaging = True
            DataGrid1.AllowCustomPaging = True
            DataGrid1.PageSize = psize
            DataGrid1.VirtualItemCount = CInt(cmd.Parameters("p_rcount").Value)

            '--- 2. 取得當前頁資料 ---
            cmd.CommandText = "DotNet.DotNetPagination"
            cmd.Parameters.Clear()
            cmd.Parameters.Add("p_index", OracleDbType.Int32).Value = pindex
            cmd.Parameters.Add("p_sql", OracleDbType.Varchar2).Value = psql
            cmd.Parameters.Add("p_size", OracleDbType.Int32).Value = psize
            cmd.Parameters.Add("p_count", OracleDbType.Int32).Direction = ParameterDirection.Output
            cmd.Parameters.Add("v_cur", OracleDbType.RefCursor).Direction = ParameterDirection.Output

            Using dr As OracleDataReader = cmd.ExecuteReader()
                DataGrid1.DataSource = dr
                DataGrid1.DataBind()
            End Using

            Response.Write("總頁數：" & cmd.Parameters("p_count").Value)
        End Using
    End Sub

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim psql As String = "SELECT ROWNUM rn, t.* FROM cd_ssxl t"
            gridbind(0, psql, 20)
        End If
    End Sub

    Protected Sub DataGrid1_PageIndexChanged(source As Object, e As DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
        Dim psql As String = "SELECT ROWNUM rn, t.* FROM cd_ssxl t"
        DataGrid1.CurrentPageIndex = e.NewPageIndex
        gridbind(e.NewPageIndex, psql, 20)
    End Sub
End Class

關鍵注意事項

• 基礎查詢必須包含 `ROWNUM rn`，否則外層 `WHERE rn BETWEEN ...` 無法運作。
• p_index 從 0 起算，對應 `DataGrid.CurrentPageIndex`。
• 使用 AllowCustomPaging=True + VirtualItemCount 實現「只取當前頁資料」的高效分頁。
• 參數方向：Output 對應 Oracle `OUT`，`RefCursor` 需指定 OracleDbType.RefCursor。

效能優化建議

1. 基礎查詢盡量只選需要欄位，避免 `SELECT *`。
2. 確保分頁欄位（如排序欄位）有索引支援。
3. 大數據量時考慮 ROW_NUMBER() OVER (ORDER BY ...) 寫法（Oracle 12c+），可避免雙層巢狀查詢。