insert into sc (sno,sportno,score) values
(10103,1,'')
5．4 数据库完整性
1、 绑定列上默认值 use sports go
create default ssex as '男' go
exec sp_bindefault 'ssex','' go
2、 绑定规则
create rule rule_sportno as @c1 between 1 and 12 go
create rule rule_sno as @c1 between 10101 and 40340 go
exec sp_bindrule 'rule_sportno','' go
exec sp_bindrule 'rule_sno','' go
5．5 成绩表按成绩自动排名（触发器，函数，存储过程）
功能简介：当sc 表有成绩插入删除或者修改时就触发，实现对sc表中单项目名次nrank属性的及时更新，更新完之后，调用存储过程，实现对clscore表中班级成绩和班级排名的更新。
5．5．1 更新项目名次触发器
--触发器----及时更新 sc 表
--当有成绩插入删除或者修改sc 表时就触发 --实现对sc表中单项目名次nrank属性的及时更新 use sports
go----------------------------插入更新与删除操作sc 表
if exists(select * from sysobjects where name = 'trSC' and type = 'TR') drop trigger trSC go
create trigger trSC on sc after insert,update,delete as declare @sportno0 int,@sno0 int,@sportno1 int,@sportno2 int select @sno0=sno,@sportno0=sportno from inserted select @sportno1=sportno1,@sportno2=sportno2 from stu where sno = @sno0 if @sportno0 not in(@sportno1,@sportno2) --if @sportno0!=@sportno1 and @sportno0!=@sportno2 begin print '操作有误，此记录与运动员表不一致，请先修改运动员表！' rollback end
else begin --更新名次 --声明变量 declare @sno int,@sportno int,@rank int declare iu_scSportno_cursor CURSOR for select sportno from sp open iu_scSportno_cursor fetch next from iu_scSportno_cursor into @sportno while @@fetch_status = 0 begin set @rank = 1 --声明游标 declare iu_sc_cursor CURSOR for select sno from scRankFun(@sportno) --打开游标 open iu_sc_cursor --提取第一行 fetch next from iu_sc_cursor into @sno while @@FETCH_STATUS = 0 begin update sc set srank = @rank where sno = @sno and sportno = @sportno set @rank = @rank+ 1 fetch next from iu_sc_cursor into @sno end close iu_sc_cursor deallocate iu_sc_cursor fetch next from iu_scSportno_cursor into @sportno end close iu_scSportno_cursor deallocate iu_scSportno_cursor end -----------执行存储过程updateClscore，更新clscore 表----------- exec updateClscore go Go
测试代码及截图如下：
5．5．2 计算班级总成绩存储过程
存储过程：当sc 表名次有变动时，及时更新clscore表中的班级总成绩
---------------当sc 表有更新时，及时更新clscore表中每个班的总分数---------- use sports go
if exists(select * from sysobjects where name = 'updateClscore' and type = 'P') drop procedure updateClscore go
create procedure updateClscore as begin declare @classname varchar(20),@srank int,@srankTotal int declare cursor_class cursor for select sclassname from stu group by sclassname open cursor_class fetch next from cursor_class into @classname while @@FETCH_STATUS = 0 begin set @srankTotal = 0 declare cursor_srank cursor for select srank from sc where sno in(select sno from stu where sclassname = @classname) open cursor_srank fetch next from cursor_srank into @srank while @@FETCH_STATUS = 0 begin if @srank < 8 set @srankTotal = @srankTotal+(8-@srank) else set @srankTotal = @srankTotal+0
fetch next from cursor_srank into @srank end update clscore set sclscore = @srankTotal where sclassname = @classname close cursor_srank deallocate cursor_srank fetch next from cursor_class into @classname end close cursor_class deallocate cursor_class ------------执行存储过程，更新clscore表，进行班级排名--------------- exec updateClscore_rank end Go
测试代码及截图如下：
5．5．3 班级排名存储过程
存储过程：更新clscore表，对clscore表中的班级总成绩分别按学院和学校排名次 --------------存储过程，更新clscore表，进行班级排名--------------- use sports
if exists(select * from sysobjects where name='updateClscore_rank' and type = 'p') drop proc updateClscore_rank go
create proc updateClscore_rank as begin declare @dept varchar(20),@class varchar(20),@rank int ------------在学院排名次------------- declare cursor_dept cursor for select sdeptname from clscore group by sdeptname open cursor_dept fetch next from cursor_dept into @dept while @@FETCH_STATUS = 0 begin set @rank = 1 declare cursor_class cursor for
end Go
select sclassname from clscore where sdeptname = @dept order by sclscore desc open cursor_class fetch next from cursor_class into @class while @@FETCH_STATUS = 0 begin update clscore set sclrankindept = @rank where sclassname = @class set @rank = @rank + 1 fetch next from cursor_class into @class end close cursor_class deallocate cursor_class fetch next from cursor_dept into @dept end
close cursor_dept deallocate cursor_dept
------------在学校排名次------------- declare cursor_class cursor for select sclassname from clscore order by sclscore open cursor_class
fetch next from cursor_class into @class set @rank = 1
while @@FETCH_STATUS = 0 begin update clscore set sclrankinsch = @rank where sclassname = @class set @rank = @rank + 1 fetch next from cursor_class into @class end
close cursor_class deallocate cursor_class
5．5．4 项目排名表值函数
------------------------------某项目排名函数 表值函数 --输入：某个项目的编号
--输出：此项目的排序后的运动员学号顺序表 use sports go
if exists(select * from sysobjects where name = 'scRankFun' and type = 'TF') drop function scRankFun go
create function scRankFun(@sportno int) returns @st table (
数据库课程设计 运动会成绩管理系统
