数据库课程设计实验
- 格式：doc
- 大小：118.22 KB
- 文档页数：7
实验5 存储过程与触发器
1.实验目的与要求：
1.1 掌握存储过程使用
1.2 掌握触发器使用
1.3 掌握游标使用
2.实验环境与实验器材：
计算机，网络环境，投影设备。
实验相关软件：Window xp、PowerDesigner、SQL Server 2008。
3.实验内容与步骤
使用已经建立的数据库students及student course sc表实现下面操作。
3.1 创建满足下述要求的存储过程，并查看执行结果。
1）查询每个学生的修课总学分，要求列出学生学号及总分数。
CREATE procedure p1 as
select student.sno,sum(credit) 总学分
from student,course,SC
where student.sno=sc.sno and o=o
group by student.sno;
2）查询学生的学号、姓名、选修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值“计算机”。执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。
ALTER PROCEDURE p2 @sdept char(20)='计算机系'
AS
SELECT student.Sno,Sname, Sdept, o,Cname, Credit FROM Student,course,sc
WHERE student.sno=sc.sno and o=o and Sdept = @sdept;
exec p2;
exec p2 '信息管理系';
exec p2 '通信工程系';
3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。
CREATE PROCEDURE p3
@sdept char(20),@count int output
AS
SELECT @count=count(*) from student where ssex='男' and sdept=@sdept;
DECLARE @res int
EXEC p3 '计算机系',@res OUTPUT
PRINT @res;
4）删除指定学生的修课记录，其中学号为输入参数。
CREATE PROCEDURE p4
@sno char(20)
AS
delete from sc where sno=@sno;
EXEC p4 '0611102';
5）修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。
CREATE PROCEDURE p5
@cno char(5),@sem int
AS
update course set semster=@sem where cno=@cno;
exec p5 'c004',3;
3.2 创建满足下述要求的触发器（前后均可），并查看执行结果。
1）限制学生的年龄在15-45之间。
create trigger t1 on student after insert,update as
if exists(select * from inserted where sage not between 15 and 45)
begin
print '学生年龄不在15-45之间，请重新输入!';
rollback;
End
insert into student values('0631106','林道','男',10,'建筑设计系');
2）限制学生所在系的取值范围为{计算机，信息管理系、数学系、通信工程系} 。
create trigger t2 on student after insert,update as
if exists(select * from inserted where sdept not in('计算机系','信息管理系','数学系','通信工程系'))
begin
print '学生所在系输入错误，请重新输入!';
rollback;
end
insert into student values('0631106','林道','男',20,'建筑设计系');
3）限制每个学期开设的课程总学分在20-30范围内。
create trigger t3 on course after insert,update as
if((select sum(Credit) from course where semster in(select semster from inserted))not
between 20 and 30)
begin
print '课程总学分超出范围!';
rollback;
end
insert into course values('c010','算法分析',30,1);
4）限制每个学生每学期选修课门数不能超过6门。
create trigger t4 on sc after insert,update as
if(select count(*) from sc,course where o=o and semster in(select semster from course where o in(select cno from inserted))and sc.sno in(select sno from
inserted))>6
begin
print '该学生选课门数超过6门!';
rollback;
end
insert into sc values('0611101','c009',null),('0611101','c010',null),
('0611101','c011',null),('0611101','c012',null),('0611101','c013',null);
3.3 创建满足下述要求的游标，并查看执行结果。
！）列出VB考试成绩最高的前2名和最后1名学生的学号、姓名、所在系和VB成绩。显示结果。
DECLARE cs1 scroll CURSOR FOR
SELECT student.Sno,Sname,Sdept,Grade
FROM Student,sc,course
WHERE student.sno=sc.sno and o=o and cname='VB'
ORDER BY Grade DESC
open cs1
fetch next from cs1;
fetch next from cs1;
fetch last from cs1;
close cs1;
deallocate cs1;
2）列出每个系年龄最大2名学生的姓名和年龄，结果按年龄降序排序。显示结果。
declare @Sdept char(20),@Sname char(7),@sage int
declare cs2 cursor for
select distinct sdept from student
open cs2
fetch next from cs2 into @sdept
while @@FETCH_STATUS=0
begin
print @Sdept;
print '姓名 年龄';
declare cs3 cursor for
select Sname,Sage
from student
where sdept=@sdept
order by sage desc;
open cs3
fetch next from cs3 into @sname,@sage;
print @Sname+cast(@sage as char(4))
fetch next from cs3 into @sname,@sage;
print @Sname+cast(@sage as char(4)) close cs3;
deallocate cs3;
print ''
fetch next from cs2 into @sdept
end
close cs2;
deallocate cs2;
4．思考与总结
