DROP PROCEDURE IF EXISTS `pro_pager`;
CREATE DEFINER = `root`@`%` PROCEDURE `pro_pager`(
in p_pageNo int, /*当前页*/
in p_perPageCnt int, /*每页记录数*/
in p_sql VARCHAR(2000), /*查询sql语句*/
out v_totalRowsCnt int, /*记录总条数*/
out v_totalPageCnt int) /*记录总页数*/
BEGIN
/*当传入查询页数为null或者<1时，赋p_pageNo=1 */
IF p_pageNo IS NULL OR p_pageNo < 1 THEN
SET p_pageNo = 1;
END IF;
SET @rowsCnt = 0;
SET @pagesCnt = 0;
SET @sqlCnt = CONCAT('select count(1) into @rowsCnt from (',p_sql,') as t'); -- 统计总记录数sql
/*统计总记录数-预处理*/
PREPARE s_cnt from @sqlCnt;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
SET v_totalRowsCnt = @rowsCnt;
SET @pagesCnt = floor((@rowsCnt + p_perPageCnt - 1) / p_perPageCnt); -- 计算总页数
/*当传入查询页数>总页数时，赋p_pageNo=总页数 */
IF p_pageNo > @pagesCnt THEN
SET p_pageNo = @pagesCnt;
END IF;
SET v_totalPageCnt = @pagesCnt;
SET @limitStart = (p_pageNo - 1) * p_perPageCnt; -- 查询记录起始行
SET @limitEnd = p_perPageCnt; -- 查询记录结束行
SET @sqlQry = CONCAT(p_sql, ' limit ', @limitStart, ',', @limitEnd); -- 查询记录集sql
/*查询记录集-预处理*/
PREPARE record from @sqlQry;
EXECUTE record;
DEALLOCATE PREPARE record;
END ;
分享到：
-08-18 17:45
浏览 1043
分类:数据库
评论
