mysql存储分页
DROP PROCEDURE IF EXISTS UP_SplitPages;CREATE PROCEDURE UP_SplitPages(nCurrPage int,/*当前页*/nFieldName varchar(1000),/*显示字段*/nTablename varchar(100),/*表名*/nWhereStr varchar(1000),/*查询条件*/nOrderStr varchar(1000),/*排序条件*/nPageSize int/*每页条数*/)BEGIN DECLARE sBeginRow INT DEFAULT 0;/*行数*/ DECLARE sLimit varchar(1000); IF nCurrPage<1 THEN set sBeginRow = 0; ELSE set sBeginRow = (nCurrPage-1)*nPageSize; END IF; set sLimit = CONCAT(' LIMIT ',sBeginRow,', ',nPageSize);/*limit串 */ IF (nWhereStr is null OR nWhereStr='') THEN SET @sWhereStr = '' ; ELSE SET @sWhereStr =CONCAT(' WHERE ',nWhereStr); END IF; IF (nOrderStr is null OR nOrderStr='') THEN SET @sOrderStr = '' ; ELSE SET @sOrderStr =concat(' ORDER BY ',nOrderStr); END IF; SET @sql_data = CONCAT('SELECT ',nFieldName,' FROM ',nTablename,@sWhereStr,@sOrderStr,sLimit); /*SET @sql_count=CONCAT('SELECT COUNT(*) FROM ',nTablename,@sWhereStr); PREPARE stmtCount FROM @sql_count; EXECUTE stmtCount; DEALLOCATE PREPARE stmtCount;*/ PREPARE stmtData FROM @sql_data; EXECUTE stmtData; DEALLOCATE PREPARE stmtData;END; 调用:call UP_SplitPages (2,'RID,GroupID,GroupName,GroupType,Description','Group','1=1 and GroupType<10','group.GroupID','RID',4)?
?
调用:call UP_SplitPages (2,'RID,GroupID,GroupName,GroupType,Description','Group','1=1 and GroupType<10','Group.GroupID','RID',4)
如果存储过程返回有两个结果集,php中不知道如何获取,我没试成功,哪位有好的方式能处理,请留言