Sybase分页语句存储过程实现(二)
来源:互联网 发布:unity3d 骨骼动画 编辑:程序博客网 时间:2024/06/05 08:34
create procedure test_p @ipage int, @num int as begin declare @maxpages int, @rcount int if @ipage>=100 select @maxpages=ceiling(count(*)/@num) from test else select @maxpages=100000 if @ipage<=@maxpages/2 begin select @rcount=@ipage*@num set rowcount @rcount select id=identity(12),name,descs,ddd into #temptable1 from test order by id select * from #temptable1 where id>=(@ipage-1)*@num and id<= @ipage*@num end else begin select @rcount=(@maxpages-@ipage+1)*@num set rowcount @rcount select id=identity(12),name,descs,ddd into #temptable2 from test order by id desc select id,name, ddd,descs from #temptable2 where id>=(@maxpages-@ipage)*@num and id<= (@maxpages-@ipage+1)*@num order by id desc end end
create procedure splitpage @qry varchar(16384),@ipage int, @num int as begin declare @maxpages int declare @rcount int declare @execsql varchar(16384) if @ipage>=100 select @maxpages=ceiling(count(*)/@num) from test else select @maxpages=100000 if @ipage<=@maxpages/2 begin select @rcount=@ipage*@num set rowcount @rcount set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),') set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from') set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num) execute (@execsql) end else begin select @rcount=(@maxpages-@ipage+1)*@num set rowcount @rcount set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),') set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from') set @execsql = @execsql || ' order by sybid desc' set @execsql = @execsql || ' select * from #temptable1 where sybid > ' || convert(varchar,(@maxpages-@ipage)*@num) || ' and sybid <= ' || convert(varchar,(@maxpages-@ipage+1)*@num) execute (@execsql) end end
>select id=identity(12),name,descs,ddd into #temptable1 from test order by id >select * from #temptable1 where id>=(@ipage-1)*@num and id<= @ipage*@num 我在项目中也是使用这个模式的,问题是写临时表时是否会发生物理写操作?如果发生,还有没有不需要写临时表的方案? vc00, sybase中的临时表当用select into #table的形式时,是不记录日志的。速度非常快。 这点和oracle不同。算是sybase的一个feature. ncowboy ,老程序在处理10万页以上结果集会出现问题。用select @maxpages=ceiling(count(*)/@num) from test此处使用是不对的。 由于即席查询时获取maxpages效率不高,对此作以下修改:可以由用户指定最大查询页数,缺省定为5000 create procedure splitpage @qry varchar(16384),@ipage int, @num int,@maxpages int = 5000 as begin declare @rcount int declare @execsql varchar(16384) if @ipage > @maxpages begin select '输入页数[' || convert(varchar,@ipage) || ']大于最大查询页数[' || convert (varchar,@maxpages) ||']' return end select @rcount=@ipage*@num set rowcount @rcount set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),') set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from') set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num) execute (@execsql) end 由于无法精确且高效取出即席查询结果集的总页数,原程序中考虑后几页查询的优化算法就无法使用了。 如果可以先算出精确的总页数,然后作为参数传给splitpage,那么也可以把查询后几页的算法再补上。 谁有更好的办法也可以提出来大家一起把这个分页存储过程优化到底。 版本更新:1.0.2 注意程序最后要加上set rowcount 0 create procedure splitpage @qry varchar(16384),@ipage int, @num int,@maxpages int = 5000 as begin declare @rcount int declare @execsql varchar(16384) if @ipage > @maxpages begin select '输入页数[' || convert(varchar,@ipage) || ']大于最大查询页数[' || convert (varchar,@maxpages) ||']' return end select @rcount=@ipage*@num set rowcount @rcount set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),') set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from') set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num) execute (@execsql) set rowcount 0 end
Reference:
http://www.08.la/news2010/newshtml/JiShuWenZhang/SYBASE/9648.html
0 0
- Sybase分页语句存储过程实现(二)
- 存储过程实现sybase分页
- sybase分页存储过程的实现
- Sybase分页存储过程
- sybase 存储过程分页(转)
- Sybase分页显示存储过程
- 调用存储过程实现分页(二)
- sql存储过程分页(sql语句)
- Oracle 分页语句,存储过程
- 存储过程分页Sql语句
- 通用ORACLE 分页查询语句,存储过程实现
- 存储过程实现分页
- 存储过程实现分页
- 存储过程实现分页
- 存储过程实现分页
- Sybase 分页查询语句
- 存储过程 实现分页功能(2)
- C# DataGridView分页功能(SQL分页存储过程实现)
- MyEclipse8.5插件FindBugs的安装
- 14.2.2泛化的java引用
- Python特殊语法:filter、map、reduce、lambda
- Spring AOP 理解
- UVa 10282 & POJ 2503 - Babelfish
- Sybase分页语句存储过程实现(二)
- 【Android】xml文件里面出现unbound prefix的问题
- struct clk clk-get() clk_enable();
- 从一个字符串中找出第一个不重复字符
- Android Bundle类
- iOS开发- UICollectionView详解+实例
- java I/O中文件字节输入流 和字符输入流
- Ubuntu下wxWidgets学生信息管理sqlite3
- Sqlce在DataGridView分页显示