mysql分页通用存储过程

ゝ一世哀愁。 2022-07-16 03:42 258阅读 0赞

use mysql;  
drop procedure if exists prc\_page\_result  
create procedure prc\_page\_result (  
in currpage int,  
in columns varchar(500),  
in tablename varchar(500),  
in sCondition varchar(500),  
in order\_field varchar(100),  
in asc\_field int,  
in primary\_field varchar(100),  
in pagesize int  
)  
begin  
declare sTemp varchar(1000);  
declare sSql varchar(4000);  
declare sOrder varchar(1000);  
  
if asc\_field = 1 then  
set sOrder = concat(' order by ', order\_field, ' desc ');  
set sTemp = '<(select min';  
else  
set sOrder = concat(' order by ', order\_field, ' asc ');  
set sTemp = '>(select max';  
end if;  
  
if currpage = 1 then  
if sCondition <> '' then  
set sSql = concat('select ', columns, ' from ', tablename, ' where ');  
set sSql = concat(sSql, sCondition, sOrder, ' limit ?');  
else  
set sSql = concat('select ', columns, ' from ', tablename, sOrder, ' limit ?');  
end if;  
else  
if sCondition <> '' then  
set sSql = concat('select ', columns, ' from ', tablename);  
set sSql = concat(sSql, ' where ', sCondition, ' and ', primary\_field, sTemp);  
set sSql = concat(sSql, '(', primary\_field, ')', ' from (select ');  
set sSql = concat(sSql, ' ', primary\_field, ' from ', tablename, sOrder);  
set sSql = concat(sSql, ' limit ', (currpage-1)\*pagesize, ') as tabtemp)', sOrder);  
set sSql = concat(sSql, ' limit ?');  
else  
set sSql = concat('select ', columns, ' from ', tablename);  
set sSql = concat(sSql, ' where ', primary\_field, sTemp);  
set sSql = concat(sSql, '(', primary\_field, ')', ' from (select ');  
set sSql = concat(sSql, ' ', primary\_field, ' from ', tablename, sOrder);  
set sSql = concat(sSql, ' limit ', (currpage-1)\*pagesize, ') as tabtemp)', sOrder);  
set sSql = concat(sSql, ' limit ?');  
end if;  
end if;  
set @iPageSize = pagesize;  
set @sQuery = sSql;  
prepare stmt from @sQuery;  
execute stmt using @iPageSize;  
end;