存储过程触发器和函数实验.doc

存储过程、触发器和用户自定义函数实验 实验内容一 练习教材中存储过程、触发器和用户自定义函数的例子。教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。 实验内容二 针对附件1中的教学活动数据库，完成下面的实验内容。 1、存储过程 （1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。 （2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。 （3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即 A、B、C、D、E）。 （4）创建一个存储过程，该存储过程有一个参数用来接收学生姓名，该存储过程查询该学生的学号以及选修课程的门数。 （5）创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。 2、触发器 （1）为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。 （2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。 （3）为course表创建一个INSERT触发器，要求插入的课程记录中任课教师不能为空。 3、用户自定义函数 （1）创建一个返回标量值的用户定义函数 RectangleArea：输入矩形的长和宽就能计算矩形的面积。 create function RectangleArea(@a int,@b int) returns int as begin return @a*@b end （2）创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。调用这个函数，生成相应的报表并给用户浏览。 create function student_table() returns table as return( select student_course.tcid 课程号,course.cname 课程名,COUNT(student_course.sno) 选修人数,max(student_course.score) 最高分,min(student_course.score) 最低分,avg(student_course.score) 平均分 from student_course,course where student_course.tcid=course.cno group by student_course.tcid,course.cname ) 实验数据库说明 教学活动数据库包括student、course和study三个基本表，三个基本表的结构说明和数据如下： （1）学生表(student) 学生表的结构 列名 数据类型 长度 是否允许为空值 字段说明 sno char 5 NO 学号 sname char 8 NO 姓名 age smallint 年龄 sex nchar 1 性别 说明：sno为主键，age的范围为15~35之间，sex只能为“男”或“女”。 学生表的记录 sno sname age sex 98601 李强 20 男 98602 刘丽 21 女 98603 张兵 20 男 98604 陈志坚 22 男 98605 王颖 21 女 （2）课程表(course) 课程表的结构 列名 数据类型 长度 是否允许为空值 说明 cno char 4 NO 课程号 cname char 20 NO 课程名 teacher char 8 任课教师 说明：cno为主键。 课程表的记录 cno cname teacher C601 高等数学 周振兴 C602 数据结构 刘建平 C603 操作系统 刘建平 C604 编译原理 王志伟 （3）选课表(study) 选课表的结构 列名 数据类型 长度 是否允许为空值 说明 sno char 5 NO 学号 cno char 4 NO 课程号 score smallint 成绩 说明：sno和cno为主键，sno为外键（参照student表的sno），cno为外键（参照course表的cno），score的范围为0~100之间。 选课表的记录 sno cno score 98601 C601 90 98601 C602 90 98601 C603 85 98601 C604 87 98602 C601 90 98603 C601 75 98603 C602 70 98603 C604 56 98604 C601 90 98604 C604