存储过程和存储函数

一

1.输入以下代码，创建存储过程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('张青平');

2.使用studentsdb数据库中的student_info表、curriculum表、grade表。

（1）创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。
（2）调用存储过程stu_grade。

DELIMITER @@
CREATE PROCEDURE stu_grade(IN number CHAR(4))
BEGIN
select s.姓名,c.课程名称,g.分数 from curriculum c,grade g ,student_info s
where g.课程编号=c.课程编号 and s.学号=g.学号 and g.学号=number;
END @@

DELIMITER ;
CALL stu_grade('0001');

3.使用studentsdb数据库中的student_info表、curriculum表、grade表。

（1）创建存储过程stu_name，当任意输入一个学生的姓名时，查看其课程的最高分、最低分、平均分。
（2）调用存储过程stu_name。
（3）删除存储过程stu_name。

DELIMITER @@
CREATE PROCEDURE stu_name(IN name CHAR(10))
BEGIN

select s.姓名,max(g.分数) as 最高分,min(g.分数) as 最低分,avg(g.分数) as 平均分 from curriculum c,grade g ,student_info s
where g.课程编号=c.课程编号 and s.学号=g.学号 and s.姓名=name;

END @@

DELIMITER ;
CALL stu_name('刘东阳');

drop PROCEDURE stu_name;

4.使用studentsdb数据库中的grade表。

（1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生选修课程的门数。
（2）执行存储过程stu_g_r，输入学号0002。
（3）显示0002号学生的选课门数。

DELIMITER @@
CREATE PROCEDURE stu_g_r(IN number CHAR(4))
BEGIN
select count(课程编号) as 选修课门数 from grade 
where 学号=number;
END @@

DELIMITER ;
CALL stu_g_r('0002');

5.使用studentsdb数据库中的curriculum表、grade表。

（1）创建一个存储函数num_func，统计指定课程名称的选课人数。
（2）执行存储函数num_func，查看“C语言程序设计”选课人数。

DELIMITER @@
CREATE PROCEDURE num_func(IN name CHAR(10))
BEGIN
select count(g.学号) as 选课人数 from grade g
where 课程编号=(select 课程编号 from curriculum where 课程名称=name );
END @@

DELIMITER ;
CALL num_func('C语言程序设计');

6.使用studentsdb数据库中的curriculum表、grade表。

解决问题：

执行：set global log_bin_trust_function_creators=1

问题解决

（1）创建一个存储函数avg_func，通过游标统计指定课程的平均分。

DELIMITER @@
CREATE FUNCTION avg_func(name CHAR(10))
RETURNS DECIMAL
   BEGIN
      DECLARE avg_c DECIMAL;#定义变量存取数据
		  DECLARE avg_y CURSOR #声明游标
		   FOR SELECT AVG(g.分数) FROM curriculum c,grade g WHERE g.课程编号=c.课程编号 AND  课程名称=name;
		  OPEN avg_y;#打开游标
		  FETCH avg_y INTO avg_c;##提取游标
		  CLOSE avg_y;#关闭游标
		  RETURN avg_c;#返回数据
	 END @@

（2）执行存储函数avg_func，查看“C语言程序设计”课程平均分。

DELIMITER ;
CALL num_func('C语言程序设计');

（3）删除存储函数avg_func。

DROP FUNCTION avg_func;

二、实验思考

1.存储函数和存储过程如何将运算结果返回给外界？

存储函数可以通过return语句返回函数值，存储过程通过out inout 参数将结果带出
存储函数和存储过程如何将运算结果返回方式：
    1.通过return语句返回值
    2.通过out、inout 参数将结果带出

2.存储函数有OUT参数、INOUT参数吗？

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型：IN，OUT，INOUT
格式为：Create procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])

1.IN 输入参数——> 表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

2.OUT 输出参数——> 该值可在存储过程内部被改变，并可返回

3.INOUT 输入输出参数——> 调用时指定，并且可被改变和返回

3.使用游标的步骤。

• 声明游标
• 打开游标
• 提取数据
• 关闭游标