MySQL自定义函数

Question

数据库链接：https://pan.baidu.com/s/1xgLgKKOrhUR6UmO3yIZTGw
提取码：gt5m

Answer 1

第一题：定义函数fun_sum()计算n!

create function fun_sum(n int)
returns int #返回值类型
begin
    declare i int default 1; #变量声明及初始化
    declare ans int default 1;
    while i<=n do
        set ans=ans*i;
        set i=i+1;
    end while;   /*注意：end while后面要加分号*/
    return ans;  -- 返回结果
end

select fun_sum(4)

第二题：

delimiter $$
CREATE FUNCTION fun_class ( s_no VARCHAR ( 20 ) ) 
RETURNS VARCHAR ( 20 ) 
READS SQL DATA 
BEGIN
    DECLARE    classname VARCHAR ( 20 );
    SELECT
        clname INTO classname 
    FROM
        student s
        LEFT JOIN class c ON s.classno = c.classno 
    WHERE
        sno = s_no;
    RETURN classname;
END;
$$

select fun_class('2018010101');

第三题：

delimiter $$
CREATE FUNCTION fun_compare (peacetime decimal(5,1),practice decimal(5,1)) 
RETURNS VARCHAR (20) 
READS SQL DATA 
BEGIN
    DECLARE    compareresult VARCHAR ( 20 );
  case when peacetime = practice then set compareresult = '比较均衡';
           when peacetime > practice then set compareresult = '加强实践';
             when peacetime < practice then set compareresult = '注重平时' ;
            end case ;
    RETURN compareresult;
END;
$$

select sno,peacetime,practice,fun_compare(peacetime,practice) from score