- /*
- --名称:MYSQL版查询分页存储过程
- --输入参数:@fields -- 要查询的字段用逗号隔开
- --输入参数:@tables -- 要查询的表
- --输入参数:@where -- 查询条件
- --输入参数:@orderby -- 排序字段
- --输出参数:@page -- 当前页计数从1开始
- --输出参数:@pagesize -- 每页大小
- --输出参数:@totalcount -- 总记录数
- --输出参数:@pagecount -- 总页数
- --输出参数:_sumfields -- 统计字段
- */
- DROP PROCEDURE IF EXISTS Proc_Page;
- CREATE PROCEDURE Proc_Page
- (
- in _fields varchar(2000),
- in _tables text,
- in _where varchar(2000),
- in _orderby varchar(200),
- in _pageindex int,
- in _pagesize int,
- in _sumfields varchar(200),
- out _totalcount int ,
- out _pagecount int
- )
- 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);
- prepare strsql from @strsql;
- execute strsql;
- deallocate prepare strsql;
- 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
- set @sumsql = contact('select ',_sumfields,' from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end);
- prepare sumsql from @sumsql;
- execute sumsql;
- deallocate prepare sumsql;
- end if;
- end
- --该片段来自于http://www.codesnippet.cn/detail/2010201410710.html
来源: http://www.codesnippet.cn/detail/2010201410710.html