/*
完成一个通用的存储过程分页
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),
sortOrder char(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
set selections = '*';
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;
通用分页查询
最新推荐文章于 2021-08-27 21:53:32 发布