游标/光标
-- 查询emp表中数据, 并逐行获取进行展示create procedure pro_test11()begindeclare e_id int(11);declare e_name varchar(50);declare e_age int(11);declare e_salary int(11);declare emp_result cursor for select * from emp;open emp_result;fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);close emp_result;end$
通过循环结构 , 获取游标中的数据 :
create procedure pro_test12()begindeclare e_id int(11);declare e_name varchar(50);declare e_age int(11);declare e_salary int(11);declare has_data int default 1;declare emp_result cursor for select * from emp;DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;open emp_result;repeatfetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);until has_data = 0end repeat;close emp_result;end$
