一张表SC

在储存过程中使用游标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();