分頁獲取數據 code

DELIMITER $$ DROP PROCEDURE IF EXISTS `testDB`.`GetRecordAsPage` $$ CREATE PROCEDURE `testDB`.`GetRecordAsPage` (in tbName varchar(800),in fldName varchar(1000), in strWhere varchar(500),in pageIndex int,in pageSize int,in orderType int,in sortName varchar(50)) BEGIN declare startRow int; declare sqlStr varchar(1000); declare limitTemp varchar(1000); declare orderTemp varchar(1000); set startRow = (pageIndex-1)*pageSize; set sqlStr = CONCAT('SELECT ',fldName,' from ',tbName); set limitTemp = CONCAT(' limit ',startRow,',',pageSize); set orderTemp = CONCAT(' order by ',sortName); if orderType = 0 then set orderTemp = CONCAT(orderTemp,' ASC '); else set orderTemp = CONCAT(orderTemp,' DESC '); end if; set @sqlString = CONCAT(sqlStr,' ',strWhere,orderTemp,limitTemp); prepare sqlstmt from @sqlString; execute sqlstmt; deallocate prepare sqlstmt; END $$ DELIMITER ; //執行存儲過程
CALL `testDB`.`GetRecordAsPage`('article','*','',1,2,1,'aid');

　　

?獲得總記錄數

DELIMITER $$ DROP PROCEDURE IF EXISTS `testDB`.`GetRecordCount` $$ CREATE PROCEDURE `testDB`.`GetRecordCount`(in tbName varchar(1000),in strWhere varchar(1000)) BEGIN set @strSQL=concat('select count(*) as countStr from ',tbName,strWhere); prepare sqlstmt from @strSQL; execute sqlstmt; deallocate prepare sqlstmt; END $$ DELIMITER ;
//執行存儲過程
CALL testDB.GetRecordCount('article','');

　　

?

轉載于:https://www.cnblogs.com/xsj1989/p/6482790.html

總結

以上是生活随笔為你收集整理的MySql分页存储过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。