一张表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();