分页获取数据 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','');