数据库原理及应用实验报告 7

实验成绩
《数据库系统原理及应用》
实
验
报
告
七
专业班级: 计算机科学与技术
学号: 201116910233
姓名: 范晓曈
指导教师: 苏小玲
2013年11 月28 日
实验七名称：SQL Server触发器和游标
一、实验内容及要求
（使用教材建立的student、course和sc表实现）
1、创建满足下述要求的DML触发器（前触发器和后触发器均可），并验证触发器执行情况。

任务1：限制学生所在系的取值范围为｛计算机系，信息管理系，数学系，通信工程系｝。

任务2：限制每个学期所开设的课程总学分在20~30范围内。

任务3：限制每个学生每学期选课门数不能超过6门（设只针对单行插入操作）
任务4：限制不能删除有人选的课程
2.创建满足下述要求的游标
任务1：查询java课程的考试情况，并按教材11-19所示样式显示结果数据。

任务2：统计每个系的男生人数和女生人数，并按教材11-20所示样式显示结果数据。

任务3：列出每个系的学生信息，要求首先列出一个系的系名，然后在该系名下列出本系学生的姓名和性别，以此类推，直至列出全部系。

要求按教材11-21所示样式显示结果数据。

二、实验目的
掌握触发器的概念；掌握触发器创建方法；掌握游标的概念；掌握游标的使用方法。

三、实验步骤
创建满足下述要求的DML触发器（前触发器和后触发器均可），并验证触发器执行情况。

任务1：限制学生所在系的取值范围为｛计算机系，信息管理系，数学系，通信工程系｝。

create trigger tri_limitedept
on student after insert,update
as
IF EXISTS(SELECT*FROM INSERTED
WHERE sdept!='计算机'and sdept!='信息管理系'
and sdept!='数学系'and sdept!='通信')
ROLLBACK
insert into student
values(001,'一','女','通信工程系','1991-01-01','JA V A','5.0')
任务2：限制每个学期所开设的课程总学分在20~30范围内。

create trigger tri_limit
on course after insert,update
as
if exists(select sum(credit)from course
where semester in(select semester from inserted)having sum(credit)not between 20 and 30 )
print'你所插入的课程所在学期的总学分不在~30这个范围内'
Rollback
insert into Course
values('C007','0007','007','JA V A',3,2)
任务3：限制每个学生每学期选课门数不能超过6门（设只针对单行插入操作）
create trigger tri_count
ON SC after INSERT
as
if(select count(*)from sc where sno=(select sno from inserted))>4
begin
print'选课超过限额'
rollback
End
insert into sc
values(201116910231,'C005','2013.09.01',85,75)
任务4：限制不能删除有人选的课程
create trigger tri_Deletekc
ON course AFTER DELETE
AS
IF EXISTS(SELECT*FROM sc join DELETED d on o=o)
BEGIN
PRINT'不能删除有学生选的课程'
ROLLBACK
END
delete from course where cno='C001'
2.创建满足下述要求的游标
任务1：查询java课程的考试情况，并按教材11-19所示样式显示结果数据。

declare@name varchar(200),@dept varchar(200),@sscore varchar(10)--声明存放结果
集的变量
declare java_cursor cursor for--声明游标
select s.sname,s.sdept,sc.sscore
from student s join sc on s.sno=sc.sno
join course c on o=o
where cname='java'
open java_cursor--打开游标
fetch next from java_cursor into@name,@dept,@sscore--首先提取第一行
print'选了JA V A课程的学生的成绩：'
print'姓名所在系成绩'
while@@FETCH_STA TUS=0
begin
print@name+' '+@dept+' '+@sscore
fetch next from java_cursor into@name,@dept,@sscore
end
close java_cursor
deallocate java_cursor
任务2：统计每个系的男生人数和女生人数，并按教材11-20所示样式显示结果数据。

declare@dept char(20),@sex char(20),@number int
declare cur_number cursor for
select sdept,ssex,count(*)
from student s group by ssex,sdept
open cur_number
fetch next from cur_number into@dept,@sex,@number
print'系名性别人数'
print'==================================='
while@@FETCH_STA TUS=0
begin
print@dept+@sex+cast(@number as varchar)
fetch next from cur_number into@dept,@sex,@number
end
close cur_number
deallocate cur_number
任务3：列出每个系的学生信息，要求首先列出一个系的系名，然后在该系名下列出本系学生的姓名和性别，以此类推，直至列出全部系。

要求按教材11-21所示样式显示结果数据。

declare@dept varchar(20),@name varchar(20),@sex varchar(20)
declare c1cursor for
select distinct sdept from student
open c1
fetch next from c1into@dept
while@@FETCH_STA TUS=0
begin
print@dept+'系的学生:'
declare c2cursor for
select sname,ssex from student where sdept=@dept
open c2
fetch next from c2into@name,@sex
while@@FETCH_STA TUS=0
begin
print@name+@sex
fetch next from c2into@name,@sex
end
close c2
deallocate c2
print'=============='
print''
fetch next from c1into@dept
end
close c1
deallocate c1
四、实验心得
在实验开始之初，由于对之前所学的知识有所忘记，所以我选择首先复习之前的select语句的相关知识。

在进行触发器的相关实验时，遇到一些令我迷惑的问题例如：限制每个学生每学期选课门数不能超过6门。

由于有多重限制条件，后来在同学的讲解下利用declare解决了。

感觉触发器太神奇了，操作者做了不符合条件的操作就可以自动警告。

在做游标的相关实验时，相对简单。

油表可以对查询的数据逐行逐列的操作，方便简洁。

知识输出的结果至今无法整齐对照，令人费解。