mysql 分頁建立預存程序並實現模糊查詢

標籤：ar   on   ef   size   as   sql   mysql   har   rom   

這個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

mysql 分頁建立預存程序並實現模糊查詢