SQL存储过程[2] - 分页（SQL Server 、MySQL）

SQL语句 Server 分页存储过程（SQL Server 、MySQL）

1、SQL Server 分页存储过程

1.1 游标分页

--sql分页存储过程
--@sqlstr  查询语句
--@currentpage 当前页码
--@pagesize每页信息数
--返回值
---1、记录数
---2、符合条件的记录集
CREATE procedure PagingQuery
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int, --每页行数
@allrecords int OUTPUT  --返回的总记录数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @allrecords=@rowcount
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
GO

2.2 以下支持 SQL SERVER2000 以上版本的分页存储过程

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'PageShowOne' AND type = 'P')   -----分页存储过程开始创建
DROP PROCEDURE PageShowOne
GO
CREATE Proc PageShowOne
@PageSize int=10 ,--每页显示的记录数
@PageCurrent int=1 ,--当前要显示的页号
@FdName varchar(100)='' ,--主键名或者标识列名
@SelectStr varchar(2000)='', --select子句，不包含select关键字,如：*或者Id,UserId,UserName等。
@FromStr varchar(1000)='', --from子句，不包含from关键子，如：myTable或者myTable,yourTable
@WhereStr varchar(2000)='', --Where子句，不包含where关键字，如空的，或者 id>2 等
@OrderByStr varchar(1000)='',--order by 子句，不包含order by 子句 ，如id desc,UserId asc 等
@CountRows int=0 output, --返回记录总数
@CountPage int=0 output --返回总页数
as
--------定义局部变量---------
declare @Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(1000) --order by 子句
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
declare @TmpStr varchar(2000) --临时
----------------------------
if @OrderByStr <> ''
set @OrderBySqls = ' order by '+@OrderByStr
else
set @OrderBySqls = ''
--------
if @WhereStr <> ''
set @WhereSqls = ' where ('+@WhereStr+')'
else
set @WhereSqls = ''
--------
set @TmpStr = @WhereSqls
--如果显示第一页，可以直接用top来完成
if @PageCurrent<=1 
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+' '+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls)
goto LabelRes
end
---------------------------
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
----------
if @WhereSqls <> ''
set @WhereSqls = @WhereSqls + ' and (' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+'))'
else
set @WhereSqls = ' where ' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
----------
set @Sqls = 'select top '+@Id1+ ' '+ @SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls
exec (@Sqls)
-----------
LabelRes:
-----返回总记录数
set @Sqls = 'select @a=count(1) from '+@FromStr+@TmpStr
exec sp_executesql @sqls,N'@a int output',@CountRows output 
-----返回总页数
if @CountRows <= @PageSize
set @CountPage = 1
else
begin
set @CountPage = @CountRows/@PageSize
if (@CountRows%@PageSize) > 0
set @CountPage = @CountPage + 1
end
return
GO

 2.3 以下分页过程，仅支持 SQL 2005 以上版本(含)

create procedure [dbo].[sp_super_page]
@TableName varchar(5000), --要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id)
@Fields varchar(5000), --表中的字段，可以使用*代替
@OrderField varchar(5000), --要排序的字段
@sqlWhere varchar(5000), --WHERE子句
@pageSize int,--分页的大小
@pageIndex int,--要显示的页的索引
@TotalPage int output, --页的总数
@TotalRecords int output--信息总条数
as
begin
Begin Tran	--开始事务
Declare @sql nvarchar(4000);
Declare @totalRecord int; --记录总数
if (@sqlWhere IS NULL or @sqlWhere = '')
--在没有WHERE子句的情况下得到表中所有的记录总数
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
--利用WHERE子句进行过滤
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
--执行sql语句得到记录总数
EXEC sp_executesql@sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
--根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序
if (@sqlWhere IS NULL or @sqlWhere = '')
set @sql = 'select * from (select ROW_NUMBER() over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'select * from (select ROW_NUMBER() over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--确保当前页的索引在合理的范围之内
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage
--得到当前页在整个结果集中准确的ROW_NUMBER值
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord =(@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--输出当前页中的数据
set @Sql = @Sql + ') as t' + ' where rowId between '+ Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
Exec(@Sql)
If @@Error <> 0
Begin
RollBack Tran		--回滚事务
SET @TotalRecords=-1
End
Else
Begin
Commit Tran		--提交事务
SET @TotalRecords=@totalRecord
End
end

　　

2、MySQL 通用分页存储过程

CREATE PROC sp_PageView
@sql         ntext,     --要执行的sql语句
@PageCurrent int=1,     --要显示的页码
@PageSize    int=10,    --每页的大小
@PageCount   int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
    @cursor=@p1 OUTPUT,
    @stmt=@sql,
    @scrollopt=1,
    @ccopt=1,
    @rowcount=@PageCount OUTPUT
 
--计算总页数
IF ISNULL(@PageSize,0)<1
    SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
    SET @PageCurrent=1
ELSE
    SET @PageCurrent=(@PageCurrent-1)*@PageSize+1
 
--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize
 
--关闭分页游标
EXEC sp_cursorclose @p1

declare @I int
execute SP_PageVIew 'SELECT * FROM Item',1,10,@I output
select @I
--此分页过程返回三个数据集。
--可通过:  UniQuery1.OpenNext;  分别取出自己要的数据。

 

 

创建时间：2020.09.14　　更新时间：2020.09.16