文档介绍：存储过程、触发器和用户自定义函数实验兰州大学数据库实验报告实验内容一练****教材中存储过程、触发器和用户自定义函数的例子。教材中的 BookSales 数据库, 在群共享中,文件名为。实验内容二针对附件 1 中的教学活动数据库,完成下面的实验内容。 1 、存储过程(1 )创建一个存储过程,该存储过程统计“高等数学”的成绩分布情况,即按照各分数段统计人数。 CREATE Proc MATH_NUM ***@MATH CHAR ( 20 )= ' 高等数学' AS SELECT ***@MATH as canme , count ( case when score >= 90 then 1 end ) as [90 以上], count ( case when score >= 80 and score < 90 then 1 end ) as [80-90] , count ( case when score >= 70 and score < 80 then 1 end ) as [70-80] , count ( case when score >= 60 and score < 70 then 1 end ) as [60-70] , count ( case when score < 60 then 1 end ) as [60 以下] FROM study , course WHERE study . cno = course . cno and course . cname = ***@MATH GROUP BY course . cname 运行结果: (2 )创建一个存储过程,该存储过程有一个参数用来接收课程号,该存储过程统计给定课程的平均成绩。 CREATE Proc AVG_SCORE ***@cno CHAR (5) AS SELECT ***@cno as 课程号, course . cname as 课程名, STR ( AVG ( score ), 5,2) as 平均成绩 FROM study , course WHERE study . cno = course . cno and course . cno = ***@cno GROUP BY course . cname 运行结果: (3 )创建一个存储过程,该存储过程将学生选课成绩从百分制改为等级制(即 A、B、C、 D、E)。 CREATE Proc SCORE_CHANGE AS SELECT course . cname as 课程名, study . sno as 学号, study . cno as 课程号, study . score as 成绩, case when score >= 90 and score <= 100 then 'A' when score >= 80 and score < 90 then 'B' when score >= 70 and score < 80 then 'C' when score >= 60 and score < 70 then 'D' when score < 60 then 'E' end as ' 等级' from study , course where study . cno = course . cno 运行结果: (4 )创建一个存储过程,该存储过程有一个参数用来接收学生姓名,该存储过程查询该学生的学号以及选修课程的门数。 CREATE Proc STUDENT_STUDY ***@name char (8) AS select ***@name as 姓名, study . sno as 学号, count ( cno ) as 选修门数 from study , student where study . sno = student . sno and sname = ***@name group by study . sno 运行结果: (5 )创建一个存储过程,该存储过程有两个输入参数用来接收学号和课程号,一个输出参数用于获取相应学号和课程号对应的成绩。 CREATE Proc STU_COR_SCORE ***@sno char (5 ), ***@cno char (4 ), ***@word smallint output AS select ***@word = score from study where sno = ***@sno o = ***@cno 运行结果: 2 、触发器(1)为 study 表创建一个 UPDATE 触发器, 当更新成绩时, 要求更新后的成绩不能低于原来的成绩。 CREATE TRIGGER UPDATE_SCORE ON study instead of update as decla