MSSql中实现翻页的5个存储过程

最新推荐文章于 2021-03-25 19:21:03 发布

微澜-

最新推荐文章于 2021-03-25 19:21:03 发布

阅读量325

点赞数

分类专栏：数据库文章标签： mssql 数据库

本文链接：https://blog.csdn.net/lgs790709/article/details/106737052

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

编者：李国帅

qq：9611153 微信lgs9611153

时间：2008-3-11

问题描述：

数据表分页查询时常用的查询功能，根据某些条件，一页页的展示内容。

名词解释:

存储过程(Stored Procedure)

是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。

所需资源：

MSSql2000

过程1:


CREATE PROCEDURE pageTest --用于翻页的测试
--需要把排序字段放在第一列，需要记住翻到哪里了(id 索引列)。不能自定义页码
--翻页都是需要根据索引(主码)的，可是使用本方法
-- select top 1 * from table1 where id not in (select top n * from table)
--最好的办法就是游标，移动指定记录。
(
@FirstID nvarchar(20)=null, --当前页面里的第一条记录的排序字段的值
@LastID nvarchar(20)=null, --当前页面里的最后一条记录的排序字段的值
@isNext bit=null, --true 1 :下一页；false 0:上一页
@allCount int output, --返回总记录数
@pageSize int output, --返回一页的记录数
@CurPage int --页号（第几页）0：第一页；-1最后一页。
)
AS
if @CurPage=0--返回第一页的数据
begin
	--统计总记录数
	select @allCount=count(ProductId) from Product_test
	set @pageSize=10
	select top 10 ProductId,ProductName,Introduction from Product_test order by ProductId
end
else if @CurPage=-1 --先反序后正序
	select * from
	(select top 10 ProductId,ProductName,Introduction from Product_test order by ProductId desc ) as aa
	order by ProductId
else
begin
if @isNext=1--翻到下一页
	select top 10 ProductId,ProductName,Introduction from Product_test where ProductId > @LastID order by ProductId
else
--翻到上一页
select * from
	(select top 10 ProductId,ProductName,Introduction from Product_test where ProductId < @FirstID order by ProductId desc) as bb
	order by ProductId
end

过程2:

CREATE procedure pagination1
(@pagesize int,  --页面大小，如每页存储20条记录
@pageindex int   --当前页码
)
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int)  --定义表变量
declare @PageLowerBound int  --定义此页的底码
declare @PageUpperBound int  --定义此页的顶码
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,
-365,getdate()) order by fariqi desc
select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t
where O.gid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
--http://www.linux-cn.com/html/database/other/20070423/10742_9.html

过程3:

/*--调用例子：
     --1.单表/单排序
     EXEC usp_PagingLarge 'bigtable','d_id','d_id,d_title,d_content,d_time',20,1,'','','d_id desc'
     --2.单表/多排序
     EXEC usp_PagingLarge 'bigtable','d_id','*',20,0,'','','d_time asc,d_id desc'
     --3.多表/单排序
     EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_id asc'
     --4.多表/多排序
     EXEC usp_PagingLarge 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc'
G_Add
     EXEC G_PagingLarge 'AlertOIcqTab left join ComTab on AlertOIcqTab.strComCode=ComTab.strComCode', 'AlertOIcqTab.strComCode', 'AlertOIcqTab.*,ComTab.strComName', 20, 0, '', '', 'AlertOIcqTab.id'
*/

ALTER   PROCEDURE G_PagingLarge
@TableNames VARCHAR(2000),      --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100),      --主键，可以为空，但@Order为空时该值不能为空
@Fields      VARCHAR(1000),            --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT,                  --每页记录数
@CurrentPage INT,            --当前页，0表示第1页
@Filter VARCHAR(500) = '',      --条件，可以为空，不用填 where
@Group VARCHAR(200) = '',      --分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = ''      --排序，可以为空，为空默认按主键升序排列，不用填 order by
AS
BEGIN
     DECLARE @SortColumn VARCHAR(2000)
     DECLARE @Operator CHAR(2)
     DECLARE @SortTable VARCHAR(2000)
     DECLARE @SortName VARCHAR(2000)
     IF @Fields = ''
           SET @Fields = '*'
     IF @Filter = ''
           SET @Filter = 'Where 1=1'
     ELSE
           SET @Filter = 'Where ' +   @Filter
     IF @Group <>''
           SET @Group = 'GROUP BY ' + @Group
     IF @Order <> ''
     BEGIN
           DECLARE @pos1 INT, @pos2 INT
           SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
           IF CHARINDEX(' DESC', @Order) > 0
                 IF CHARINDEX(' ASC', @Order) > 0
                 BEGIN
                       IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                             SET @Operator = '<='
                       ELSE
                             SET @Operator = '>='
                 END
                 ELSE
                       SET @Operator = '<='
           ELSE
                 SET @Operator = '>='
           SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
           SET @pos1 = CHARINDEX(',', @SortColumn)
           IF @pos1 > 0
                 SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
           SET @pos2 = CHARINDEX('.', @SortColumn)
           IF @pos2 > 0
           BEGIN
                 SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
                 IF @pos1 > 0
                       SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
                 ELSE
                       SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
           END
           ELSE
           BEGIN
                 SET @SortTable = @TableNames
                 SET @SortName = @SortColumn
           END
     END
     ELSE
     BEGIN
           SET @SortColumn = @PrimaryKey
           SET @SortTable = @TableNames
           SET @SortName = @SortColumn
           SET @Order = @SortColumn
           SET @Operator = '>='
     END
     DECLARE @type varchar(50)
     DECLARE @prec int
     Select @type=t.name, @prec=c.prec
     FROM sysobjects o
     JOIN syscolumns c on o.id=c.id
     JOIN systypes t on c.xusertype=t.xusertype
     Where o.name = @SortTable AND c.name = @SortName
     IF CHARINDEX('char', @type) > 0
     SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
     DECLARE @TopRows INT
     SET @TopRows = @PageSize * @CurrentPage + 1
     --print @TopRows
     --print @Operator
     --print'TableName:'+@TableNames
     --print'Fields:'+@Fields
	DECLARE @execsql varchar(8000)
	set @execsql = 'DECLARE @SortColumnBegin ' + @type +
           ' SET ROWCOUNT ' + convert(varchar(12),@TopRows)
           + ' Select @SortColumnBegin=' + @SortColumn + ' FROM   ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' orDER BY ' + @Order
           + ' SET ROWCOUNT ' + convert(varchar(12),@PageSize)
           + ' Select ' + @Fields + ' FROM   ' + @TableNames + ' ' + @Filter   + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' orDER BY ' + @Order + '      '
	--print @execsql
     EXEC (@execsql)
END

过程4

--获取指定页的数据

CREATE PROCEDURE pagination3
@tblName   varchar(255),       -- 表名
@strGetFields varchar(1000) = '*',  -- 需要返回的列
@fldName varchar(255)='',      -- 排序的字段名
@PageSize   int = 10,          -- 页尺寸
@PageIndex  int = 1,           -- 页码
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序
@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL   varchar(5000)       -- 主语句
declare @strTmp   varchar(110)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型
if @doCount != 0
begin
    if @strWhere !=''
    set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
    else
    set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
	if @OrderType != 0
	begin
	    set @strTmp = "<(select min"
	set @strOrder = " order by [" + @fldName +"] desc"
	--如果@OrderType不是0，就执行降序，这句很重要！

	end
else
begin
    set @strTmp = ">(select max"
    set @strOrder = " order by [" + @fldName +"] asc"
end
if @PageIndex = 1
	begin
	    if @strWhere != ''
	    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from
	[" + @tblName + "] where " + @strWhere + " " + @strOrder
	     else
	     set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from
	["+ @tblName + "] "+ @strOrder
	--如果是第一页就执行以上代码，这样会加快执行速度
	end
else
	begin
	--以下代码赋予了@strSQL以真正执行的SQL代码
	set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from
	["+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "])
	from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from
	[" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
	if @strWhere != ''
	 set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["
	        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
	        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
	        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
	        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
	end
end
exec (@strSQL)

最后分析一个分页过程

SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
　　　　(SELECT TOP n-1 id
　　　　 FROM publish))

CREATE PROCEDURE pagination2
(
 @SQL nVARCHAR(4000),    --不带排序语句的SQL语句
 @Page int,              --页码
 @RecsPerPage int,       --每页容纳的记录数
 @ID VARCHAR(255),       --需要排序的不重复的ID号
 @Sort VARCHAR(255)      --排序字段及规则
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str='SELECT   TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+')
T WHERE T.'+@ID+'NOT IN
(SELECT   TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM
('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str
GO

--其实，以上语句可以简化为：
SELECT TOP 页大小 *
FROM Table1
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID

--但这个存储过程有一个致命的缺点，就是它含有NOT IN字样。虽然我可以把它改造为：
SELECT TOP 页大小 *
FROM Table1
WHERE not exists
(select * from (select top (页大小*页数) * from table1 order by id) b where b.id=a.id )
order by id

--因为分页优化的最终目的就是避免产生过大的记录集，而我们在前面也已经提到了TOP的优势，通过TOP 即可实现对数据量的控制。
--在分页算法中，影响我们查询速度的关键因素有两点：TOP和NOT IN。TOP可以提高我们的查询速度，而NOT IN会减慢我们的查询速度，所以要提高我们整个分页算法的速度，就要彻底改造NOT IN，同其他方法来替代它。

微澜-

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MSSql中实现翻页的5个存储过程

编者：李国帅qq：9611153 微信lgs9611153时间：2008-3-11问题描述：数据表分页查询时常用的查询功能，根据某些条件，一页页的展示内容。名词解释:存储过程(Stored Procedure)是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升预编.
复制链接

扫一扫