6.4 存储函数
#函数 /* 含义：一组预先编译好的SQL语句的集合，理解成批处理语句 1、提高代码的重用性 2、简化操作 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 存储过程与存储函数的区别对比： 存储过程：可以查询、可以增删改、可以不返回值 存储函数：必须 return 一个值（数字、字符串都行），适合处理数据后会返回一个结果的场景。 函数里不能写INSERT/UPDATE/DELETE（只读），适合做查询、计算、判断、拼接。 */ #一、创建语法
sql
DELIMITER //
CREATE FUNCTION 函数名(参数)
RETURNS 返回类型--必须写！
BEGIN
-- 逻辑代码
RETURN 结果; -- 必须返回！
END //
DELIMITER ;/* 注意： 1.参数列表 包含两部分： 参数名 参数类型 2.函数体：肯定会有return语句，如果没有会报错 如果return语句没有放在函数体的最后也不报错，但不建议 存储函数必须返回一个值，必须指定DETERMINISTIC或NO SQL，否则 MySQL 5.7 会报错。 MySQL 5.7 强制要求你告诉它： 这个函数是不是只做查询、不修改数据。 你必须二选一： DETERMINISTIC：输入固定 → 输出固定（查学生、算成绩、判断等级） NO SQL：这个函数不操作数据库（纯计算） 代码里的体现： RETURNS int -- 必须声明返回什么类型 BEGIN RETURN 123; -- 必须有 return 语句 END 3.存储函数没有输出变量，使用 into 变量名 来传递数值， SELECT 查出来多少个字段，INTO 就跟多少个变量，一一对应就行 标准格式： SELECT 字段1, 字段2, 字段3 ... INTO 变量1, 变量2, 变量3 ... FROM 表 WHERE 条件; 一条 SELECT 只能有 一个 INTO ❌不能写多个 INTO 查询字段个数 = INTO 后面变量个数，必须相等 顺序一一对应：第一个字段赋值给第一个变量，依次往下 4.函数体中仅有一句话，则可以省略begin end 5.使用 delimiter语句设置结束标记 */ #二、调用语法
sql
SELECT 函数名(参数列表)#案例 #一、创建函数，实现传入两个float，返回二者之和
sql
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END;
SELECT test_fun1(1,2);#三、查看函数
sql
SHOW CREATE FUNCTION myf3;#四、删除函数
sql
DROP FUNCTION myf3;
DROP FUNCTION IF EXISTS 函数名;#------------------------------案例演示---------------------------- #1.无参有返回 #案例1：返回课程的个数
sql
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义局部变量
SELECT COUNT(*) INTO c #赋值
FROM course;
RETURN c;
END;
SELECT myf1();#2.有参有返回 #案例2：创建存储函数myf2，输入学生姓名in_name，返回学号；查不到返回'未知'。
sql
DELIMITER //
CREATE FUNCTION myf2(in_name VARCHAR(10))
RETURNS VARCHAR(10)
BEGIN
DECLARE n VARCHAR(10);
SELECT Sno INTO n FROM student WHERE Sname = in_name LIMIT 1;
IF n IS NULL THEN
RETURN '未知';
END IF;
RETURN n;
END //
DELIMITER ;-- 调用
sql
SELECT myf2('赵雷');
SELECT myf2('张三');#案例3：创建存储函数myf3，输入学号 + 课程号，返回该生该课成绩；无成绩返回0。
sql
DELIMITER //
CREATE FUNCTION myf3(in_sno VARCHAR(10), in_cno VARCHAR(10))
RETURNS DECIMAL(18,1)
BEGIN
DECLARE sc_val DECIMAL(18,1);
SELECT score INTO sc_val FROM sc WHERE Sno = in_sno AND Cno = in_cno LIMIT 1;
IF sc_val IS NULL THEN
RETURN 0;
END IF;
RETURN sc_val;
END //
DELIMITER ;-- 调用
sql
SELECT myf3('001','02');拓展：创建存储函数myf3k，输入学号 + 课程号，返回该生的课程信息，课程信息为："学生姓名/课程名/成绩"组成；如无姓名返回'无姓名'，无课程返回'无课程'，无成绩返回0。
sql
drop FUNCTION if EXISTS myf3k;
DELIMITER //
CREATE FUNCTION myf3k(in_sno VARCHAR(10), in_cno VARCHAR(10))
RETURNS VARCHAR(50)
BEGIN
DECLARE xm VARCHAR(10) DEFAULT '无姓名';
DECLARE kc VARCHAR(10) DEFAULT '无课程';
DECLARE cj DECIMAL(5,1) DEFAULT 0; SELECT
IFNULL(student.sname, '无姓名'),
IFNULL(course.cname, '无课程'),
IFNULL(sc.score, 0)
INTO xm, kc, cj
-- 一条 SELECT 语句，只能有 1 个 INTO，一个 INTO 后面，可以跟 N 个变量，没有数量限制
FROM student
JOIN sc ON student.Sno = sc.Sno
JOIN course ON course.Cno = sc.Cno
WHERE student.Sno = in_sno AND course.Cno = in_cno
LIMIT 1;
RETURN CONCAT(xm, '/', kc, '/', cj);
END //
DELIMITER ;
-- 调用
sql
SELECT myf3k('001','02') as 课程信息;-- 调用
sql
SELECT myf3k('101','102') as 课程信息;第 4 题 创建存储函数fn_get_teacher_name，输入教师编号，返回教师姓名；查不到返回'无教师'。
sql
DELIMITER //
CREATE FUNCTION fn_get_teacher_name(in_tno VARCHAR(10))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE tname_val VARCHAR(10);
SELECT Tname INTO tname_val FROM teacher WHERE Tno = in_tno LIMIT 1;
IF tname_val IS NULL THEN
RETURN '无教师';
END IF;
RETURN tname_val;
END //
DELIMITER ;-- 调用
sql
SELECT fn_get_teacher_name('02');第 5 题 创建存储函数fn_get_class_stu_count，输入班级号，返回该班学生人数。
sql
DELIMITER //
CREATE FUNCTION fn_get_class_stu_count(in_class CHAR(2))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE cnt INT;
SELECT COUNT(*) INTO cnt FROM student WHERE class = in_class;
RETURN cnt;
END //
DELIMITER ;-- 调用
sql
SELECT fn_get_class_stu_count('03');第 6 题 创建存储函数fn_get_score_level，输入分数，按grades表规则返回：优秀 / 良好 / 及格 / 不及格。
sql
DELIMITER //
CREATE FUNCTION fn_get_score_level(score_val DECIMAL(18,1))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
IF score_val >= 90 THEN RETURN '优秀';
ELSEIF score_val >= 75 THEN RETURN '良好';
ELSEIF score_val >= 60 THEN RETURN '及格';
ELSE RETURN '不及格';
END IF;
END //
DELIMITER ;-- 调用
sql
SELECT fn_get_score_level(87);第 7 题 创建存储函数fn_get_stu_total_score，输入学号，返回该生所有课程总分；无成绩返回0。
sql
DELIMITER //
CREATE FUNCTION fn_get_stu_total_score(in_sno VARCHAR(10))
RETURNS DECIMAL(18,1)
DETERMINISTIC
BEGIN
DECLARE total DECIMAL(18,1);
SELECT IFNULL(SUM(score),0) INTO total FROM sc WHERE Sno = in_sno;
RETURN total;
END //
DELIMITER ;-- 调用
sql
SELECT fn_get_stu_total_score('001');第 8 题 创建存储函数fn_get_course_avg_score，输入课程号，返回该课平均分（保留 2 位）；无成绩返回0。
sql
DELIMITER //
CREATE FUNCTION fn_get_course_avg_score(in_cno VARCHAR(10))
RETURNS DECIMAL(18,2)
DETERMINISTIC
BEGIN
DECLARE avg_val DECIMAL(18,2);
SELECT IFNULL(ROUND(AVG(score),2),0) INTO avg_val FROM sc WHERE Cno = in_cno;
RETURN avg_val;
END //
DELIMITER ;-- 调用
sql
SELECT fn_get_course_avg_score('01');第 9 题 创建存储函数fn_is_pass，输入学号 + 课程号，返回'及格'或'不及格'。
sql
DELIMITER //
CREATE FUNCTION fn_is_pass(in_sno VARCHAR(10), in_cno VARCHAR(10))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE sc_val DECIMAL(18,1);
SELECT score INTO sc_val FROM sc WHERE Sno = in_sno AND Cno = in_cno LIMIT 1;
IF sc_val >= 60 THEN
RETURN '及格';
ELSE
RETURN '不及格';
END IF;
END //
DELIMITER ;-- 调用
sql
SELECT fn_is_pass('004','01');第 10 题（综合题） 创建存储函数fn_get_stu_score_info，输入学号，返回字符串： 姓名:XX,总分:XX,平均分:XX,等级:XX 平均分按总分 / 科目数算，等级按平均分判定。
sql
DELIMITER //
CREATE FUNCTION fn_get_stu_score_info(in_sno VARCHAR(10))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
DECLARE s_name VARCHAR(10);
DECLARE total_score DECIMAL(18,1);
DECLARE cnt INT;
DECLARE avg_score DECIMAL(18,2);
DECLARE lev VARCHAR(10);
DECLARE result VARCHAR(100); -- 姓名
SELECT Sname INTO s_name FROM student WHERE Sno = in_sno LIMIT 1;
-- 总分
SELECT IFNULL(SUM(score),0) INTO total_score FROM sc WHERE Sno = in_sno;
-- 科目数
SELECT IFNULL(COUNT(*),0) INTO cnt FROM sc WHERE Sno = in_sno;
-- 平均分
IF cnt = 0 THEN
SET avg_score = 0;
ELSE
SET avg_score = ROUND(total_score / cnt, 2);
END IF;
-- 等级
SET lev = fn_get_score_level(avg_score);
-- 拼接
SET result = CONCAT('姓名:',s_name,',总分:',total_score,',平均分:',avg_score,',等级:',lev);
RETURN result;
END //
DELIMITER ;
-- 调用
sql
SELECT fn_get_stu_score_info('001');