存储过程、触发器和用户自定义函数实验讲解.doc

存储过程、触发器和用户自定义函数实验 实验内容一 练习教材中存储过程、触发器和用户自定义函数的例子。教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。 针对附件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 （3A、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创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。 （1）为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。 CREATE TRIGGER UPDATE_SCORE ON studyinstead of updateasdeclare @sno2 char(5),@cno2 char(4),@score1 smallint,@score2 smallint select @sno2=sno,@cno2=cno,@score2=scorefrom insertedselect @score1=scorefrom deletedif(@score2=@score1)update study set score=@score2where study.cno=@cno2 and study.sno=@sno2go （2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。 CREATE TRIGGER DEL_STUDY ON studyinstead of DELETEASbegindeclare @num int,@sno char(5),@cno char(4)select @num=COUNT(*)from deletedif @num=1beginselect @sno=sno,