同济大学数据库作业lab5

同济大学

《数据库技术及应用》

实验报告

实验报告题目：

视图，存储过程和触发器

姓名：学号：

年级：专业：

指导教师：

日期：2014 年10 月27 日

一．实验目的

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

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

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

二．实验内容

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

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

create view viewA

as

select student.snum,sc.secnum,ame,sc.score

from student,sc,sections,course

where student.snum=sc.snum and sc.secnum=sections.secnum and um=um

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

from viewA

group by cname

having min(score)>60

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

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

create proc proca

as

select*

from student

exec proca

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

create proc procb

@_year int

as

select*from student

where year(birthday)=@_year

declare@y int

set@y=1994

exec procb@y

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

create proc procc

@_xh char(4)

as

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

from sc

where snum=@_xh

declare@xh1char(4)

set@xh1='s007'

exec procc@xh1

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

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

create trigger xhys on sc

for insert,update

as

begin

if((select snum from inserted ins)not in(select snum from student))

begin

print'违反参照完整规则'

rollback

end

end

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

CREATE TRIGGER T_age_student

ON student

FOR INSERT

AS

DECLARE@_snum AS CHAR(4)

SELECT@_snum=i.snum FROM inserted I

DECLARE@age AS INTEGER

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

FROM student

WHERE snum=@_snum

IF@age>=14 and@age<=35

BEGIN

PRINT'年龄越界！'

ROLLBACK TRAN

END

ELSE

PRINT'数据录入成功！'

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

create trigger tc_course

on course

for insert

as

declare@_textbox varchar(40)

select@_textbox=i.textbox from inserted i

if not exists(select*from course where textbox like'%同济大学出版社%'or textbox like '%复旦大学出版社%'or textbox like'%清华大学出版社%'or textbox like'%高等教育出版社%') begin

print'不是指定出版社，不能订购'

rollback

end

else

begin

print'订购成功'

end

（4）自定义td，完成sc的数据完整性控制。即当用户在选择课表中插入或更新一条选课记录时，如果同一个学号的选课记录也在其中，出现了同一门课程多个班号的情况，则直接删除sc表中最新插入或更新的记录，否则提交sc表中对应插入或更新的记录

三．实验体会或收获（有感而发，没有体会也可以不写）