实验报告(五) 游标、存储过程与触发器
一、实验目的
掌握使用T-SQL实现游标、存储过程和触发器的创建,使用方法。
二、实验内容
在实验一、实验二创建的表中用T-SQL语句完成以下内容:
--1.使用游标实现将SC表中及格的选课信息输出。
use student
declare @s varchar(10),
@c varchar(10),
@g smallint
declare c1 cursor static for select Sno,Cno,Grade from SC where Grade >= 60 --without 'static' is ok....haoyong
open c1
fetch next from c1 into @s,@c,@g
print ' ' + 'Sno' + ' ' + 'Cno' + ' ' + 'grade';
while(@@FETCH_STATUS=0)
begin
print @s + ' ' + @c + ' ' + cast(@g as varchar);
fetch next from c1 into @s,@c,@g
end
close c1
deallocate c1
--2.使用游标将SPJ表中的偶数行输出。
use project
declare @s1 varchar(10),
@p1 varchar(10),
@j1 varchar(10),
@q1 int
declare c3 cursor static for select * from spj
open c3
fetch next from c3 into @s1,@p1,@j1,@q1
print '偶数行信息输出:';
declare @i int = 0;
while(@@FETCH_STATUS=0)
begin
set @i = @i + 1;
if(@i%2=0)
print @s1 + ' ' + @p1 + ' ' + @j1 + ' ' + cast(@q1 as varchar);
fetch next from c3 into @s1,@p1,@j1,@q1
end
close c3
deallocate c3
--3.创建存储过程,根据指定的学生姓名查询该学生所修课程的课程信息,将课程号和课程名输出,并使用exec语句执行存储过程。
use student
go
create procedure p1
@name varchar(20)
as
select C.Cno,Cname
from s,c,sc
where S.sno = SC.sno and C.cno = SC.cno and S.sname = @name
exec p1 '胡胜军'
exec p1 '任静凯'
--4.创建存储过程,根据指定的学生学号统计该学生选修的所有课程的平均成绩和选课门数,将统计的结果用输出参数返回,
--并使用exec语句执行存储过程。
go
create procedure p2
@s varchar(20),
@avg int output,
@couc int output
as
select @avg = AVG(Grade),@couc = COUNT(Sno)
from SC
where Sno = @s
group by Sno
DECLARE @a int,@b int
exec p2 '101102' ,@a output ,@b output
select @a ,@b
--5.创建存储过程,根据用户指定的供应商号删除SPJ表中相应的供货信息,并使用exec语句执行存储过程。
use project
go
create procedure delete_spj
@s2 varchar(10)
as
delete from SPJ where Sno = @s2
exec delete_spj 's3'
--6.创建存储过程,将指定零件的重量增加指定的值,并使用exec语句执行存储过程。\
go
create procedure add_weight
@p2 varchar(10),
@addWeight int
as
update P set Weight += @addWeight where Pno = @p2
exec add_weight 'P1' , 10
原图
执行之后
--7.(1)删除SPJ关系中所有数据。
declare @s2 varchar(10),
@p2 varchar(10),
@j2 varchar(10),
@q2 int
declare c4 cursor static for select * from SPJ
open c4
fetch next from c4 into @s2,@p2,@j2,@q2
while(@@FETCH_STATUS=0)
begin
delete from SPJ
fetch next from c4 into @s2,@p2,@j2,@q2
end
close c4
deallocate c4
--7(2)在插入和修改SPJ表中QTY属性列的值时用触发器实现约束:
--如果是北京的供应商,供应任何零件的数量不能少于,如果少于则自动改为300。
go
create trigger beijing on SPJ
after insert,update
as
declare @a varchar(10),
@b int,
@city varchar(20)
select @a = Sno,@b = QTY from inserted,S
if @city = S.City and @b < 300
update SPJ set QTY = 300
where SPJ.Sno = @a
insert into SPJ
values ('S3','P2','J3',120),('S2','P2','J3',120)
insert into SPJ
values ('S1','P2','J3',120),('S4','P2','J3',120)
update SPJ
set QTY = 200
where Sno = 'S2'
--7(3)在SPJ表中录入值进行验证。
见7.2
--8(1)删除SC关系中的所有数据。
declare @sno varchar(10),
@c varchar(10),
@g smallint
declare c2 cursor static for select Sno,Cno,Grade from SC
open c2
fetch next from c2 into @sno,@c,@g
while(@@FETCH_STATUS=0)
begin
delete from SC
fetch next from c2 into @sno,@c,@g
end
close c2
deallocate c2
--8(2)在SC关系中增加新属性列Status,用来记录课程成绩的等级。
use student
alter table SC add status varchar(10)
--8(3)用触发器实现自动记录成绩等级,当插入和修改grade列的值时,如果grade在-分,status自动填写为“不合格”;
--grade在-分,status自动填写为“合格”;grade在-分,status自动填写为“良好”;grade在以上status自动填写为“优秀”。
go
create trigger addStatus on SC
after insert,update
as
declare @gr smallint,
@ss varchar(20),
@cc varchar(20)
select @gr = grade , @ss = Sno, @cc = Cno from inserted
if @gr >= 0 and @gr < 60
update SC set status = '不及格'
where Sno = @ss and Cno = @cc
else if @gr >= 60 and @gr < 70
update SC set status = '及格'
where Sno = @ss and Cno = @cc
else if @gr >= 70 and @gr < 90
update SC set status = '良好'
where Sno = @ss and Cno = @cc
else if @gr >= 90 and @gr < 101
update SC set status = '优秀'
where Sno = @ss and Cno = @cc
--(4)在SC表中录入值进行验证。
--insert into SC(Sno,Cno,Grade) 外键约束 不宜插入
--values ('101101','C01',87),('101101','C02',56)
update SC
set Grade = 23
where Sno = '101102' and Cno = 'C01'
update SC
set Grade = 76
where Sno = '101102' and Cno = 'C02'
update SC
set Grade = 100
where Sno = '101102' and Cno = 'C03'
--9.以下两道题任选一道完成。
--()每个学院有若干班级和教研室,每个教研室有若干教员,其中有的教授和副教授每人带若干研究生,
--每个班有若干学生,每个学生选修若干门课程,没门课程可由若干学生选修。
--学院的属性有:学院编号、学院名称、办公电话、办公地址,
--班级的属性有:班级编号、班级名称、人数、入校时间,教研室属性有:教研室编号、名称、电话、地点,
--教员属性有:职工号、名称、年龄、性别、职称、研究方向,
--课程属性有:课程编号、课程名、开课学期、学时、学分,学生属性有:学号、姓名、出生日期、性别、专业。
--根据以上描述设计此应用的概念模型,画出ER图。
--根据转换规则将ER图转换成关系模式,要求转换出的关系模式尽可能少。
实验总结:基本功不是很扎实,希望实习的时候好好锻炼数据库方面的知识!
第二篇:数据库实验报告4
SQL Server数据库设计实验报告(四)
20##年 5月15 日