[mysql学习]存储过程分页-代码

SET @sumsql = contact('select ',_sumfields,' from ',_tables,CASE IFNULL(_where,'') WHEN '' THEN '' ELSE CONCAT(' where ',_where) END);
SELECT CONCAT('@sumsql==',@sumsql);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `cadb`.`Query_Pagination`(
IN _fields VARCHAR(2000),
IN _tables TEXT,
| 2 | 1 | INSP20140909111 | 进口测试商品 | 69373266589 | 2014-05-12 00:00:00 | 测试 | 测试 | 1 | INSP20140909111-1 |
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 0,3 |
OUT _totalcount INT ,
OUT _pagecount INT
)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| 1 | -1 | 110040114000736 | 2011年拉抚庄园哈斯图干红葡萄酒 | 69373266589 | 2014-02-09 00:00:00 | 北京玖美电子商务有限公司 | 北京润和恒安国际贸易有限公司 | 1 | 2204210000 |
SELECT CONCAT('_totalcount==',_totalcount);
SELECT CONCAT('_pageSize==',_pageSize);
IF (_totalcount <= _pageSize) THEN
SET _pagecount = 1;
mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',1,3,'',@_totalcount,@_pagecount);
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
/*
--名称：MYSQL版查询分页存储过程 by peace 2013-8-14
--输入参数：@fields -- 要查询的字段用逗号隔开
--输入参数：@tables -- 要查询的表
3 rows in set
+-------------------------------------+
SELECT CONCAT('strsql==',@strsql);
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
SET _totalcount = FOUND_ROWS();
IN _where VARCHAR(2000),
IN _orderby VARCHAR(200),
IN _NT,
IN _sumfields VARCHAR(200),/*增加统计字段2013-5-8 peaceli*/
+-----------+-----------+-----------------+--------------------------------+-------------+---------------------+--------------------------+------------------------------+----------+-------------------+
*/
BEGIN
SELECT '--begin--';
SET @startRow = _pageSize*(_pageIndex -1);
SET @pageSize = _pageSize;
SET @rowindex = 0;
SET @strsql = CONCAT('select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,',_fields,' from ',_tables,CASE IFNULL(_where,'') WHEN '' THEN '' ELSE CONCAT(' where ',_where) END,' order by ',_orderby,' limit ',@startRow,',',@pageSize);
+-----------+-----------+-----------------+--------------------------------+-------------+---------------------+--------------------------+------------------------------+----------+-------------------+
| 3 | 2 | BJ002 | 进口测试商品1 | 69373266590 | 2014-05-09 00:00:00 | 杭州滨江 | 杭州西湖 | 1 | hs001 |
--输入参数：@where -- 查询条件
--输入参数：@orderby -- 排序字段
--输出参数：@page -- 当前页计数从1开始
--输出参数：@pagesize -- 每页大小
--输出参数：@totalcount -- 总记录数
--输出参数：@pagecount -- 总页数
PREPARE sumsql FROM @sumsql;
EXECUTE sumsql;
DEALLOCATE PREPARE sumsql;
END IF;
SELECT '--end--';
END$$
DELIMITER ;
1 row in set
+-----------+-----------+-----------------+--------------------------------+-------------+---------------------+--------------------------+------------------------------+----------+-------------------+
| rownumber | InspectID | InspectCode | ProductName | TypeID | InspectDate | InDistributors | InAgentor | Category | HsCode |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
ELSE
IF (MOD(_totalcount , _pageSize )> 0) THEN