13. 游标

-- 游标练习[例子]
-- 所有学生+5分
-- 修改后的在55~59分的一律改为60

DROP PROCEDURE update_record_proc;
DELIMITER $$
CREATE PROCEDURE update_record_proc(IN c_no INT) 
MODIFIES SQL DATA
BEGIN
DECLARE tmp_stu_no VARCHAR(30);
DECLARE tmp_score INT;
DECLARE state VARCHAR(30);
-- 1. 声明游标
DECLARE score_cursor CURSOR FOR SELECT score,student_no FROM choose WHERE c_no = course_no;

DECLARE CONTINUE HANDLER FOR 1329
BEGIN
SET state = 'error';
END; 
-- 2. 打开游标
OPEN score_cursor;

-- 3. 从游标中提取数据

REPEAT
FETCH score_cursor INTO tmp_score,tmp_stu_no;
SET tmp_score = tmp_score + 5;

IF(tmp_score >= 55 AND tmp_score <= 59) THEN
SET tmp_score = 60;
END IF;

IF(tmp_score >= 100) THEN
SET tmp_score = 100;
END IF;

UPDATE choose SET score = tmp_score WHERE course_no = c_no AND student_no = tmp_stu_no;

UNTIL state = 'error'
END REPEAT;

-- 4. 关闭游标
CLOSE score_cursor;
END
$$
DELIMITER ;

CALL update_record_proc(2);
SELECT * FROM choose WHERE course_no = 2;