寻找解决方法——mysql创建储存函数

Question

问题遇到的现象和发生背景

问题相关代码：CREATE FUNCTION func_4(student_sno VARCHAR(10))

RETURNS VARCHAR(8)
BEGIN
RETURN( SELECT a=AVG(degree) FROM sc
IF a>=90 THEN
SELECT "该生成绩优秀" as 查询结果;
ELSEIF a>=80 THEN
SELECT "该生成绩良好" as 查询结果;
ELSEIF a>=60 THEN
SELECT "该生成绩及格" as 查询结果;
ELSE SELECT "该生成绩不合格" as 查询结果;
WHERE sno=student_sno);
END

运行结果及报错内容 [SQL] CREATE FUNCTION func_4(student_sno VARCHAR(10))

RETURNS VARCHAR(8)
BEGIN
RETURN( SELECT a=AVG(degree) FROM sc
IF a>=90 THEN
SELECT "该生成绩优秀" as 查询结果;
ELSEIF a>=80 THEN
SELECT "该生成绩良好" as 查询结果;
ELSEIF a>=60 THEN
SELECT "该生成绩及格" as 查询结果;
ELSE SELECT "该生成绩不合格" as 查询结果;
WHERE sno=student_sno);
END

[Err] 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 'IF a>=90 THEN
SELECT "该生成绩优秀" as 查询结果;
ELSEIF a>=80 THEN' at line 5

我想要达到的结果：想要寻找思路，找到解决方法

Answer 1

一般来说,常规写法是,这个过程里要声明两个变量,一个平均分,一个返回值,
先根据条件查询平均分, select into 到这个变量里去,然后再用if来判断这个变量,根据不同情况对这个返回值进行赋值,最后return返回值；

另一个做法就是,先写个查询sql,计算平均值,并直接用case when 判断,直接得出最终输出的结果,然后把这个sql改成 into 到一个变量,最后return它就好了

DELIMITER $$
CREATE     OR REPLACE FUNCTION test_func ( student_sno VARCHAR ( 20 ) ) 
RETURNS VARCHAR ( 20 ) 
BEGIN
DECLARE l_avg FLOAT;
DECLARE    o VARCHAR ( 20 );
SELECT    AVG( degree ) INTO l_avg 
  FROM    sc x 
  WHERE sno = student_sno;
IF
    l_avg >= 90 THEN
    SET o = 'AAA';
ELSEIF l_avg >= 70 THEN
  SET o = 'AA';
END IF;
RETURN O;
END 
$$
DELIMITER;