mysql_技能7_游标

– 游标/光标

– 声明光标：
DECLARE cursor_name CURSOR FOR select_statement ;
– OPEN 光标：
OPEN cursor_name ;
– FETCH 光标：
FETCH cursor_name INTO var_name [, var_name] …
– CLOSE 光标：
CLOSE cursor_name ;

– 查询emp表中数据, 并逐行获取进行展示
create procedure protest11() begin
declare eid int(11);
declare ename varchar(50);
declare eage int(11);

declare esalary int(11); declare empresult cursor for select * from emp;

open emp_result;

fetch empresult into eid,ename,eage,esalary; select concat(‘id=’,eid , ‘, name=’,ename, ‘, age=’, eage, ', 薪资为: ',e_salary);

fetch empresult into eid,ename,eage,esalary; select concat(‘id=’,eid , ‘, name=’,ename, ‘, age=’, eage, ', 薪资为: ',e_salary);

fetch empresult into eid,ename,eage,esalary; select concat(‘id=’,eid , ‘, name=’,ename, ‘, age=’, eage, ', 薪资为: ',e_salary);

fetch empresult into eid,ename,eage,esalary; select concat(‘id=’,eid , ‘, name=’,ename, ‘, age=’, eage, ', 薪资为: ',e_salary);

fetch empresult into eid,ename,eage,esalary; select concat(‘id=’,eid , ‘, name=’,ename, ‘, age=’, eage, ', 薪资为: ',e_salary);

close emp_result;

end;

– 通过循环结构 , 获取游标中的数据 :

create procedure protest12()
begin
DECLARE id int(11);
DECLARE name varchar(50);
DECLARE age int(11);
DECLARE salary int(11);
DECLARE hasdata int default 1;

DECLARE empresult CURSOR FOR select * from emp; DECLARE EXIT HANDLER FOR NOT FOUND set hasdata = 0;

open emp_result;

repeat
fetch empresult into id , name , age , salary;
select concat(‘id为’,id, ‘, name 为’ ,name , ', age为 ’ ,age , ', 薪水为: ', salary);
until hasdata = 0
end repeat;

close emp_result;

end;