您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 存儲過程實現分頁查詢：PL/SQL 與 C# 完整範例

編輯：Oracle 教學

Oracle 存儲過程實現分頁查詢：PL/SQL 與 C# 完整範例

分頁是應用系統常見需求。本文示範如何用 Oracle 存儲過程封裝分頁邏輯，並提供 C# 呼叫完整代碼，支援動態表名、欄位、條件、排序與總筆數統計。

資料庫端：建立套件與存儲過程

-- 1. 建立 REF CURSOR 類型套件
create or replace package testpackage as
  type test_cursor is ref cursor;
end testpackage;
/

-- 2. 建立分頁存儲過程
create or replace procedure fenye (
  tableName   in varchar2,    -- 表名
  fields      in varchar2,    -- 查詢欄位
  wherecase   in varchar2,    -- WHERE 條件
  pageSize    in number,      -- 每頁筆數
  pageNow     in number,      -- 當前頁碼
  orderField  in varchar2,    -- 排序欄位
  orderFlag   in number,      -- 排序方向 0:ASC 1:DESC
  myrows      out number,     -- 總筆數
  myPageCount out number,     -- 總頁數
  p_cursor    out testpackage.test_cursor -- 結果游標
) is
  v_sql       varchar2(1000);
  v_begin     number := (pageNow-1)*pageSize + 1;
  v_end       number := pageNow*pageSize;
  v_orderSql  varchar2(100) := '';
  v_wherecase varchar2(100) := '';
begin
  -- 動態組裝 ORDER BY
  if orderField is not null then
    if orderFlag = 0 then
      v_orderSql := ' order by ' || orderField;
    elsif orderFlag = 1 then
      v_orderSql := ' order by ' || orderField || ' desc';
    end if;
  end if;
  
  -- 動態組裝 WHERE
  if wherecase is not null then
    v_wherecase := ' where ' || wherecase;
  end if;
  
  -- 分頁核心 SQL：雙層 ROWNUM 過濾
  v_sql := 'select * from (select t1.*, rownum rn from (select ' || fields || 
           ' from ' || tableName || v_wherecase || ' ' || v_orderSql || ') t1 where rownum <= ' || v_end || 
           ') where rn >= ' || v_begin;
  
  open p_cursor for v_sql;
  
  -- 統計總筆數
  v_sql := 'select count(*) from ' || tableName || v_wherecase;
  execute immediate v_sql into myrows;
  
  -- 計算總頁數
  if mod(myrows, pageSize) = 0 then
    myPageCount := myrows / pageSize;
  else
    myPageCount := myrows / pageSize + 1;
  end if;
end;
/

C# 端：呼叫存儲過程封裝類

using System;
using System.Data;
using System.Data.OracleClient;

public class Paging {
  private string _connectionString, _tableName, _fields = "*", _whercase = "";
  private int _pageSize = 10, _pageNow = 1, _orderFlag = 0;
  private string _orderField = "";
  private int _myRows, _myPageCount;
  private DataTable _result;

  // 屬性省略...

  public void fenye() {
    using (OracleConnection conn = new OracleConnection(_connectionString)) {
      conn.Open();
      using (OracleCommand cmd = new OracleCommand("fenye", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;
        
        // 參數陣列對應存儲過程參數順序
        OracleParameter[] parameters = new OracleParameter[10];
        parameters[0] = new OracleParameter("tableName", OracleType.VarChar) { Value = _tableName };
        parameters[1] = new OracleParameter("fields", OracleType.VarChar) { Value = _fields };
        parameters[2] = new OracleParameter("wherecase", OracleType.VarChar) { Value = _whercase };
        parameters[3] = new OracleParameter("pageSize", OracleType.Int32) { Value = _pageSize };
        parameters[4] = new OracleParameter("pageNow", OracleType.Int32) { Value = _pageNow };
        parameters[5] = new OracleParameter("orderField", OracleType.VarChar) { Value = _orderField };
        parameters[6] = new OracleParameter("orderFlag", OracleType.Int32) { Value = _orderFlag };
        parameters[7] = new OracleParameter("myrows", OracleType.Int32) { Direction = ParameterDirection.Output };
        parameters[8] = new OracleParameter("myPageCount", OracleType.Int32) { Direction = ParameterDirection.Output };
        parameters[9] = new OracleParameter("p_cursor", OracleType.Cursor) { Direction = ParameterDirection.Output };
        
        foreach (var p in parameters) cmd.Parameters.Add(p);
        
        using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) {
          DataSet ds = new DataSet();
          oda.Fill(ds);
          _result = ds.Tables[0];
        }
        _myRows = Convert.ToInt32(parameters[7].Value);
        _myPageCount = Convert.ToInt32(parameters[8].Value);
      }
    }
  }
}

使用範例

string connStr = "Data Source=server1;User ID=s227;Password=123;Unicode=True";
Paging pager = new Paging();
pager.ConnectionString = connStr;
pager.TableName = "SPJ";
pager.PageSize = 10;
pager.PageNow = 1;

pager.fenye(); // 執行第 1 頁
foreach (DataRow row in pager.Result.Rows) {
  foreach (var col in row.ItemArray) Console.Write(col + " ");
  Console.WriteLine();
}

pager.PageNow = 2;
pager.fenye(); // 執行第 2 頁
// 輸出結果...