Oracle 分頁存儲過程實作與 .NET (VB.NET) 調用完整範例

設計思路

• Oracle 無法像 SQL Server 直接返回結果集，**必須使用 REF CURSOR (游標變數)** 作為 OUT 參數回傳。
• 複雜 SQL 由 .NET 端組裝，存儲過程僅負責分頁邏輯與總筆數統計。
• 採用 ROWNUM 雙層嵌套查詢實現分頁 (經典高效寫法)。

資料庫端：Package 規範與實作

CREATE OR REPLACE PACKAGE DotNet IS
  TYPE type_cur IS REF CURSOR;  -- 定義游標類型

  -- 分頁主程序
  PROCEDURE DotNetPagination(
    Pindex  IN  NUMBER,      -- 頁索引 (從 0 開始)
    Psql    IN  VARCHAR2,    -- 含 rownum rn 的查詢 SQL
    Psize   IN  NUMBER,      -- 頁大小
    Pcount  OUT NUMBER,      -- 總頁數
    v_cur   OUT type_cur     -- 當前頁資料游標
  );

  -- 統計總筆數 (可選，若前端已知總筆數可省略)
  PROCEDURE DotNetPageRecordsCount(
    Psqlcount IN  VARCHAR2,  -- 統計 SQL
    Prcount   OUT NUMBER     -- 總筆數
  );
END DotNet;
/

CREATE OR REPLACE PACKAGE BODY DotNet IS
  PROCEDURE DotNetPagination(Pindex IN NUMBER, Psql IN VARCHAR2, Psize IN NUMBER, Pcount OUT NUMBER, v_cur OUT type_cur) AS
    v_sql      VARCHAR2(1000);
    v_count    NUMBER;
    v_Plow     NUMBER;
    v_Phei     NUMBER;
  BEGIN
    -- 1. 計算總筆數 -> 總頁數
    v_sql := 'SELECT COUNT(*) FROM (' || Psql || ')';
    EXECUTE IMMEDIATE v_sql INTO v_count;
    Pcount := CEIL(v_count / Psize);

    -- 2. 計算當前頁 ROWNUM 範圍
    v_Phei := (Pindex + 1) * Psize;       -- 結束行號
    v_Plow := v_Phei - Psize + 1;         -- 起始行號

    -- 3. 取當前頁資料 (要求 Psql 內層已產生 rn 欄位)
    v_sql := 'SELECT * FROM (' || Psql || ') WHERE rn BETWEEN ' || v_Plow || ' AND ' || v_Phei;
    OPEN v_cur FOR v_sql;
  END DotNetPagination;

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

.NET 端調用 (VB.NET + OracleClient)

Imports System.Data
Imports OracleClient = Oracle.DataAccess.Client  ' ODP.NET 命名空間

Partial Class PagingDemo
    Inherits System.Web.UI.Page

    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Private connStr As String = "Password=gzdlgis;User ID=gzdlgis;Data Source=gzgis"

    Private Sub GridBind(ByVal pindex As Integer, ByVal psql As String, Optional ByVal psize As Integer = 10)
        Using conn As New OracleClient.OracleConnection(connStr)
            Using cmd As New OracleClient.OracleCommand()
                cmd.Connection = conn
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()

                '--- 1. 取得總筆數，設定 DataGrid 虛擬分頁屬性 ---
                cmd.CommandText = "DotNet.DotNetPageRecordsCount"
                cmd.Parameters.Clear()
                cmd.Parameters.Add("psqlcount", OracleClient.OracleType.VarChar).Value = psql
                cmd.Parameters.Add("prcount", OracleClient.OracleType.Number).Direction = ParameterDirection.Output
                cmd.ExecuteNonQuery()

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

                '--- 2. 取得當前頁游標綁定 DataGrid ---
                cmd.CommandText = "DotNet.DotNetPagination"
                cmd.Parameters.Clear()
                cmd.Parameters.Add("pindex", OracleClient.OracleType.Number).Value = pindex
                cmd.Parameters.Add("psql", OracleClient.OracleType.VarChar).Value = psql  ' 例: "SELECT ROWNUM rn, t.* FROM cd_ssxl t"
                cmd.Parameters.Add("psize", OracleClient.OracleType.Number).Value = psize
                cmd.Parameters.Add("v_cur", OracleClient.OracleType.Cursor).Direction = ParameterDirection.Output
                cmd.Parameters.Add("pcount", OracleClient.OracleType.Number).Direction = ParameterDirection.Output

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

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

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

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

關鍵點提示

1. 傳入的 Psql **必須包含 ROWNUM rn** (如 SELECT ROWNUM rn, t.* FROM table t)，否則外層 WHERE rn BETWEEN ... 會失效。
2. DataGrid 設定 AllowCustomPaging=True + VirtualItemCount 實現虛擬分頁，避免一次性載入全量資料。
3. 使用 OracleType.Cursor 對應 REF CURSOR，ExecuteReader 直接繫結 DataReader。