mysql 預存程序分頁 轉載

標籤：tables   rownum   null   arc   輸入   mysql   mit   size   輸出參數   

/*--名稱：MYSQL版查詢分頁預存程序 by peace 2013-8-14--輸入參數：@fields        -- 要查詢的欄位用逗號隔開--輸入參數：@tables        -- 要查詢的表--輸入參數：@where        -- 查詢條件--輸入參數：@orderby    -- 排序欄位--輸出參數：@page        -- 當前頁計數從1開始--輸出參數：@pagesize    -- 每頁大小--輸出參數：@totalcount -- 總記錄數--輸出參數：@pagecount  -- 總頁數 */DROP PROCEDURE IF EXISTS Query_Pagination; CREATE PROCEDURE Query_Pagination(    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),/*增加統計欄位2013-5-8 peaceli*/    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:[email protected]+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

mysql 預存程序分頁 轉載