通用分页查询

/*
完成一个通用的存储过程分页
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;