同济大学数据库作业lab5.docVIP

同济大学

《数据库技术及应用》

实验报告

实验报告题目：

视图，存储过程和触发器

姓名：学号：

年级：专业：

指导教师：

日期：2014年10月27日

实验目的

学会视图的建立和基于视图的数据库建立

学会存储过程的建立和存储方法

学会触发器的建立和使用方法，通过实验数据的操作过程了解应用触发器实现数据库完整性控制的设计过程

实验内容

（实验题目+运行界面截图+实现代码）

1.（1）创建视图viewa，查询有选课记录的学生号，课程号，课程名称。成绩。

createviewviewA

as

selectstudent.snum,sc.secnum,course.cname,sc.score

fromstudent,sc,sections,course

wherestudent.snum=sc.snumandsc.secnum=sections.secnumandsections.cnum=course.cnum

（2）在上述视图的基础上查询所有学生都及格的课程名称

selectcname

fromviewA

groupbycname

havingmin(score)60

2.存储过程的建立和执行

（1）建立存储过程proca，其功能是显示所有学生的基本信息

createprocproca

as

select*

fromstudent

execproca

（2）建立procb，查询出给定出生年份信息的学生信息

createprocprocb

@_yearint

as

select*fromstudent

whereyear(birthday)=@_year

declare@yint

set@y=1994

execprocb@y

（3）建立存储过程procc，查询给定学好的学生的课程平均成绩，选修课程的门数和不及格课程的门数

createprocprocc

@_xhchar(4)

as

selectavg(score)as平均成绩,count(1-score/60)as不及格课程门数,count(score)as选修课程门数

fromsc

wheresnum=@_xh

declare@xh1char(4)

set@xh1=s007

execprocc@xh1

3.建立一组触发器，并设计一组必要的数据操作验证其功能

（1）自定义一个触发器ta，完成选课表sc属性snum参照完整性控制

createtriggerxhysonsc

forinsert,update

as

begin

if((selectsnumfrominsertedins)notin(selectsnumfromstudent))

begin

print违反参照完整规则

rollback

end

end

（2）自定义tb，完成student的完整性约束，具体要求是如果年龄超出14-35岁，则报“年龄越界”否则显示数据录入成功

CREATETRIGGERT_age_student

ONstudent

FORINSERT

AS

DECLARE@_snumASCHAR(4)

SELECT@_snum=i.snumFROMinsertedI

DECLARE@ageASINTEGER

SELECT@age=year(getdate()-year(birthday))

FROMstudent

WHEREsnum=@_snum

IF@age=14and@age=35

BEGIN

PRINT年龄越界！

ROLLBACKTRAN

END

ELSE

PRINT数据录入成功！

（3）自定义tc，完成课程表course的完整性约束，如果课程订购的教材不是高等教育出版社，不能订购，否则报订购成功的信息

createtriggertc_course

oncourse

forinsert

as

declare@_textboxvarchar(40)

select@_textbox=i.textboxfrominser