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

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

1、SQL Server 分页存储过程

1.1 游标分页

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

--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 以上版本的分页存储过程

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

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 以上版本(含)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

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 通用分页存储过程

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

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

?

1 2 3 4 5

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

 

 

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