【存储过程和存储函数】MySQL

存储过程和存储函数

一、实验目的

掌握通过SQL语句CREATE PROCEDURE创建存储过程的方法。
掌握使用SQL语句CALL调用存储过程的方法。
掌握使用SQL语句ALTER PROCEDURE修改存储过程的方法。
掌握使用SQL语句DROP PROCEDURE删除存储过程的方法。
掌握使用CREATE FUNCTION创建存储函数的方法。
掌握使用SQL语句ALTER FUNCTION修改存储函数的方法。
掌握使用SQL语句DROP FUNCTION删除存储函数的方法。
二、实验内容
存储过程的创建、修改、删除及调用SQL语句。
存储函数的创建、修改、删除及执行SQL语句。
三、实验步骤
输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名的学生的各科成绩。
DELIMITER @@
CREATE PROCEDURE stu_info(IN name CHAR(8) )
BEGIN
SELECT s.学号,姓名,课程编号,分数 FROM student_info s,grade g
WHERE s.学号=g.学号 AND 姓名=name;
END @@
使用CALL命令执行存储过程stu_info，其参数值为’张青平’。
DELIMITER ;
CALL stu_info();
使用studentsdb数据库中的student_info表、curriculum表、grade表。
（1）创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。
DELIMITER @@
CREATE PROCEDURE stu_grade()
BEGIN
SELECT 姓名,课程名称,分数 FROM student_info s,grade g,curriculum c
WHERE s.学号=g.学号 AND g.课程编号=c.课程编号 AND s.学号=‘0001’;
END @@
（2）调用存储过程stu_grade。
DELIMITER；
CALL stu_grade（）;
使用studentsdb数据库中的student_info表、curriculum表、grade表。
（1）创建存储过程stu_name，当任意输入一个学生的姓名时，查看其课程的最高分、最低分、平均分。
DELIMITER @@
CREATE PROCEDURE stu_name(IN name CHAR(8))
BEGIN
SELECT 姓名,MAX(分数) 最高分,MIN(分数) 最低分,AVG(分数) 平均分
FROM student_info s,grade g,curriculum c
WHERE s.学号=g.学号 AND g.课程编号=c.课程编号 AND 姓名=name;
END @@
（2）调用存储过程stu_name。

（3）删除存储过程stu_name。
DROP PROCEDURE stu_name；
4. 使用studentsdb数据库中的grade表。
（1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生选修课程的门数。
DELIMITER @@
CREATE PROCEDURE stu_g_r(IN cno CHAR(4)，OUT num IN)
BEGIN
SELECT count(*) INTO num
FROM grade
WHERE 课程编号=cno;
END @@
（2）执行存储过程stu_g_r，输入学号0002。
DELIMITER；
CALL stu_g_r(‘0002’,@num);
（3）显示0002号学生的选课门数。
SELECT @num
在这里插入图片描述

使用studentsdb数据库中的curriculum表、grade表。
（1）创建一个存储函数num_func，统计指定课程名称的选课人数。
SET GLOBAL log_bin_trust_function_creators=1;
DELIMITER @@
CREATE FUNCTION num_func(cname VARCHAR(50) CHARACTER SET utf8)
RETURNS INT
BEGIN
DECLARE num INT;
SELECT COUNT(*)INTO num FROM grade g,curriculum c
WHERE g.课程编号=c.课程编号 AND 课程名称=cname;
RETURN num;
END @@
（2）执行存储函数num_func，查看“C语言程序设计”选课人数。
使用studentsdb数据库中的curriculum表、grade表。
（1）创建一个存储函数avg_func，通过游标统计指定课程的平均分。
DELIMITER @@
CREATE FUNCTION avg_func(cname VARCHAR(50) CHARACTER SET utf8)
RETURNS DECIMAL
BEGIN
DECLARE v_avg DECIMAL;
DECLARE avg_Cur CURSOR FOR SELECT avg(分数) FROM grade g,curriculum c
WHERE g.课程编号=c.课程编号 AND 课程名称=cname;
OPEN avg_Cur;
FETCH avg_Cur INTO v_avg;
CLOSE avg_Cur;
RETURN v_avg;
END @@
（2）执行存储函数avg_func，查看“C语言程序设计”课程平均分。
SELECT avg_func(‘C语言程序设计’) 课程平均分；