MySQL存储过程的创建与管理（实验七）

提示：数据库任然使用xkgl脚本文件.sql
1、创建存储过程p_AvgGrade1，查询出每门课程的平均成绩。
代码：

delimiter $$
create  procedure p_AvgGrade1()
reads sql data
begin
select course.CourseName,avg(grade.Grade) from course left join grade on course.CourseID=grade.CourseID group by course.CourseID;
end
$$
delimiter ;

结果截图：

2、分别创建存储过程p_AvgGrade2（用OUT参数返回结果）和函数f_AvgGrade2，能够查询出指定课程（课程名）的平均成绩。
代码：
（1）

delimiter $$
create procedure p_AvgGrade2(in courses char(20),out avgnum decimal(5,2))
reads sql data
begin
select avg(grade.Grade) into avgnum from grade join course on grade.CourseID=course.CourseID where course.CourseName=courses;
end
$$
delimiter ;
set @name = 'JAVA程序设计';
set @avgnum = 0;
call p_AvgGrade2(@name,@avgnum);
select @avgnum;

（2）

delimiter $$
create function f_AvgGrade2(courses char(20)) returns decimal(5,2)
reads sql data
begin 
declare avgnum decimal(5,2);
select avg(grade.Grade) into avgnum from grade join course on grade.CourseID=course.CourseID where course.CourseName=courses;
return avgnum;
end;
$$
delimiter ;
select f_AvgGrade2('JAVA程序设计');

结果截图：


3、创建存储过程用out参数和select语句分别返回结果（写两个存储过程p_AvgGrade3和p_AvgGrade4），查询某个老师所授的某门课程的平均成绩和最高成绩。
代码：
（1）

delimiter $$
create  procedure p_AvgGrade3(in teacher_id char(20),in course_id char(20),out avgnum decimal(5,2))
reads sql data
begin
select distinct avg(grade.Grade) into avgnum from grade inner join schedule on grade.CourseID=schedule.CourseID inner join teacher on schedule.TeacherID=teacher.TeacherID where teacher.TeacherID=teacher_id and grade.CourseID=course_id;
end
$$
delimiter ;
set @teacher_id = 'dep01001';
set @course_id = 'Dp010003';
set @avg_grade = 0;
call p_AvgGrade3(@teacher_id,@course_id,@avg_grade);
select @teacher_id,@course_id,@avg_grade;

（2）

delimiter $$
create procedure p_AvgGrade4(in teacher_id char(20),in course_id char(20))
reads sql data
begin
select distinct teacher.TeacherID,teacher.Teachername,grade.CourseID,avg(grade.Grade) from grade inner join schedule on grade.CourseID=schedule.CourseID inner join teacher on schedule.TeacherID=teacher.TeacherID where teacher.TeacherID=teacher_id and grade.CourseID=course_id;
end
$$
delimiter ;
call p_AvgGrade4('dep01001','Dp010003');

结果截图：


4、分别调用存储过程p_AvgGrade3和p_AvgGrade4，查询dep01001号教师所授的Dp010003号课程的平均成绩和最高成绩。
代码：
（1）

set @teacher_id = 'dep01001';
set @course_id = 'Dp010003';
set @avg_grade = 0;
call p_AvgGrade3(@teacher_id,@course_id,@avg_grade);
select @teacher_id,@course_id,@avg_grade;

（2）

call p_AvgGrade4('dep01001','Dp010003');

结果截图：

5、分别创建存储过程p_Insertstu和函数f_Insertstu，实现向数据表student中插入一个新同学，并提供相应的实参值（实参值由用户自定）。
代码：
（1）

delimiter $$
create  procedure p_Insertstu(in StudentID char(12),in StudentName char(8),in Sex enum('男','女'),in Birth date,in HomeAddr varchar(80),in EntranceTime datetime,in ClassID char(8))
reads sql data
begin
insert into student values(StudentID,StudentName,Sex,Birth,HomeAddr,EntranceTime,ClassID);
end
$$
delimiter ;
call p_Insertstu('17887675468','佳木','男','1999-07-20','河南省南阳市','2018-09-01 10:39:10','Cs021001');

（2）

delimiter $$
create  function f_Insertstu(StudentID char(12),StudentName char(8),Sex enum('男','女'),Birth date,HomeAddr varchar(80),EntranceTime datetime,ClassID char(8)) returns char(20)
reads sql data
begin
declare warning char(20);
insert into student values(StudentID,StudentName,Sex,Birth,HomeAddr,EntranceTime,ClassID);
set warning = '成功';
return warning;
end
$$
delimiter ;
select f_Insertstu('S7887675468','佳木','男','1999-07-20','河南省南阳市','2018-09-01 10:39:10','Cs021001');

结果截图：
此处截图请自测！！！

6、删除存储过程p_Insertstu和函数f_Insertstu。
代码：

drop procedure p_Insertstu;
drop function f_Insertstu;

结果截图：

7、创建名为p_teacherinfo2的存储过程。要求：有两个参数：teacher id和type。满足：根据编号(teacher id)来查询teacher表中的记录。如果type的值是1时，则返回教师姓名；如果type的值是2时，则返回教师年龄；如果type为其他值，则返回字符串“Error”。
代码：

delimiter $$
create  procedure p_teacherinfo2(in teacher_id char(20),in type int,out info char(20))
reads sql data
begin
if (type=1) then select Teachername into info from teacher where TeacherID=teacher_id;
elseif (type=2) then select year(now())-year(Brith) into info from teacher where TeacherID=teacher_id;
else set info='ERROR';
end if;
end
$$
delimiter ;
set @teacher_id='dep01002';
set @type=1;
set @information='0';
call p_teacherinfo2(@teacher_id,@type,@information);
select @teacher_id,@type,@information;

结果截图：

8、调用p_teacherinfo2存储过程，返回dep01001号教师的年龄。
代码：

set @teacher_id='dep01001';
set @type=2;
set @information='0';
call p_teacherinfo2(@teacher_id,@type,@information);
select @teacher_id,@type,@information;

结果截图：

【分析与思考】
（1） 存储过程与函数的区别时什么？
答：1.存储过程处理的功能比较复杂，而函数实现的功能针对性更强一些。
2.存储过程一般是作为独立的部分来执行，而函数可以作为查询语句的一部分来调用。
3.存储过程可以返回参数，结果集。函数只能返回值或者表对象。
（2） 编程时，什么时候适合通过存储过程来实现？
答：1、当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；
2、当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；
3、还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。