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 ;

分享到：

2010-08-18 17:45

瀏覽 1043

分類:數據庫

評論