mysql - 创建 MySQL 存储过程时出错

Question

create procedure proc_Grade(in roll int(5),in name varchar(20),in marks int(5))
-> begin
-> declare grade varchar(20);
-> insert into student values(roll,name,marks);
-> if marks<=100 and marks>=67 then set grade="distinction";
-> if marks<=66 and marks>=57 then set grade="firstclass";
-> if marks<=56 and marks>=47 then set grade="secdclass";
-> if marks<=46 and marks>=40 then set grade="pass";
-> else set grade="fail";
-> insert into result values(roll,grade);
-> end if;
-> end$$

ERROR 1064 (42000): 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 '' at line 12

Here's a screenshot of command line client

Answer 1

就错误消息而言，该消息特别没有帮助。

还有两个问题:

1. 第二个插入语句(结果)的位置，仅适用于“未通过”类(class)的学生。

2. IF 语句语法，其中第二个以及后续的 IF 应为 ELSEIF，否则应终止每个 带有 END IF 的 IF 语句；

语法应采用以下形式

IF condition1 THEN
  statement1;
ELSEIF condition2 THEN
  statement2;
END IF;  

文档位于 https://dev.mysql.com/doc/refman/5.7/en/if.html (但请注意 END IF 后遗漏分号的拼写错误；)

您的程序将如下所示:

delimiter $$

create procedure proc_Grade(in roll int(5),in name varchar(20),in marks int(5))
begin
  declare grade varchar(20);

  insert into student values(roll,name,marks);

  if marks<=100 and marks>=67 then 
    set grade="distinction";
  elseif marks<=66 and marks>=57 then
    set grade="firstclass";
  elseif marks<=56 and marks>=47 then
    set grade="secdclass";
  elseif marks<=46 and marks>=40 then
    set grade="pass";
  else
    set grade="fail";
  end if;

  insert into result values(roll,grade);

end $$