整理的oracle数据库分页存储过程及示例

1、oracle数据库分页存储过程

CREATE OR REPLACE  PACKAGE DotNet  is

  TYPE type_cur IS REF CURSOR;     --定义游标变量用于返回记录集
  PROCEDURE DotNetPagination
  (
  Pindex in number,                --分页索引
  Psql in varchar2,                --产生dataset的sql语句
  Psize in number,                 --页面大小
  Pcount out number,               --返回分页总数
  v_cur out type_cur               --返回当前页数据记录
  );
  procedure DotNetPageRecordsCount
  (
  Psqlcount in varchar2,           --产生dataset的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
  ------------------------------------------------------------取分页总数
  v_sql := 'select count(*) from (' || Psql || ')';
  execute immediate v_sql into v_count;
  Pcount := ceil(v_count/Psize);
  ------------------------------------------------------------显示任意页内容
  v_Phei := Pindex * Psize + Psize;
  v_Plow := v_Phei - Psize + 1;
  --Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段
  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_prcount number;
  begin
  v_sql := 'select count(*) from (' || Psqlcount || ')';
  execute immediate v_sql into v_prcount;
  Prcount := v_prcount;                  --返回记录总数
  end DotNetPageRecordsCount;
 --**************************************************************************************
end DotNet;

 

2、使用示例

       /// <summary>
       /// 填充dataSet数据集－Oracle库
       /// </summary>
       /// <param name="pindex">当前页</param>
       /// <param name="psql">执行查询的SQL语句</param>
       /// <param name="psize">每页显示的记录数</param>
       /// <returns></returns>
      private bool gridbind(int pindex, string psql, int psize)
     {
            OracleConnection conn = new OracleConnection();
            OracleCommand cmd = new OracleCommand();
            OracleDataAdapter dr = new OracleDataAdapter();
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();
            cmd.CommandText = "DotNet.DotNetPageRecordsCount";
            cmd.Parameters.Add("psqlcount", OracleType.VarChar).Value = psql;
            cmd.Parameters.Add("prcount", OracleType.Number).Direction = ParameterDirection.Output;
            
            cmd.ExecuteNonQuery();
            string PCount = cmd.Parameters["prcount"].Value.ToString();
            cmd.Parameters.Clear();
            cmd.CommandText = "DotNet.DotNetPagination";
            if (pindex != 0)
            {
                cmd.Parameters.Add("pindex", OracleType.Number).Value = pindex - 1;
            }
            else
            {
                cmd.Parameters.Add("pindex", OracleType.Number).Value = pindex;
            }
            cmd.Parameters.Add("psql", OracleType.VarChar).Value = psql;
            cmd.Parameters.Add("psize", OracleType.Number).Value = psize;
            cmd.Parameters.Add("v_cur", OracleType.Cursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("pcount", OracleType.Number).Direction = ParameterDirection.Output;
            dr.SelectCommand = cmd;
            try
            {
                ds = new DataSet();
                dr.Fill(ds);
                //显示页码条的状态
                showStatus(Convert.ToInt32(cmd.Parameters["pindex"].Value) + 1,
                    Convert.ToInt32(cmd.Parameters["pcount"].Value),
                    Convert.ToInt32(PCount));
                for (int i = 0; i < ds.Tables.Count; i++)
                { //把数据行为零的表删除
                    if (ds.Tables[i].Rows.Count == 0)
                        ds.Tables.Remove(ds.Tables[i].TableName);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
 
            conn.Close();
            return true;
      }