展开全部
工具:SQLyogDELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `p_update_student_score`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_update_student_score`()
BEGIN
DECLARE ids INT;
DECLARE ranks INT;
DECLARE stopFlag INT;
DECLARE cursor_ids CURSOR FOR SELECT t.id ids,@a := @a + 1 ranks FROM (SELECT * FROM STUDENT ORDER BY score DESC) t ,(SELECT @a := 0) AS a;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stopFlag=1;
OPEN cursor_ids;
REPEAT
FETCH cursor_ids INTO ids,ranks;
UPDATE STUDENT SET RANK = ranks WHERE id = ids;
UNTIL stopFlag END REPEAT;
CLOSE cursor_ids;
END$$
DELIMITER ;
用法:e68a84e8a2ad3231313335323631343130323136353331333335343364CALL p_update_student_score
结果: