mysql 分页创建存储过程并实现模糊查询

这个mysql 是在 navicat 里面写的。

create procedure Aa_GetStudentByPage
(
in _pageIndex int,
in _pageSize int,
in _userName varchar(20),
out _pageCount int
)
begin
declare _paidex int default 0;
declare _count int default 0;
set _paidex=(_pageIndex-1)*_pageSize;

if(_userName='') then
begin
select COUNT(id) into _count from student;
select student.`name`,student.age,student.`password` from student LIMIT _paidex,_pageSize;
end;
else
begin
select COUNT(id) into _count from student where student.`name` like CONCAT("%",_userName,"%");
select student.`name`,student.age,student.`password` from student where student.`name` like CONCAT("%",_userName,"%") limit _paidex,_pageSize;
end;
end if;
set _pageCount=CEILING(_count*1.0/_pageSize);
end