存储过程和函数

【实验14-15】

(1) 创建不带参数的存储过程。
① 创建一个从student表查询班级号为“20070301”班的学生资料的存储过程proc_1，其中包括学号、姓名、性别、出生年月等。调用proc_1存储过程，观察执行结果。

CREATE PROCEDURE proc_1()
BEGIN
	SELECT sno,sname,ssex,sbirthday
	FROM student
	WHERE classno='20070301';
END;
CALL proc_1();

② 在gradem1数据库中创建存储过程proc_2，要求实现如下功能：存在不及格情况的学生选课情况列表，其中包括学号、姓名、性别、课程号、课程名、成绩、系别等。调用proc_2存储过程，观察执行结果。

CREATE PROCEDURE proc_2()
BEGIN
	SELECT a.sno,sname,ssex,b.cno,cname,degree,sdept
	FROM student a,sc b,course c
	WHERE a.sno=b.sno AND b.cno=c.cno
	AND degree<60;
END;
CALL proc_2();

(2) 创建带输入参数的存储过程。
创建一个从student表查询学生资料的存储过程 proc_3，其中包括学号、姓名、性别、出生年月、班级等。要查询的班级号通过执行语句中的输入参数传递给存在过程。
其中，“20070303”为要传递给存储过程proc_3的输入参数，也即是要查询的资料的班级号。

CREATE PROCEDURE proc_3(IN classno_no INT,OUT class_no VARCHAR(20))
BEGIN
	SELECT sno,sname,ssex,sbirthday,classno
	FROM student
	WHERE classno=classno_no;
END;
CALL proc_3('20070303',@sum); 

(3) 创建带输出参数的存储过程。
创建一个从sc表查询某一门课程考试成绩总分的存储过程proc_4。
在以上存储过程中，要查询的课程号通过执行语句中的输入参数@cno传递给存储过程，@sum_degree作为输出参数用来存放查询得到的总分。执行此存储过程，观察执行结果。

CREATE PROCEDURE proc_4(IN `@cno` char(10),OUT `@sun_degree` VARCHAR(20))
	BEGIN
		SELECT SUM(degree)
		FROM sc
		WHERE cno=@cno;
	END;
CALL proc_4('a01',@sun_degree);

这个答案是空的，不知道哪里错了

(4) 创建proc_5实现功能：输入的学号，根据该生所选课程的平均分显示提示信息，平均分大于等于90的，则显示“该生成绩优秀”，平均分小于90但是大于等于80的，则显示“该生成绩良好”，平均分小于80但是大于等于60的，则显示“该生成绩合格”，平均分小于60的，则显示“该生成绩不及格”。
并调用该存储过程，显示某个学生的学习情况。

CREATE PROCEDURE proc_5(IN sno_no INT)
BEGIN
	SELECT CASE
	WHEN AVG(degree)>=90 THEN '改考生成绩优秀'
	WHEN AVG(degree)>=80 AND degree<90 THEN '改考生成绩良好'
	WHEN AVG(degree)>=60 THEN '改考生成绩合格'
	WHEN AVG(degree)<60 THEN '改考生成绩不合格'
	END
	AS '成绩'
	FROM sc
	WHERE sno=sno_no;
END;
CALL proc_5('2007010104');

(5) 创建func_5实现功能：输入的学号，根据该生所选课程的平均分显示提示信息，平均分大于等于90的，则显示“该生成绩优秀”，平均分小于90但是大于等于80的，则显示“该生成绩良好”，平均分小于80但是大于等于60的，则显示“该生成绩合格”，平均分小于60的，则显示“该生成绩不及格”。并调用该函数，显示某个学生的学习情况。

CREATE FUNCTION func_5(sno_no INT)
RETURNS VARCHAR(20)
BEGIN
	DECLARE a VARCHAR(20);
	SELECT CASE
	WHEN AVG(degree)>=90 THEN '改考生成绩优秀'
	WHEN AVG(degree)>=80 AND degree<90 THEN '改考生成绩良好'
	WHEN AVG(degree)>=60 THEN '改考生成绩合格'
	WHEN AVG(degree)<60 THEN '改考生成绩不合格'
	END AS 成绩 INTO a 
	FROM sc
	WHERE sno=sno_no;
	RETURN a;
END;
SELECT func_5('2007010104');