mysql - 过程中的 SQL 错误，错误代码 = 1064

Question

我正在使用 MySQL，在插入此过程时出现错误，下面是显示的错误，如果有人可以提供帮助，请。

    CREATE PROCEDURE esperance_gain
BEGIN
declare i int(10);
declare E int(20);
set E:=0;
Select count(*) into nb_lignes from questionnaire;
set i:=1;
while (i<=nb_lignes) LOOP
select question4,question7,question8 from questionnaire where i=identifiant;
case question7
when question7='moins de 1dt' then 
case question8
when question8='certain' then set E:=E+0.5;
when question8='fort probable' then set E:=E+(0.5*0.75);
when question8='en fonction des prochains jeux' then set E:=E+(0.5*0.5);
when question8='peu probable' then set E:=E+(0.5*0.25);
when question8='probabilité nulle' then set E:=E+(0.5*0);
end case;
when question7='entre 1dt et 5dt' then
case question8
when question8='certain' then set E:=E+3;
when question8='fort probable' then set E:=E+(3*0.75);
when question8='en fonction des prochains jeux' then set E:=E+(3*0.5);
when question8='peu probable' then set E:=E+(3*0.25);
when question8='probabilité nulle' then set E:=E+(3*0);
end case;
when question7='entre 5dt et 15dt' then
case question8
when question8='certain' then set E:=E+10;
when question8='fort probable' then set E:=E+(10*0.75);
when question8='en fonction des prochains jeux' then set E:=E+(10*0.5);
when question8='peu probable' then set E:=E+(10*0.25);
when question8='probabilité nulle' then set E:=E+(10*0);
end case;
when question7='entre 15dt et 20dt' then
case question8
when question8='certain' then set E:=E+17.5;
when question8='fort probable' then set E:=E+(17.5*0.75);
when question8='en fonction des prochains jeux' then set E:=E+(17.5*0.5);
when question8='peu probable' then set E:=E+(17.5*0.25);
when question8='probabilité nulle' then set E:=E+(17.5*0);
end case;
when question7='plus de 20dt' then
case question8
when question8='certain' then 
case question4
when question4='Oui' then set E:=E+(60*1);
when question4='Parfois' then set E:=E+(35*1);
when question4='Non' then set E:=E+(20*1);
end case;
when question8='fort probable' then 
case question4
when question4='Oui' then set E:=E+(60*0.75);
when question4='Parfois' then set E:=E+(35*0.75);
when question4='Non' then set E:=E+(20*0.75);
end case;
when question8='en fonction des prochains jeux' then 
case question4
when question4='Oui' then set E:=E+(60*0.5);
when question4='Parfois' then set E:=E+(35*0.5);
when question4='Non' then set E:=E+(20*0.5);
end case;
when question8='peu probable' then 
case question4
when question4='Oui' then set E:=E+(60*0.25);
when question4='Parfois' then set E:=E+(35*0.25);
when question4='Non' then set E:=E+(20*0.25);
end case;
when question8='probabilité nulle' then
case question4
when question4='Oui' then set E:=E+(60*0);
when question4='Parfois' then set E:=E+(35*0);
when question4='Non' then set E:=E+(20*0);
end case;
end case;
set i:=i+1;
END LOOP;
END 

这是显示的错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN
declare i int(10);
declare E int(20);
set E:=0;
Select count(*) into n' at line 2

我在 oracle10g 中试过，它运行良好，但 MySQL 让我抓狂

Answer 1

您缺少一些关键字，而且就像 vhu 已经说过您缺少括号一样。

我建议设置定界符，这样程序体和使用的;就没有问题了。您没有声明名称为 nb_lignes 的变量，我已将其添加到过程中。您还错过了一个 end case 而第二个循环是没必要，我删除了它并将 END LOOP 替换为 END WHILE。

希望对你有帮助。

DELIMITER \\

CREATE PROCEDURE esperance_gain()
BEGIN
    declare i int(10);
    declare E int(20);
    declare nb_lignes int;
    set E:=0;
    Select count(*) into nb_lignes from questionnaire;
    set i:=1;
    while (i<=nb_lignes) do 
        select question4,question7,question8 from questionnaire where i=identifiant;
        case question7
            when question7='moins de 1dt' then 
            case question8
                when question8='certain' then set E:=E+0.5;
                when question8='fort probable' then set E:=E+(0.5*0.75);
                when question8='en fonction des prochains jeux' then set E:=E+(0.5*0.5);
                when question8='peu probable' then set E:=E+(0.5*0.25);
                when question8='probabilité nulle' then set E:=E+(0.5*0);
            end case;
            when question7='entre 1dt et 5dt' then
            case question8
                when question8='certain' then set E:=E+3;
                when question8='fort probable' then set E:=E+(3*0.75);
                when question8='en fonction des prochains jeux' then set E:=E+(3*0.5);
                when question8='peu probable' then set E:=E+(3*0.25);
                when question8='probabilité nulle' then set E:=E+(3*0);
            end case;
            when question7='entre 5dt et 15dt' then
            case question8
                when question8='certain' then set E:=E+10;
                when question8='fort probable' then set E:=E+(10*0.75);
                when question8='en fonction des prochains jeux' then set E:=E+(10*0.5);
                when question8='peu probable' then set E:=E+(10*0.25);
                when question8='probabilité nulle' then set E:=E+(10*0);
            end case;
            when question7='entre 15dt et 20dt' then
            case question8
                when question8='certain' then set E:=E+17.5;
                when question8='fort probable' then set E:=E+(17.5*0.75);
                when question8='en fonction des prochains jeux' then set E:=E+(17.5*0.5);
                when question8='peu probable' then set E:=E+(17.5*0.25);
                when question8='probabilité nulle' then set E:=E+(17.5*0);
            end case;
            when question7='plus de 20dt' then
            case question8
                when question8='certain' then 
                case question4
                    when question4='Oui' then set E:=E+(60*1);
                    when question4='Parfois' then set E:=E+(35*1);
                    when question4='Non' then set E:=E+(20*1);
                end case;
                when question8='fort probable' then 
                case question4
                    when question4='Oui' then set E:=E+(60*0.75);
                    when question4='Parfois' then set E:=E+(35*0.75);
                    when question4='Non' then set E:=E+(20*0.75);
                end case;
                when question8='en fonction des prochains jeux' then 
                case question4
                    when question4='Oui' then set E:=E+(60*0.5);
                    when question4='Parfois' then set E:=E+(35*0.5);
                    when question4='Non' then set E:=E+(20*0.5);
                end case;
                when question8='peu probable' then 
                case question4
                    when question4='Oui' then set E:=E+(60*0.25);
                    when question4='Parfois' then set E:=E+(35*0.25);
                    when question4='Non' then set E:=E+(20*0.25);
                end case;
                when question8='probabilité nulle' then
                case question4
                    when question4='Oui' then set E:=E+(60*0);
                    when question4='Parfois' then set E:=E+(35*0);
                    when question4='Non' then set E:=E+(20*0);
                end case;
            end case;
        end case;
    set i:=i+1;
    END WHILE;
END \\

DELIMITER ;