关于MySQL储存过程中的游标cursor

一张表SC
关于MySQL储存过程中的游标cursor
在储存过程中使用游标cursor,根据分数逐行更新rank。

DELIMITER &&
CREATE PROCEDURE ud()
BEGIN
    DECLARE vsno CHAR(9);
    DECLARE vcno CHAR(4);
    DECLARE vgrade INT(11); 
    DECLARE v_finished INT DEFAULT 0;
    DECLARE sc_cursor CURSOR FOR SELECT Sno,Cno,Grade FROM SC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
    OPEN sc_cursor;
    up:LOOP
    FETCH sc_cursor  INTO vsno,vcno,vgrade;
    IF v_finished = 1 THEN
    LEAVE up;
    END IF;
    IF vgrade < 60 THEN
    UPDATE SC SET `rank` = 'E' WHERE Sno = vsno AND Cno = vcno;
    ELSEIF vgrade < 70 THEN
    UPDATE SC SET `rank` = 'D' WHERE Sno = vsno AND Cno = vcno;
    ELSEIF vgrade < 80 THEN
    UPDATE SC SET `rank` = 'C' WHERE Sno = vsno AND Cno = vcno;
    ELSEIF vgrade < 90 THEN
    UPDATE SC SET `rank` = 'B' WHERE Sno = vsno AND Cno = vcno;
    ELSE 
    UPDATE SC SET `rank` = 'A' WHERE Sno = vsno AND Cno = vcno;
    END IF;
    END LOOP up;
    CLOSE sc_cursor;
END&&
call ud();
上一篇:[源码解析] 深度学习流水线并行 PipeDream(5)--- 通信模块


下一篇:【Nginx】高性能web,反向代理服务(与FastDFS连用) 基本知识点概括与整理(思维导图)