c# oracle 預存程序分頁查詢

簡單貼出主要部分代碼。

.sql

--定義包
create or replace package PKG_G_SelectSp as type p_G_Cursor is ref cursor;
procedure P_G_GetPagingData(
  q_Sql varchar2,
  rowCountPage number,
  indexNowPage number,
  totalRows out number,
  totalPages out number,
  p_corsor out PKG_G_SelectSp.p_G_Cursor
  );
end PKG_G_SelectSp;

--分頁查詢資料存放區過程
create or replace procedure P_G_GetPagingData(
  q_Sql varchar2,
  rowCountPage number, --每頁面記錄條數
  indexNowPage number, --當前頁碼
  totalRows out number, --總記錄數
  totalPages out number, --總頁數
  p_corsor out PKG_G_SelectSp.p_G_Cursor --遊標,用於返回結果集
  ) as
  --分頁起始記錄編號
  startRowNum number:=(indexNowPage-1)*rowCountPage+1;
  --分頁結束記錄編號
  endRowNum number:=indexNowPage*rowCountPage;
  --查詢資料sql字串
  s_sql varchar2(5000);
  sc_sql varchar2(5000);
 
  begin
  s_sql:='select * from (select t1.*,rownum rn from ('||q_Sql||') t1 where rownum <= '||endRowNum||' )where rn >='||startRowNum;
  --開啟遊標,關聯Sql語句
  open p_corsor for s_sql;
 
  --查詢記錄總數
  sc_sql:='select count(*) from('||q_Sql|| ')';
  execute immediate sc_sql into totalRows;
  --計算總頁數
  if mod(totalRows,rowCountPage)=0 then
     totalPages:=totalRows/rowCountPage;
  else
     totalPages:=totalRows/rowCountPage+1;
  end if;
end;

.aspx

public OracleConnection oConnection;
public OracleCommand oCommand;

public OracleDataReader oDataReader;

public int totalRows = 0;

public int totalPages = 0;

//串連資料庫部分省略....

//定義調用預存程序方法

 /// <summary>
  /// 調用預存程序查詢指定頁碼記錄條數的資料
  /// </summary>
  /// <param name="q_Sql">基本的資料查詢</param>
  /// <param name="rowsCountPage">每頁顯示的記錄數</param>
  /// <param name="indexNowPage">頁碼</param>
  /// <param name="totalRows">查詢總記錄數</param>
  /// <param name="totalPages">總頁數</param>
  public void procedureSelectData(string q_Sql, int rowsCountPage, int indexNowPage, ref int totalRows, ref int totalPages) {
    try {
      this.oCommand = new OracleCommand();
      this.oCommand.Connection = this.oConnection;
       
      oCommand.CommandText = "P_G_GetPagingData";
      oCommand.CommandType = CommandType.StoredProcedure;

      OracleParameter op0 = new OracleParameter("q_Sql", OracleDbType.Varchar2, 2000);
      OracleParameter op1 = new OracleParameter("rowCountPage", OracleDbType.Int32, 10);
      OracleParameter op2 = new OracleParameter("indexNowPage", OracleDbType.Int32, 10);
      OracleParameter op3 = new OracleParameter("totalRows", OracleDbType.Int32, 10);
      OracleParameter op4 = new OracleParameter("totalPages", OracleDbType.Int32, 10);
      OracleParameter op5 = new OracleParameter("p_corsor", OracleDbType.RefCursor);

      op0.Direction = ParameterDirection.Input;
      op1.Direction = ParameterDirection.Input;
      op2.Direction = ParameterDirection.Input;
      op3.Direction = ParameterDirection.Output;
      op4.Direction = ParameterDirection.Output;
      op5.Direction = ParameterDirection.Output;

      op0.Value = q_Sql;
      op1.Value = rowsCountPage;
      op2.Value = indexNowPage;

      oCommand.Parameters.Add(op0);
      oCommand.Parameters.Add(op1);
      oCommand.Parameters.Add(op2);
      oCommand.Parameters.Add(op3);
      oCommand.Parameters.Add(op4);
      oCommand.Parameters.Add(op5);

      this.oDataReader = oCommand.ExecuteReader();
      totalRows = Int32.Parse(op3.Value.ToString());
      totalPages = int.Parse(op4.Value.ToString());
    } catch(Exception) {
    }
  }

//調用方法

procedureSelectData(sqlStr, 200, 1, ref this.totalRows, ref this.totalPages);

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/lifeng_beijing/archive/2009/12/22/5054261.aspx