MYSQL Paging Stored Procedure

DELIMITER $$ #改动分隔符为 $ $DROP PROCEDURE IF EXISTS sp_mvccommondatasource$$ #分隔符CREATE PROCEDURE sp_mvccommondatasource (#输入 Number of _fields varchar, #要查询的字段, separated by commas (,) _tables TEXT, #要查询的表_where varchar, #查询条件_orderby varchar, #排序规则_p Ageindex int, #查询页码_pageSize int, #每页记录数_sumfields VARCHAR ($), #求和字段 # output parameters out _totalcount int, #总记录数OUT _pagecount int , #总页数OUT _sumresult VARCHAR #求和结果) begin#140529-xxj-page Stored Procedure # calculates the starting line number set @startRow = _pagesize * (_pageindex-1); SET @pageSize = _pagesize; SET @rowindex = 0; #行号 # merge String Set @strsql = CONCAT (# ' Select Sql_calc_found_rows @rowindex: [email protected]+1 as RowNumber, ' # Record line number ' 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 records do not use the limit statement, get the number of rows set _totalcount = Found_rows () using Found_rows (), #计算总页数IF (_totalcount <= _pagesize) TH Enset _pagecount = 1; ELSE IF (_totalcount% _pagesize > 0) Thenset _pagecount = _totalcount/_pagesize + 1; Elseset _pagecount = _totalcount/_pagesize; END IF; END IF; #计算求和字段IF (Ifnull (_sumfields, ') <> ") then# sequence SUM result 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; #改动分隔符为分号 (;) ################################################### Test stored procedure #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 #每页记录Number, ' Order_no,order_no ' #求和字段, @totalcount #输出总记录数, @pagecount #输出用页数, @sumResult #求和结果); SELECT @totalcount, @pagecount, @sumResult;