c# oracle 分页

//////////////////////////////////////////oracle部分存储过程

create or replace package PACK_PAGE is

 

 TYPE type_cur IS REF CURSOR;     --定义游标变量用于返回记录集
  PROCEDURE PROC_PAGE
  (
  Pindex in number,                --分页索引
  Psql in varchar2,                --产生dataset的sql语句
  Psize in number,                 --页面大小
  Pcount out number,               --返回分页总数
  v_cur out type_cur               --返回当前页数据记录
  );
  procedure PROC_PAGECOUNT
  (
  Psqlcount in varchar2,           --产生dataset的sql语句
  Prcount   out number             --返回记录总数
  );

end PACK_PAGE;

///////////////////////////////////////////////////////

create or replace package body PACK_PAGE is

 

 PROCEDURE PROC_PAGE
  (
  Pindex in number,
  Psql in varchar2,
  Psize in number,
  Pcount out number,
  v_cur out type_cur
  )
  AS
  v_sql VARCHAR2(5000);
  v_tempSql varchar2(5000);
  v_count number;
  v_Plow number;
  v_Phei number;
  Begin
  ------------------------------------------------------------取分页总数
  v_sql := 'select count(*) from (' || Psql || ')';
  execute immediate v_sql into v_count;
  Pcount := ceil(v_count/Psize);
  ------------------------------------------------------------显示任意页内容
  v_Phei := (Pindex-1) * Psize + Psize;
  v_Plow := v_Phei - Psize + 1;
  v_tempSql := 'select rownum rn,t.* from ('|| Psql ||')t Where rownum <= ' || v_Phei ;            --要求必须包含rownum字段
  v_sql := 'select * from (' || v_tempSql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
  open v_cur for v_sql;
  End PROC_PAGE;
 --**************************************************************************************
  procedure PROC_PAGECOUNT
  (
  Psqlcount in varchar2,
  Prcount   out number
  )
  as
  v_sql varchar2(5000);
  v_prcount number;
  begin
  v_sql := 'select count(*) from (' || Psqlcount || ')';
  execute immediate v_sql into v_prcount;
  Prcount := v_prcount;                  --返回记录总数
  end PROC_PAGECOUNT;
 --**************************************************************************************
end PACK_PAGE;

 

////////////////////// c# 调用

   /// <summary>
        /// 分页条件查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页包含的记录数</param>
        /// <param name="tabName">填充数据集时的表名称</param>
        /// <returns></returns>
        public DataSet QueryByPage(string sql, int pageIndex, int pageSize, string tabName)
        {
            using (OracleHelper orahp = new OracleHelper())
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(new DataTable(tabName));

 

                orahp.Open();
                orahp.ExecuteSP_Page(ds.Tables[tabName], "pack_page.proc_page", sql, pageIndex, pageSize, true);
                orahp.Close();

                return ds;
            }
        }

 

 

　　 public int ExecuteSP_Page(DataTable outDataTable, string procName, string sql, int pageIndex, int pageSize, bool setCase)
        {
            if (null == connection)
            {
                throw new ObjectDisposedException(GetType().FullName);
            }

 

            int nReturn = 0;

            using (OracleCommand command = new OracleCommand(procName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = this._timeout;

                command.Parameters.Add("psql", OracleType.VarChar).Value = sql;
                command.Parameters.Add("pindex", OracleType.Number).Value = pageIndex;
                command.Parameters.Add("psize", OracleType.Number).Value = pageSize;
                command.Parameters.Add("pcount", OracleType.Number).Direction = ParameterDirection.Output;
                command.Parameters.Add("v_cur", OracleType.Cursor).Direction = ParameterDirection.Output;

                if (null != transaction)
                {
                    command.Transaction = transaction;
                }

                using (OracleDataAdapter oracAdapter = new OracleDataAdapter(command))
                {
                    if (setCase) outDataTable.CaseSensitive = true;
                    nReturn = oracAdapter.Fill(outDataTable);
                }

                command.Parameters.Clear();

                return nReturn;
            }
        }