游标与select结果集:
本质上一种能从select结果集中每次提取一条记录的机制,因此游标与select语句有绑定关系;游标的作用是处理多行结果集;
使用步骤:
1、声明游标
declare 游标名 cursor for select语句;
使用declare语句声明游标时,此时与该游标对应的select语句并没有执行,MySQL服务器内并不存在于select对应的结果集;
2、打开游标
open 游标名;
使用open语句打开游标后,与该游标对应的select语句被执行,MySQL服务器内存中存放于select语句对应的结果集;
3、循环提取并处理数据
fetch 游标名 into 变量1,变量2,...;
变量的个数必须与声明游标时使用的select语句结果集的字段个数保持一致。每执行一次fetch语句,从结果集中提取一行数据,同时游标向下移动一行;
4、关闭游标
close 游标名;
关闭游标的作用在于释放游标打开时产生的结果集,从而节省MySQL服务器的内存空间。游标如果没有被明确关闭,那么它将在被打开的begin-end语句块的末尾关闭;
应用:
试想,choose表中,记录了所有的学生的成绩和对应的课程;若老师发现一门课程总体成绩不满意,需要进行处理:选择该课程的每个学生的分数,先都加五分,之后将超过100的设置为100,在55~60之间的设置为60,从而提高通过率;我们自然想到用一个 procedure 处理;
先将select语句筛选出,选择该课程的学生,得到学号和成绩的结果集;之后利用游标的特性,循环处理每一行数据,并进行更新;
not found 的触发条件的错误,因此我们利用一个局部变量state设计一个错误处理程序:捕获这个not found,设置这个局部变量为error;
既然要循环处理,那么选择循环的方式:因为每次要先执行fetch语句,然后才判断条件,因此舍弃while(先判断);最后我们为了配合错误处理程序,需要将结束循环条件放到 紧跟 fetch语句块后,判断state状态,判断是否没有数据了;因此我们选择loop循环;而do until是在最后判断状态;
声明变量的顺序:先声明局部变量,再声明游标;接着声明错误处理;
t综合代码如下:
-- 使用步骤:
delimiter $$
create procedure update_score_proc(in c_no int)
begin
-- 声明局部变量
declare stu_n char(11);
declare grade int;
declare state char(10);
-- 1、声明游标
declare score_cur cursor for select student_no,score from choose where c_no=course_no;
-- 捕获错误
declare continue handler for not found
begin
set state = 'error';
end;
-- 2、打开游标
open score_cur;
-- 3、循环提取并处理数据
update_cur : loop
fetch score_cur into stu_n,grade;
if state = 'error' then
leave update_cur;
end if;
set grade = grade + 5;
if grade > 100 then
set grade = 100;
end if;
if grade < 60 and grade >= 55 then
set grade = 60;
end if;
update choose set score=grade where student_no=stu_n and course_no=c_no;
end loop update_cur;
-- 4、关闭游标
close score_cur;
end;
$$
delimiter ;
测试代码1:
call update_score_proc(1);
select * from choose;
测试代码2:
call update_score_proc(2);
select * from choose;