存储过程MYSQL

xkrw2x1b 于 2022-12-26 发布在 Mysql

关注(0)|答案(2)|浏览(156)

我正在使用MySQL完成有关存储过程的高级IBM任务，
无法执行以下代码，它计数语法错误
我有表public_school和列schoolID（应为整数）和列Leaders_Score（应为整数）
我想使用（CASE-END）更新列Leaders_Icon，因为它随着分数本身的变化而变化
密码是：

DELIMITER @
  
CREATE PROCEDURE UPDATE_LEADERS_SCORE (in_School_ID INT, in_Leader_Score INT) 
BEGIN
    UPDATE public_school
    SET Leaders_Score = in_Leader_Score
    WHERE School_ID = in_School_ID;
    CASE
        WHEN in_Leaders_Score >=80 THEN 
            UPDATE public_school
            SET Leaders_Icon = "Very_Strong"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >= 60 and in_Leaders_Score <= 79  
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Strong"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >=  40 and in_Leaders_Score <=  59
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Average"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >=  20 and in_Leaders_Score <=  39 
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Weak"
            WHERE School_ID = in_School_ID;
        ELSE
            UPDATE public_school
            SET Leaders_Icon = "Very Weak"
            WHERE School_ID = in_School_ID;
    END
END @
  
DELIMITER ;

顺便说一下- MySQL在--（END @）旁边给出了错误的红色标志
我不知道我做错了什么，也不知道下一步该怎么办

mysql

来源：https://stackoverflow.com/questions/74893714/stored-procedure-mysql

2条答案

按热度按时间

mkshixfv1#

CASE语句必须以END CASE结尾，而不仅仅是END，并且在语句之后需要一个;。

CREATE PROCEDURE UPDATE_LEADERS_SCORE (in_School_ID INT, in_Leader_Score INT) 
BEGIN
    UPDATE public_school
    SET Leaders_Score = in_Leader_Score
    WHERE School_ID = in_School_ID;
    CASE
        WHEN in_Leaders_Score >=80 THEN 
            UPDATE public_school
            SET Leaders_Icon = "Very_Strong"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >= 60 and in_Leaders_Score <= 79  
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Strong"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >=  40 and in_Leaders_Score <=  59
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Average"
            WHERE School_ID = in_School_ID;
        WHEN in_Leaders_Score >=  20 and in_Leaders_Score <=  39 
        THEN
            UPDATE public_school
            SET Leaders_Icon = "Weak"
            WHERE School_ID = in_School_ID;
        ELSE
            UPDATE public_school
            SET Leaders_Icon = "Very Weak"
            WHERE School_ID = in_School_ID;
    END CASE;
END @

赞(0）回复(0）举报 2022-12-26

6jjcrrmo2#

@Maysara -以下是不带CASE的较短版本

DELIMITER @

CREATE PROCEDURE UPDATE_LEADERS_SCORE ( IN in_School_ID INT, IN in_Leader_Score INT) 
BEGIN
    UPDATE public_school
    SET Leaders_Score = in_Leader_Score,
        Leaders_Icon  = ELT( LEAST(( in_Leader_Score div 20 )+1, 5)
        , "Very Weak", "Weak" , "Average", "Strong", "Very_Strong")    
    WHERE School_ID = in_School_ID;
    
END @

DELIMITER ;

赞(0）回复(0）举报 2022-12-26

我来回答

存储过程MYSQL

2条答案

相关问题

热门标签

最新问答