存储过程、触发器和用户自定义函数(存储过程)实验总汇.doc

存储过程、触发器和用户自定义函数实验 实验内容一 练习教材中存储过程、触发器和用户自定义函数的例子。教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。 实验内容二 针对附件1中的教学活动数据库，完成下面的实验内容。 1、存储过程 （1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。 CREATE Proc MATH_NUM @MATH CHAR(20)=高等数学ASSELECT @MATH as canme,count(case when score=90 then 1 end)as[90以上], count(case when score=80 and score90 then 1 end)as[80-90],count(case when score=70 and score80 then 1 end)as[70-80],count(case when score=60 and score70 then 1 end)as[60-70],count(case when score60 then 1 end)as[60以下] FROM study,courseWHERE study.cno=course.cno and course.cname=@MATHGROUP BY course.cname （2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。 CREATE Proc AVG_SCORE @cno CHAR(20)ASSELECT @cno as 课程号,course.cname as 课程名,STR(AVG(score),5,2) as 平均成绩FROM study,courseWHERE study.cno=course.cno and course.cno=@cnoGROUP BY course.cname （3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即 A、B、C、D、E）。 CREATE Proc SCORE_CHANGEASSELECT course.cname as 课程名,study.sno as 学号,study.cno as 课程号,study.score as 成绩,casewhen score=90 and score=100 thenAwhen score=80 and score90 thenBwhen score=70 and score80 thenCwhen score=60 and score70 thenDwhen score60 thenEend as 等级from study,coursewhere study.cno=course.cno （4）创建一个存储过程，该存储过程有一个参数用来接收学生姓名，该存储过程查询该学生的学号以及选修课程的门数。 CREATE Proc STUDENT_STUDY @name varchar(20)ASselect @name as 姓名,study.sno as 学号,count(cno) as 选修门数from study,studentwhere study.sno=student.sno and sname=@namegroup by study.sno （5）创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。 CREATE Proc STU_COR_SCORE @sno varchar(20),@cno char(20),@word smallint output ASselect @word=scorefrom studywhere sno=@sno and cno=@cno 实验数据库说明 教学活动数据库包括student、course和study三个基本表，三个基本表的结构说明和数据如下： （1）学生表(student) 学生表的结构 列名 数据类型 长度 是否允许为空值 字段说明 sno char 5 NO 学号 sname char 8 NO 姓名 age smallint     年龄 sex nchar 1   性别   说明：sno为主键，age的范围为15~35之间，sex只能为“男”或“女”。 CREATE TABLE student ( age smallint check (age = 15 and age = 35), sex varchar(20) check (sex in(男,女)) --性别sno varchar(20) PRIMA