sql server 实验14 存储过程与触发器
1、① 创建一存储过程利用学生姓名查询该生选修的课程名、成绩、以及任课教师姓名（涉及的表可能有s、sc、t、c、tc）,并调用存储过程查询王青山的选修的课程名、成绩以及任课教师姓名，结果如图所示。
2、① 创建一存储过程利用系名查询某系的学生的最大年龄和最小年龄。调用存储过程查询信息系的学生最大年龄和最小年龄。（涉及的表可能有s）
3、① 创建一存储过程利用学生姓名和课程名检索该生该课程的成绩，涉及的表可能有s,sc,c。调用该存储过程查询王青山程序设计的成绩，结果如下图所示。
创建存储过程：
if exists(select name from sysobjects where name='pro_qscore' and type='p')
drop proc pro_qscore
go
CREATE proc [dbo].[pro_qscore]
@sname_in char(10),
@cname_in char(10),
@grade_out tinyint output
as
select @grade_out=score
from s,sc,c
where s.sn=@sname_in
and s.sno=sc.sno
and sc.cno=c.cno
and c.cn=@cname_in
Go
调用存储过程：
declare @sname_in char(10),@cname_in char(10),@grade_out tinyint
set @sname_in='王青山'
set @cname_in='程序设计'
exec pro_qscore @sname_in,@cname_in,@grade_out output
select @sname_in 姓名,@cname_in 课程,@grade_out 成绩
4、① 查询某系的教师平均年龄和学生人数。
创建存储过程：
if exists(select name from sysobjects where name='pro_dept_count' and type='p')
drop proc pro_dept_count
go
create proc pro_dept_count
@dept_in char(10)
as
select a.dept 系别,平均年龄,学生人数
from (select dept,count(tno) as 教师人数,avg(age) as 平均年龄 from t group by dept) a,(select dept,count(sno) 学生人数 from s group by dept) b
where a.dept=b.dept and a.dept=@dept_in
go
调用存储过程：
declare @dept_in char(10)
set @dept_in='计算机'
exec pro_dept_count @dept_in
5、① 利用教师姓名和课程名检索该教师该课的课程名、课时数、选课人数、平均成绩、最高成绩、并查询教师“张雪”的“微机原理”课程的情况记录及教师“张朋”的“数据库”课程，结果如图所示。
创建存储过程：
if exists(select name from sysobjects where name='pro_tname_cname' and type='p')
drop proc pro_tname_cname
go
create proc pro_dept_count
@tname_in char(10),
@cname_in char(10),
@student_sum int output,
@grade_avg int output,
@grade_max int output
as
select @student_sum=count(sno),@grade_avg=avg(Score),@grade_max=max(score)
from t,c,sc,tc
where sc.cno=c.cno and c.cno=tc.cno
and tc.tno=t.tno and t.tn=@tname_in
and c.cn=@cname_in
group by tn,cn
Go
调用存储过程：
declare @tname_in char(10), @cname_in char(10),
@student_sum int,@grade_avg int,@grade_max int
set @tname_in='张雪'
set @cname_in='微机原理'
exec pro_dept_count @tname_in,@cname_in,@student_sum output,@grade_avg output,@grade_max output
if (@student_sum is null)
print'没有学生选微机原理这门课程，故总课时数，选课人数，平均成绩等统统为0'
else
select @tname_in 教师姓名,@cname_in 学生姓名,@student_sum 选课总人数,@grade_avg 平均成绩,@grade_max 总成绩
6、1．创建并使用触发器，注意创建触发器前一定要先用IF EXISTS判断一下是否已创建同名的触发器，若有先删除在创建。
① 为表sc创建一触发器：当插入或修改一个记录时，确保此记录的成绩在0~100分之间。（思路：新的数据行就会被插入inserted表，通过检查该表判断插入的行是否在0~100之间，如果在正常插入，否则不做插入，回滚至插入的状态。）
l 插入数据行('s5','c1','101')，是否弹出如图所示的错误。请问该行数据是否插入到sc表中
l 将s5的c1成绩更新至101,能否更新成功？
if exists(select name from sysobjects where name='score_sc_tri' and type='tr')
drop trigger score_sc_tri
go
create trigger score_sc_tri
on sc for insert,update
as
declare @score_in tinyint
select @score_in=score from inserted
if @score_in<0 or @score_in>100
begin
print '成绩超出0-100！请重新输入.'
rollback transaction
end
go
insert into sc values ('s5','c1','101') /*触发器激活，插入失败*/
Update sc set score=101 where sno='s5' and cno='c1'
/*触发器激活，更新失败*/
7、① 为数据库表T创建一触发器：当职称从“讲师”晋升为“副教授”时，津贴自动增加500元，从“副教授”晋升为“教授”时，岗位津贴自动增加900元。
if exists (select name from sysobjects where name='ut' and type='tr')
drop trigger ut
go
create trigger ut
on t for update
as
declare @prof_old char(10)
declare @prof_new char(10)
declare @tno char(2)
select @prof_old=prof from deleted
select @prof_new=prof from inserted
select @tno=tno from deleted
if @prof_old='讲师' and @prof_new='副教授'
begin
update t set comm=comm+500 where tno=@tno
end
if @prof_old='副教授' and @prof_new='教授'
begin
update t set comm=comm+900 where tno=@tno
end
go
update t set prof='副教授' where tn='刘伟'
更新前：
更新后：
