mysql中游标的使用

游标的基本用法

1、创建一张学生表

drop table if exists sys_student;
create table sys_student(
id int primery key auto_increment,
name varchar(32),
sex char(1),
age int,
create_time varchar(32)
)

2、打开navicat，创建存储过程：

#定义变量
declare isclose int default 0;#游标关闭的标记
declare curId int;
declare curname varchar(32);
declare cur cursor for select id,name from sys_student;
#处理游标异常，游标关闭的标记
declare continue handler for not found set isclose =1;
open cur;
fetch next from cur into curId,curname;
while (isclose=0)
do
#学生姓名改为姓名+id
update sys_student set name = concat(curname ,’-’,curId) where id = curId;
fetch next from cur into curId,curname; #将下一个游标中的值付给变量
end while ;
close cur;#关闭游标