–创建
create procedure FenYe
@sqlstr nvarchar(4000), –查询字符串
@currentpage int, –第N页
@pagesize int –每页行数
as
set nocount on
declare @P1 int, –P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数–,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
–测试它
declare @pagecount int,–总页数
@recordcount int –记录数
exec FenYe ‘select * from business.dbo.tb_cocacola_webflux’,1,20,@pagecount out,@recordcount out
select @pagecount as pagecount,@recordcount as recordcount
declare @p1 int,
@rowcount int
exec sp_cursoropen @p1 output,’select * from business.dbo.tb_cocacola_webflux’,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select @p1,@rowcount
exec sp_cursorfetch @p1,16,1,20
exec sp_cursorclose @p1
这是个很经典的存储过程分页,如果用在JAVA中则不用创建它,直接用con.prepareCall(“{?=call sp_cursoropen(?, ?, ?, ?, ?)}”)和con.prepareCall(“{?=call sp_cursorfetch(?, ?, ?, ?)}”)就可以了,网上看到很多人在问,这里做下说明
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/100635.html原文链接：https://javaforall.net
