通用分页查询

/*完成一个通用的存储过程分页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 = '' THENsetselections = '*';end if;#判断是否填写查询条件if conditions is null or conditions = '' THENset conditions = '1=1';end if;#判断是否填写排序列，若未填写则使用默认的排序列‘_id’（需要事先约定好）if sortcolumn is null or sortcolumn = '' THENset sortcolumn = '_id';end if;#判断是否给定正确的排序方式，如未给定或者不符合要求，则默认使用ASC（升序）if lower(sortOrder) != 'asc' and lower(sortOrder) != 'desc' THENset 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;