简单贴出主要部分代码。

.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