同济大学数据库作业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表中对应插入或更新的记录
三．实验体会或收获（有感而发，没有体会也可以不写）。