MySQL笔记： 游标

-- 游标练习[例子]
-- 所有学生+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;

BEGIN
  DECLARE temp_id INT;
  DECLARE temp_accounter_id INT;
  DECLARE temp_accounter_no VARCHAR(64);
    DECLARE state VARCHAR(30);
    DECLARE account_book_cursor CURSOR FOR SELECT id,accounter_id FROM account_book;
    DECLARE CONTINUE HANDLER FOR 1329
    BEGIN
        SET state = 'error';
    END; 
  OPEN account_book_cursor;
  REPEAT
        FETCH account_book_cursor INTO temp_id,temp_accounter_id;
        SELECT no INTO temp_accounter_no FROM account WHERE id = temp_accounter_id;
        UPDATE account_book SET accounter_no = temp_accounter_no WHERE id = temp_id;
        UNTIL state = 'error'
    END REPEAT;
    CLOSE account_book_cursor;
END