数据库技术与应用实验五视图存储过程和触发器.docx

《数据库技术与应用实验五视图存储过程和触发器.docx》由会员分享，可在线阅读，更多相关《数据库技术与应用实验五视图存储过程和触发器.docx（7页珍藏版）》请在三一文库上搜索。

1、/* 实验五 视图、存储过程和触发器 */-1. 定义视图并在视图上完成数据查询功能-（1） 在UNIVERSITY数据库上创建视图ViewA,完成“查询有选课记录的学生学号、课程号、课程名称、成绩”的数据搜索功能。CREATE VIEW ViewAAS SELECT snum 学号, um 课程号, cname 课程名称, score 成绩FROM sc JOIN sections ON sc.secnum =sections.secnum JOIN course ON um=um-（2） 在上述视图的基础上完成查询：查询所有学生都及格的课程名称SELECT 课程名称 FROM ViewA

2、GROUP BY 课程名称HAVING MIN(成绩)=60-2. 存储过程的建立和执行-（1）建立存储过程ProcA,其功能为显示所有学生的基本信息；CREATE PROC ProcAAS SELECT *FROM student-执行ProcAEXEC ProcA -(2)建立存储过程ProcB，其功能是查询出给定出生年份信息（已知出生日期的前四位信息）的学生信息。CREATE PROC ProcB_year char(4)ASSELECT *FROM studentWHERE year(birthday)=_year-执行ProcBDECLARE year char(4)SET year

3、1994EXEC ProcB year-(3)建立存储过程ProcC,其功能是查询给定学号的学生的课程平均成绩、选修课程的门数和不及格课程门数CREATE PROC ProcC_snum char(4),_avg int out,_selected_course int out,_failed_course int outASSELECT _avg=AVG(score),_selected_course=COUNT(cnum),_failed_course=sum(1-score/60)FROM sc JOIN sections ON sc.secnum =sections.secnum W

4、HERE snum=_snum-执行ProcCDECLARE snum char(4),avg int,selectedCourse int,failedCourse intSET snum=s001EXEC ProcC snum,avg out,selectedCourse out,failedCourse outPRINT snum + 学生的平均成绩 + cast(avg as char(2) + ,选课门数： + cast(selectedCourse as char(2) + ,不及格门数： + cast(failedCourse as char(2)-3.建立一组触发器，并设计一组

5、必要的数据操作验证触发器的功能-（1）自定义一个触发器TA,完成选课表SC属性snum的参照完整性控制。CREATE TRIGGER TA ON SCFOR INSERT,UPDATEASDECLARE _snum char(4)SELECT _snum=inserted.snum FROM inserted IF not EXISTS(SELECT * FROM Student WHERE student.snum = _snum) BEGIN RAISERROR(违反参照完整性约束！操作回滚！,1,1,error) ROLLBACK ENDELSE PRINT 操作成功执行！ -测试触发器

6、前，应将上关于的外键删除-测试用例一，插入的学号不存在 INSERT INTO SC VALUES(S009,11601,89)-测试用例二，更新的学号不存在UPDATE SC SET snum=s101 where snum=s001 and secnum=11601-测试用例三，插入学号和课程号都存在INSERT INTO SC VALUES(S005,12601,87) -测试用例四，更新的学号存在UPDATE SC SET snum=s007 where snum=s005 and secnum=12601-（2）自定义一个触发器TB，完成学生表student的完整性约束。- 具体要求

7、如果年龄超出1435岁，则报“年龄越界！”错误提示信息，否则显示“数据录入成功！”CREATE TRIGGER TB ON studentFOR INSERT,UPDATEASDECLARE _age intSELECT _age = DATEDIFF(YEAR,birthday,GETDATE() FROM inserted IF _age 35 BEGIN RAISERROR(年龄越界！,13,1,error) ROLLBACK ENDELSE PRINT 数据录入成功！ -测试用例一，插入1920-1-1（年龄越界）出生的一个新记录 INSERT INTO student VALUES

8、s009,邓荣功,男,计算机,1920-1-1,023-12345678)-测试用例二，插入1995-1-1出生的一个新记录 INSERT INTO student VALUES(s009,邓荣功,男,计算机,1995-1-1,023-12345678)-测试用例三，将s009的生日更新为1920-1-1（越界） UPDATE student SET birthday=1920-1-1 WHERE snum=s009 -测试用例四，将s009的生日更新为1996-1-1（合规则） UPDATE student SET birthday=1996-1-1 WHERE snum=s009 /*（

9、3）自定义一个触发器TC，完成课程表course的完整性约束。 具体要求：如果课程订购的教材不是高等教育出版社、清华大学出版社、复旦大学出版社和同济大学出版社出版的，则报“不是指定出版社，不能订购！”错误提示信息，否则显示“订购成功！”信息。 */- 答案一CREATE TRIGGER TC ON courseFOR INSERT,UPDATEASIF NOT EXISTS(SELECT * FROM inserted WHERE Right(Rtrim(textbook),7) in(高等教育出版社, 清华大学出版社,复旦大学出版社,同济大学出版社) BEGIN RAISERROR(不是指定

10、出版社，不能订购！,13,1,ERROR) ROLLBACK ENDELSE PRINT 订购成功！- 答案二CREATE TRIGGER TC ON courseFOR INSERT,UPDATEASDECLARE textbook varchar(40)SELECT textbook=textbook FROM insertedIF Right(Rtrim(textbook),7) not in(高等教育出版社, 清华大学出版社,复旦大学出版社,同济大学出版社) BEGIN RAISERROR(不是指定出版社，不能订购！,13,1,ERROR) ROLLBACK ENDELSE PRINT

11、 订购成功！-答案三CREATE TRIGGER TC ON courseFOR INSERT,UPDATEASIF EXISTS(SELECT * FROM inserted WHERE textbook like %高等教育出版社% or textbook like %清华大学出版社% or textbook like %复旦大学出版社% or textbook like %同济大学出版社%) PRINT 订购成功！ELSE BEGIN RAISERROR(不是指定出版社，不能订购！,13,1,ERROR) ROLLBACK END-答案四CREATE TRIGGER TC ON cour

12、seFOR INSERT,UPDATEASDECLARE textbook varchar(40)SELECT textbook=textbook FROM insertedIF textbook like %高等教育出版社% or textbook like %清华大学出版社% or textbook like %复旦大学出版社% or textbook like %同济大学出版社% PRINT 订购成功！ELSE BEGIN RAISERROR(不是指定出版社，不能订购！,13,1,ERROR) ROLLBACK END- 测试用例一，插入电子工业出版社（非法）的一个新课程：c137,机器

13、学习，4，必修课，计算机系，机器学习，电子工业出版社INSERT INTO course VALUES(c137,机器学习,4,必修课,计算机系,机器学习，电子工业出版社)- 测试用例二，插入高等教育出版社（合法）的一个新课程：c137,机器学习，4，必修课，计算机系，机器学习，高等教育出版社INSERT INTO course VALUES(c137,机器学习,4,必修课,计算机系,机器学习，高等教育出版社)/* (4)自定义一个触发器TD，完成选课表SC的数据完整性控制。即当用户在选课表中插入或更新一条选课记录时，如果同一个学号的选课记录中， 出现了同一门课程的多个班号，则直接删除SC表中

14、的最新插入或更新的记录；否则提交SC表中对应插入或更新的记录。（选做题） */CREATE TRIGGER TD ON SCFOR INSERT,UPDATEASDECLARE _cnum char(4),_snum char(4),_i intSELECT _cnum=cnum,_snum=snum FROM inserted JOIN sections ON inserted.secnum =sections.secnum SELECT _i=count(*) FROM SC JOIN sections on sc.secnum =sections.secnum AND snum=_snu

15、m AND cnum=_cnumIF _i 1 BEGIN RAISERROR(该课程已选！操作被撤消！,13,1,error) ROLLBACK ENDELSE PRINT 操作成功！ GO-测试用例一，插入同课号记录（s001,11602,85）,非法INSERT INTO SC VALUES(s001,11602,85)-测试用例二，更新记录（s001,11601,77）,更新其班号为12002,已选非法UPDATE SC SET secnum=12002 WHERE snum=s001 and secnum=11601-测试用例三，插入记录（s006,13001,85）,合法INSERT INTO SC VALUES(s006,13001,85)-测试用例四，更新记录（s005,11602,77）,更新其班号为12601,未选合法UPDATE SC SET secnum=12601 WHERE snum=s005 and secnum=11602GO