MySql分页存储过程

分页获取数据 code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

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 ; //执行存储过程<br>CALL `testDB`.`GetRecordAsPage`('article','*','',1,2,1,'aid');

 获得总记录数

?

1 2 3 4 5 6 7 8 9 10

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 ;<br>//执行存储过程<br>CALL testDB.GetRecordCount('article','');

 执行存储过程：CALL SP_testsp(@ErrorCode,@Message); -- @ErrorCode,@Message 不需要声明，直接写，存储过程的参数是要加@符号的。