mysql通用存储过程分页

/*

完成一个通用的存储过程分页

1.当前页码1必填

2.每页大小5必填

3.任意表'employee'必填

4.任意的查询列'num,name,tel,depno' 可选

5.任意查询条件'depno=1'可选

6.任意的排序列'birth'可选

7.任意的排序类型（asc/desc） 'asc'可选

 

8.总记录数

9.总页数

*/

create procedure sp_paging

(

currentpage int,

pagesize int,

tname varchar(64),

selections varchar(2000),

conditions varchar(2000),

sortcolumn varchar(64),

sortOrderchar(4),

out totalnum int,

out totalpage int

)

BEGIN

#临时变量，标记开始查询位置

declare vstart int default 0;

#设置查询的起始位置

set vstart = (currentpage - 1) * pagesize;

#判断是否填写查询列，若未填写则默认使用*

if selections is null or selections = '' THEN

setselections = '*';

end if;

#判断是否填写查询条件

if conditions is null or conditions = '' THEN

set conditions = '1=1';

end if;

#判断是否填写排序列，若未填写则使用默认的排序列‘_id’（需要事先约定好）

if sortcolumn is null or sortcolumn = '' THEN

set sortcolumn = '_id';

end if;

#判断是否给定正确的排序方式，如未给定或者不符合要求，则默认使用ASC（升序）

if lower(sortOrder) != 'asc' and lower(sortOrder) != 'desc' THEN

set sortorder = 'asc';

end if;

#select * from employee where depno>1 order by birth desc limit 0,5;

set @vsql = concat('select ',

selections,

' from ',

tname,

' where ',

conditions,

' order by ',sortcolumn,' ',sortorder,

' limit ',vstart,',',pagesize);

#对sql语句预编译

prepare stmt from @vsql;

execute stmt;

DEALLOCATE PREPARE stmt;

 

#总记录数

set @vsql = concat('select count(*) into @tnum from ',tname,' where ',conditions);

prepare stmt from @vsql;

execute stmt;

set totalnum = @tnum; #将临时变量中的数据赋值给输出参数

set totalpage = CEILING(totalnum/pagesize);

DEALLOCATE PREPARE stmt;

end;

 

call sp_paging(1,2,'department',null,null,'depno','desc',@tn,@tp);

select @tp,@tn;