实验八(上) 3110305129 潘聪.doc

数据库系统概论实验报告实验题目：实验八（上） 用户自定义函数和触发器日期2013.6.18班级计算机1105姓名潘聪3110305129实验环境：SQL Server 2005实验目的:1、掌握SQLServer中用户自定义函数的使用方法。2、掌握SQL Server中触发器的使用方法。实验内容:1. 创建一个返回标量值的用户定义函数 RectangleArea：输入矩形的长和宽就能计算矩形的面积。自选2种实例调用该函数。create function RectangleArea(a int,b int) returns intasbeginreturn a*benddeclare area intexecute area=RectangleArea 3,5print (矩形面积是：)print areadeclare area intexecute area=RectangleArea 7,8print (矩形面积是：)print area2. 创建一个用户自定义函数（内嵌表值函数），功能为产生某个系的学生选修信息，内容为学号，姓名，课程名，成绩。调用这个函数，显示信息系有选课学生的信息。create function Search (sdept char(10) returns tableasreturn (select sc.sno 学号,student.sname 姓名,ame 课程名,sc.grade 成绩,student.sdept 系别from sc,student,course where o=o and sc.sno = student.sno and sdept=sdept)select * from Search(cs)3. 创建一个作用在P表上的触发器P_checks，确保用户在插入或更新P表的WEIGHT值时，所提供的WEIGHT值介于20与40之间，否则给出错误提示并回滚此操作。请测试该触发器，测试方法自定。create trigger P_checks on p for insert asbegindeclare weight intselect weight=weight from insertedif weight20begin RAISERROR(weight 必须在20之间！,16,1)ROLLBACK TRANSACTIONendendinsert into p(pno,pname,color,weight) values(p7,刀片,红,40)insert into p(pno,pname,color,weight) values(p7,刀片,红,15)select * from p4. 创建一个作用在J表上的触发器J_Update，禁止同时修改项目的名称和所在城市，并进行相应的错误提示。请测试该触发器。测试方法自定。create trigger J_Update on j for updateasbegindeclare jname1 char(10),city1 char(10),jname2 char(10),city2 char(10) select jname1=jname,city1= city from insertedselect jname2=jname,city2= city from deletedif jname1jname2 and city1city2beginRAISERROR(不能同时修改项目名称和项目地点！,16,1)ROLLBACK TRANSACTIONendendupdate j set jname=建筑 ,city=上海 where jno=j1update j set jname=建筑 where jno=j1select * from j5. 学生表（Student）中存放学生的记录，学生选修表 (SC) 中存放学生的修课及成绩情况。创建一个触发器ScDel_Cascade，当删除Student中的数据时，数据表SC中有关刚刚删除学生的修课成绩信息也能被级联删除掉。测试该触发器，测试方法自定。create trigger ScDel_Cascade on student for deleteasbegindeclare sno char(10)select sno=sno from deleteddelete from sc where sno=sno enddelete from student where sname=李勇select * from scselect * from student附加题：创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。调用这个函数，生成相应的报表并给用户浏览。(多语句表值函数)显示形式如下：Cno Cname SC_number Max_grade Min_grade Average_grade- - - - - -1 数据库 3 92 92 92.02 数学 2 85 80 82.03 信息系统 1 88 88 88.04 操作系统 1 NULL NULL NULL5 数据结构 1 NULL NULL NULL6 数据处理 2 55 55 55.07 PASCAL语言 1 NULL NULL NULL(7 行受影响)select * from sc,student,course where o = o and student.sno = sc.snocreate function studentgrade() returns tableasreturn(select o Cno,ame Cname, count(sc.sno) SC_number, max(sc.grade) Max_grade, min(sc.grade) Min_grade, avg(sc.grade) Average_gradefrom sc,student,course where sc.sno = student.sno and o = o group by o,ame)select * from studentgrade()出现的问题： 本实验中，主要学习和运用了自定义函数，经过这次实验对数据库的学习更加有浓厚的兴趣了，数据库编程也是非常有意思的一门学问，对sqlserver的理解又深入了一层！解决方案（列出遇到的主要问题和解决办法，列出没有解决的问题）：与任何函数一样，用户定义函数是可返回值的例程。根据所返回值的类型，每个用户定义函数可分成以下三个类别：1、返回可更新数据表的函数：如果用