DROP PROCEDURE IF EXISTS sp_page;CREATE PROCEDURE sp_page1( PageIndex INT, #页码 PageSize INT, #每页记录数 OUT TotalCount INT #总记录数 ) BEGINset @strSqls='select sql_calc_found_rows * from users';set @strSqls=CONCAT(@strSqls,' limit ',(PageIndex-1)*PageSize,',',PageSize);prepare strsql from @strSqls;execute strsql;deallocate prepare strsql;#计算总记录数set TotalCount = found_rows(); END调用：call sp_page(1,10,@a,@b);select @a;
DROP PROCEDURE IF EXISTS aaa;CREATE PROCEDURE aaa( PageIndex INT, #页码 PageSize INT, #每页记录数 OUT TotalCount INT #总记录数 ) BEGINDECLARE StartRow int;set StartRow=(PageIndex-1)*PageSize;SELECT sql_calc_found_rows * FROM 表名limit StartRow,PageSize;#计算总记录数set TotalCount = found_rows(); END
------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_page;CREATE PROCEDURE sp_page( IN UserID INT,-- 用户idIN PageIndex INT,-- 页码 从1开始IN PageSize INT,-- 页大小IN Conditions VARCHAR(2000),-- 过滤IN Orderbys VARCHAR(200),-- 排序OUT TotalCount INT-- 总记录数)BEGIN#处理whereSET @strWhere=' where 1=1 ';IF (UserID>0) THENSET @strWhere = CONCAT(@strWhere, ' AND id =',UserID);END IF;#处理where end#处理conditionSET @strCondition = '';IF (IFNULL(Conditions,'') <> '') THEN SET @strCondition = CONCAT(' where 1=1 ', Conditions);END IF;#处理condition end#处理排序SET @default_order='id asc'; -- 默认排序SET @orderBy = IF(IFNULL(Orderbys,'')='', @default_order,Orderbys);#处理排序 endSET @strSql = CONCAT('SELECT * from user ',@strWhere);#计算总记录数SET @exe_sql = CONCAT('SET @totalcount = (SELECT COUNT(1) FROM (', @strSql, ') tb', @StrCondition,');');PREPARE stmt FROM @exe_sql;EXECUTE stmt;SET TotalCount = @totalcount;#计算总记录数 endSET @exe_sql = @strSql;IF (IFNULL(@strCondition,'') > '') THEN SET @exe_sql = CONCAT(' SELECT * FROM ( ',@strSql,' ) tb', @strCondition,' ORDER BY ',@orderBy, ' LIMIT ', (PageIndex-1)*PageSize, ',', PageSize,' ');ELSE SET @exe_sql = CONCAT( @strSql,' ORDER BY ',@orderBy, ' LIMIT ', (PageIndex-1)*PageSize, ',', PageSize,' ');END IF; PREPARE stmt FROM @exe_sql; #select @exe_sql; EXECUTE stmt;END调用call sp_page3(1,1,10,'and id=1','username desc',@totalcount,@pagecount);SELECT @totalcount;DROP PROCEDURE IF EXISTS sp_page;CREATE PROCEDURE sp_page(IN CategoryID INT,IN PageIndex INT,-- 页码 从1开始IN PageSize INT,-- 页大小IN Orderbys VARCHAR(200),-- 排序OUT TotalCount INT-- 总记录数)BEGINSET @strWhere=' where 1=1 ';#处理whereIF (CategoryID>0) THENSET @strWhere = CONCAT(@strWhere, ' AND CategoryID =',CategoryID);END IF;#处理where endSET @strSql = CONCAT('SELECT * from Article ',@strWhere);#处理排序SET @default_order='ArticleID asc'; -- 默认排序SET @orderBy = IF(IFNULL(Orderbys,'')='', @default_order,Orderbys);#处理排序 end#计算总记录数#SET @exe_sql = CONCAT('SET @totalcount = (SELECT COUNT(1) FROM (', @strSql, ') tb;');SET @exe_sql = CONCAT('SET @totalcount = (SELECT COUNT(1) FROM (', @strSql, ') tb',');');PREPARE stmt FROM @exe_sql;EXECUTE stmt;SET TotalCount = @totalcount;#计算总记录数 endSET @exe_sql = CONCAT( @strSql,' ORDER BY ',@orderBy, ' LIMIT ', (PageIndex-1)*PageSize, ',', PageSize,' '); PREPARE stmt FROM @exe_sql; #select @exe_sql; EXECUTE stmt;ENDcall sp_page(1,1,10,'',@totalcount);SELECT @totalcount;