注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

jiwenxu的博客

CODE

 
 
 

日志

 
 

oracle数据库分页存储过程  

2007-07-23 17:36:14|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 
1、oracle数据库分页存储过程
CREATE OR REPLACE  PACKAGEDotNet  is
  TYPE type_cur IS REFCURSOR;    --定义游标变量用于返回记录集
  PROCEDURE DotNetPagination
  (
  Pindex innumber,               --分页索引
  Psql invarchar2,               --产生dataset的sql语句
  Psize innumber,                --页面大小
  Pcount outnumber,              --返回分页总数
  v_cur outtype_cur              --返回当前页数据记录
  );
  procedure DotNetPageRecordsCount
  (
  Psqlcount invarchar2,          --产生dataset的sql语句
  Prcount   outnumber            --返回记录总数
  );
end DotNet;
CREATE OR REPLACE  PACKAGE BODYDotNet"  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 || ') wherern between ' || v_Plow || ' and ' || v_Phei ;
  open v_cur for v_sql;
  End DotNetPagination;
 --**************************************************************************************
  procedure DotNetPageRecordsCount
  (
  Psqlcount in varchar2,
  Prcount   outnumber
  )
  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;
     }
  评论这张
 
阅读(40)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018