MySQL神秘误差

Question

PHPMyAdmin一直告诉我，我在第41行(第三行)有一个错误。我似乎找不到它，任何帮助/建议都将不胜感激。我已经检查了IF-然后语法和游标。

BEGIN
DECLARE `done` BOOLEAN DEFAULT false;
DECLARE `a` TEXT;
DECLARE `cur` CURSOR FOR SELECT `mana_cost` FROM `cards`;   
DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done`=TRUE;



OPEN `cur`;

read_loop : LOOP

    FETCH `cur` INTO `a`;
    IF `done`
    THEN LEAVE read_loop;
    END IF;

    IF `a` LIKE '%{B}%'
    THEN            
        UPDATE `cards` SET `color` = 'BLACK '
        WHERE `mana_cost` = `a`;    
    ELSE IF `a` LIKE '%{G}%'
    THEN            
        UPDATE `cards` SET `color` = 'GREEN '
        WHERE `mana_cost` = `a`;    
    ELSE IF `a` LIKE '%{U}%'
    THEN            
        UPDATE `cards` SET `color` = 'BLUE '
        WHERE `mana_cost` = `a`;    
    ELSE IF `a` LIKE '%{W}%'
    THEN            
        UPDATE `cards` SET `color` = 'WHITE '
        WHERE `mana_cost` = `a`;    
    ELSE IF `a` LIKE '%{R}%'
    THEN            
        UPDATE `cards` SET `color` = 'RED '
        WHERE `mana_cost` = `a`;    

    END IF;

END LOOP;  //line 41
CLOSE `cur`;
END

Answer 1

您的问题非常常见，这是因为MySQL在看到“；；”时会停止创建过程。

您需要使用分隔符来包装您的过程;)

分隔符$开始声明done布尔值默认值为false；声明a文本；为从cards选择mana_cost声明cur游标；

为NOT done=TRUE声明继续处理程序；

开放cur；

read_loop :循环

FETCH `cur` INTO `a`;
IF `done`
THEN LEAVE read_loop;
END IF;

IF `a` LIKE '%{B}%'
THEN            
    UPDATE `cards` SET `color` = 'BLACK '
    WHERE `mana_cost` = `a`;    
ELSE IF `a` LIKE '%{G}%'
THEN            
    UPDATE `cards` SET `color` = 'GREEN '
    WHERE `mana_cost` = `a`;    
ELSE IF `a` LIKE '%{U}%'
THEN            
    UPDATE `cards` SET `color` = 'BLUE '
    WHERE `mana_cost` = `a`;    
ELSE IF `a` LIKE '%{W}%'
THEN            
    UPDATE `cards` SET `color` = 'WHITE '
    WHERE `mana_cost` = `a`;    
ELSE IF `a` LIKE '%{R}%'
THEN            
    UPDATE `cards` SET `color` = 'RED '
    WHERE `mana_cost` = `a`;    

END IF;

END循环；//第41行关闭cur；END $ *分隔符;*

您应该尝试这样的方法，并查看关于划界器和MySQL笑话的文档:P