MySql 分页存储过程
孤风一剑
6月 05, 2014
231
- DELIMITER $$ #修改分隔符为 $$
- DROP PROCEDURE IF EXISTS sp_MvcCommonDataSource$$ #分隔符
- CREATE PROCEDURE sp_MvcCommonDataSource (
- #输入参数
- _fields VARCHAR(2000), #要查询的字段，用逗号(,)分隔
- _tables TEXT, #要查询的表
- _where VARCHAR(2000), #查询条件
- _orderby VARCHAR(200), #排序规则
- _pageindex INT, #查询页码
- _pageSize INT, #每页记录数
- _sumfields VARCHAR(200),#求和字段
- #输出参数
- OUT _totalcount INT, #总记录数
- OUT _pagecount INT, #总页数
- OUT _sumResult VARCHAR(2000)#求和结果
- )
- BEGIN
- #140529-xxj-分页存储过程
- #计算起始行号
- SET @startRow = _pageSize * (_pageIndex – 1);
- SET @pageSize = _pageSize;
- SET @rowindex = 0; #行号
-
- #合并字符串
- SET @strsql = CONCAT(
- #‘select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,’ #记录行号
- ‘select sql_calc_found_rows ‘
- ,_fields
- ,‘ from ‘
- ,_tables
- ,CASE IFNULL(_where, ”) WHEN ” THEN ” ELSE CONCAT(‘ where ‘, _where) END
- ,CASE IFNULL(_orderby, ”) WHEN ” THEN ” ELSE CONCAT(‘ order by ‘, _orderby) END
- ,‘ limit ‘
- ,@startRow
- ,‘,’
- ,@pageSize
- );
-
- PREPARE strsql FROM @strsql;#定义预处理语句
- EXECUTE strsql; #执行预处理语句
- DEALLOCATE PREPARE strsql; #删除定义
- #通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数
- SET _totalcount = FOUND_ROWS();
-
- #计算总页数
- IF (_totalcount <= _pageSize) THEN
- SET _pagecount = 1;
- ELSE IF (_totalcount % _pageSize > 0) THEN
- SET _pagecount = _totalcount / _pageSize + 1;
- ELSE
- SET _pagecount = _totalcount / _pageSize;
- END IF;
- END IF;
-
- #计算求和字段
- IF (IFNULL(_sumfields, ”) <> ”) THEN
- #序列sum结果
- SET @sumCols = CONCAT (
- ‘CONCAT_WS(\’,\’,’
- ,‘SUM(‘
- ,REPLACE(_sumfields,‘,’,‘),SUM(‘)
- ,‘))’);
- #拼接字符串
- SET @sumsql = CONCAT(
- ‘select ‘
- ,@sumCols
- ,‘ INTO @sumResult from ‘
- ,_tables
- ,CASE IFNULL(_where, ”) WHEN ” THEN ” ELSE CONCAT(‘ where ‘, _where) END
- ,‘;’
- );
- #select @sumsql;
- PREPARE sumsql FROM @sumsql;#定义预处理语句
- EXECUTE sumsql;
- SET _sumResult = @sumResult; #执行预处理语句
- DEALLOCATE PREPARE sumsql; #删除定义
-
- END IF;
-
- END$$
- DELIMITER ; #修改分隔符为分号(;)
-
-
- ##################################################
- # 测试存储过程
- #select order_no,order_date,order_type from `order`;
-
- CALL sp_MvcCommonDataSource(
- ‘order_no,order_date,order_type’#查询字段
- ,‘`order`’#表名
- ,‘1=1’#条件
- ,‘order_no asc’#排序
- ,2 #页码
- ,3 #每页记录数
- ,‘order_no,order_no’#求和字段
- ,@totalcount #输出总记录数
- ,@pagecount #输出用页数
- ,@sumResult #求和结果
- );
- SELECT @totalcount,@pagecount,@sumResult;
