精彩的近乎完美的分页存储过程_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了精彩的近乎完美的分页存储过程_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3546字，纯文字阅读大概需要6分钟。

内容图文

CREATE procedure main_table_pwqzc
(@pagesize int,
@pageindex int,
@docount bit,
@this_id)
as
if(@docount=1)
begin
select count(id) from luntan where this_id=@this_id
end
else
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 id from luntan where this_id=@this_id order by reply_time desc
select a.* from luntan a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
GO

存储过程会根据传入的参数@docount来确定是不是要返回所有要分页的记录总数
特别是这两行
set rowcount @PageUpperBound
insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc

真的是妙不可言!!set rowcount @PageUpperBound当记录数达到@PageUpperBound时就会停止处理查询
,select id 只把id列取出放到临时表里,select a.* from luntan a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
而这句也只从表中取出所需要的记录,而不是所有的记录,结合起来,极大的提高了效率!!
妙啊,真的妙!!!!

CREATE PROCEDURE Paging_RowCount
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END

IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END

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 @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' @Filter ' '
SET @strSimpleFilter = ' AND ' @Filter ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' @Group ' '
ELSE
SET @strGroup = ''

/*Execute dynamic query*/
EXEC(
'
DECLARE @SortColumn ' @type '
SET ROWCOUNT ' @strStartRow '
SELECT @SortColumn=' @strSortColumn ' FROM ' @Tables @strFilter ' ' @strGroup ' ORDER BY ' @Sort '
SET ROWCOUNT ' @strPageSize '
SELECT ' @Fields ' FROM ' @Tables ' WHERE ' @strSortColumn @operator ' @SortColumn ' @strSimpleFilter ' ' @strGroup ' ORDER BY ' @Sort '
'
)
GO

内容总结

以上是互联网集市为您收集整理的精彩的近乎完美的分页存储过程_MySQL全部内容，希望文章能够帮你解决精彩的近乎完美的分页存储过程_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---