实 验 报 告课程名称 数据库技术实践 实验项目 存储过程、触发器、用户自定义函数与游标 实验仪器 SQL Server 2008 系 别____计算机科学与技术系 _专 业____计算机科学与技术____ 班级/学号_______________________学生姓名 _______________________ 实验日期 ___ _______成 绩 _______________________ 指导教师 ___ _______ _______[在内容说明部分请总体说明在本部分实践过程中，具体都完成了哪些内容]一．内容说明[请按照下面练习题的要求，完成各项内容，并说明每个题目完成的情况，是否存在问题，如何解决等]二. 实验步骤与内容如无特别说明，以下各题均利用之前建立的Students数据库以及Student、Course和SC表实现1． 创建满足下述要求的存储过程，并查看存储过程的执行结果1） 查询每个学生的修课总学分，要求列出学生学号及总学分。
create proc SumCredit as select sno 学号,sum(credit) 总学分 from sc join course c on o=o group by sno go exec SumCredit（2） 查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系”执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果 create proc StudentInformation @dept varchar(50)='计算机系',@sname char(50) as select sc.sno 学号,sname 姓名,o 课程号,cname 课程名,credit 学分 from Student s join SC on s.Sno=sc.Sno join Course c on c.Cno = SC.Cno where dept = @dept and Sname = @sname go exec StudentInformation '信息管理系','吴宾' exec StudentInformation @sname='李勇' （3） 查询指定系的男生人数，其中系为输入参数，人数为输出参数。
create proc Man_Num @dept varchar(50),@rs int output as select @rs=COUNT(*) from Student where Dept=@dept and Sex='男' go declare @rs int exec Man_Num '信息管理系',@rs output select @rs as 人数（4） 查询考试平均成绩超过指定分值的学生学号和平均成绩create proc AvgGrade @grade int as select sno,AVG(grade) as '平均成绩' from SC group by Sno having AVG(grade)>@grade go exec AvgGrade 60（5） 查询查询指定系的学生中，选课门数最多的学生的选课门数和平均成绩，要求系为输入参数，选课门数和平均成绩用输出参数返回create proc Choose_Course @dept varchar(50),@rs int output,@avg int output as select top 1 @rs=COUNT(*) ,@avg=avg(grade) from Student s join SC on s.Sno=sc.Sno join Course c on c.Cno = SC.Cno where dept=@dept group by sc.Sno go declare @rs int,@avg int exec Choose_Course '信息管理系',@rs output,@avg output select @rs as 选课门数,@avg as 平均成绩（6） 删除指定学生的修课记录，其中学号为输入参数。
create proc Del_Course @sno char(50) as delete from SC where Sno=@sno go exec Del_Course '0831102' （7） 修改指定课程的开课学期输入参数为：课程号和修改后的开课学期,开课学期的默认值为2如果指定的开课学期不在1~8范围内，则不进行修改2． 利用SSMS工具查看在students数据库中创建的全部存储过程Students=>可编程性=> 存储过程3． 修改第1题（1）的存储过程，使之能够查询指定系中，每个学生选课总门数、总学分和考试平均成绩 alter proc SumCredit @dept nvarchar(20) as select COUNT(SC.Cno) 总门数,sum(credit) 总学分,AVG(Grade) 平均成绩 from student s left join sc on s.sno=sc.sno left join course c on o=o where dept = @dept group by sc.Sno go exec SumCredit '信息管理系'4． 创建满足下述要求的触发器（前触发器、后触发器均可），并验证触发器执行情况。
1） 限制每个学期开设的课程总学分在20~30范围内 alter trigger TR_SumGrade on course after insert as declare @s int,@x int,@y int set @s=(select sum(Credit) from course where semester in(select semester from inserted)) if(20<@s and @s<30) begin print '课程总学分没有超出范围!!!' print @s end else begin print'课程总学分超出范围!!!' print's=' print @s rollback end insert into course values('C010','汇编语言',200,1)（2） 限制每个学生每学期选课门数不能超过5门（设只针对插入操作） ALTER trigger TR_MEN on sc after insert as declare @x int set @x=(select count(*) from sc join course c on o=o where semester in(select semester from course where cno in(select cno from inserted))and sc.sno in(select sno from inserted)) if(@x>5) begin select * from sc join course c on o=o select *from inserted print @x print '选课门数超过门' rollback end5． 在Students数据库建立如下所示的工作表和职工表CREATE TABLE 工作表( 工作号 CHAR(8) PRIMARY KEY,最低工资 SMALLINT,最高工资 SMALLINT )CREATE TABLE 职工表( 职工号 CHAR(7) PRIMARY KEY, 职工名 CHAR(10) NOT NULL,工作号 CHAR(8) REFERENCES 工作表(工作号),基本工资 SMALLINT,浮动工资 SMALLINT )利用这两张表建立满足如下要求的触发器。
1） 限制职工的基本工资和浮动工资之和必须大于等于2000create trigger TR_Salaryon 职工表 after insert,updateasdeclare @x SMALLINT,@y SMALLINT,@z SMALLINTset @x=(select 基本工资 from 职工表 where 职工号 in(select 职工号 from inserted))set @y=(select 浮动工资 from 职工表 where 职工号 in(select 职工号 from inserted))set @z=@x+@yif(@z>=2000)beginprint'操作符合要求'endelsebeginprint @xprint @yprint @zprint'请注意，职工的基本工资和浮动工资之和小于！！！！'select * from 职工表select * from insertedrollbackendinsert into 工作表 values('G001',10000,1000)insert into 职工表values('Z001','张三','G001',1000,100)（2） 限制工作表中最高工资不能低于最低工资的1.5倍。
create trigger TR_Salary1 on 工作表 after insert,update as declare @x SMALLINT,@y SMALLINT,@z float set @x=(select 最低工资 from 工作表 where 工作号 in(select 工作号 from inserted)) set @y=(select 最高工资 from 工作表 where 工作号 in(select 工作号 from inserted)) set @z=@y/@x if(@z>=1.5) begin print'操作符合要求' end else begin print @x print @y print @z select * from 工作表 select * from inserted print '请注意，最高工资低于最低工资的.5倍' rollback end insert into 工作表 values('G002',1000,1000)（3） 限制不能删除基本工资低于1500的职工alter trigger TR_Salary2 on 职工表 after delete as if exists(select * from 职工表 where 基本工资<1500) begin print'操作符合要求' end else begin print'不能删除基本工资低于的职工' select * from 职工表 select * from deleted rollback end6． 创建满足下述要求的用户自定义标量函数。
1） 查询指定学生已经得到的修课总学分（考试及格的课程才能拿到学分），学号为输入参数，总学分为函数返回结果并写出利用此函数查询9512101学生的姓名、所修的课程名、课程学分、考试成绩以及拿到的总学分的SQL语句 create function dbo.Sum_Credit(@sno char(7)) returns int as begin declare @sum int set @sum=(select sum(credit) from sc join course c on o=o where sc.sno=@sno and grade>=60) return @sum end select sname 姓名,o 课程名,credit 课程学分,grade 考试成绩,dbo.Sum_Credit(sc.sno) as 总学分 from sc join course c on o=o join student s on s.sno=sc.sno where sc.sno='0811101'（2） 查询指定系在指定课程(课程号)的考试平均成绩 create function dbo.Avg_Grade(@dept nvarchar(20), @cno char(6)) returns int as begin declare @Avg int select @Avg=avg(grade) from sc join student s on sc.sno=s.sno where dept=@dept and o=@cno return @Avg end select distinct dbo.Avg_Grade(dept,cno) as 平均成绩 from sc join student s on sc.sno=s.sno where dept='计算机系' and o='C001'（3） 查询指定系的男生中选课门数超过指定门数的学生人数。
create function dbo.Man(@dept nvarchar(20),@menshu int) returns int as begin declare @Num int select @Num=count(*) from(select sc.sno,count(o) as b from student s left join sc on s.sno=sc.sno where dept=@dept and sex='男' group by sc.sno having count(o)>@menshu) as t return @Num end select distinct dbo.Man(dept,0) 学生人数 from student s left join sc on s.sno=sc.sno where dept='计算机系'7． 创建满足下述要求的用户自定义内联表值函数1） 查询选课门数在指定范围内的学生的姓名、所在系和所选的课程 create function dbo.F_7_1(@menshu int) returns table as return( select sname,dept,o,cname from Student s join SC on s.Sno=sc.Sno join Course c on c.Cno = SC.Cno where s.sno in ( select sno from sc group by sno having count(*)=@menshu))select * from dbo.F_7_1(3)（2） 查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。
并写出利用此函数查询计算机系学生考试情况的SQL语句，只列出学生姓名、课程名和考试成绩 create function dbo.F_7_2(@dept char(20)) returns table as return(select sname,dept,cname,grade from Student s join SC on s.Sno=sc.Sno join Course c on c.Cno = SC.Cno where dept=@dept and grade>=90) select sname,cname,grade from dbo.F_7_2('计算机系')8． 创建满足下述要求的用户自定义多语句表值函数1） 查询指定系年龄最大的前2名学生的姓名和年龄，包括并列的情况alter function dbo.F_8_1(@dept char(20))returns @ret_F_8_1 table(sname char(10),age int)asbegininsert into @ret_F_8_1select top 2 WITH TIES sname,year(GETDATE())-year(Birthday) age from studentwhere dept=@deptorder by age DESCreturnendselect sname,age from dbo.F_8_1('计算机系')（2） 查询指定学生（姓名）的考试情况，列出姓名、所在系、修的课程名和考试情况，其中考试情况列的取值为：如果成绩大于等于90，则为“优”；如果成绩在80~89，则为“良好”；如果成绩在70~79，则为“一般”；如果成绩在60~69，则为“不太好”；如果成绩小于60，则为“很糟糕”。
并写出利用此函数查询李勇的考试情况的SQL语句alter function dbo.F_8_2(@sname char(10))returns @ret_F_8_2 table(sname char(10),dept char(20),cname char(20),GStye char(6))asbegininsert into @ret_F_8_2select sname,dept,cname,casewhen grade >=90 then '优'when grade between 80 and 89 then'良好'when grade between 70 and 79 then'一般'when grade between 60 and 69 then'不太好'when grade <60 then'很糟糕'endfrom Student s join SC on s.Sno=sc.Sno join Course c on c.Cno = SC.Cnowhere sname=@snamereturnendselect sname,dept,cname,gstye from dbo.F_8_2('刘晨')select sname,dept,cname,gstye from dbo.F_8_2('李勇')9． 创建满足下述要求的游标。
1）查询VB课程的考试情况，并按如下形式显示结果数据:选了VB课程的学生情况：姓名 所在系 成绩李勇 计算机系 86 刘晨 计算机系 78 吴宾 信息系 75 张海 信息系 68 print '选了VB课程的学生情况：' print' ' print '姓名 所在系 成绩' declare @s char(10),@d char(20),@g int declare C_9_1 cursor for select sname,dept,grade from Student s join SC on s.Sno=sc.Sno join Course c on c.Cno = SC.Cno where cname='VB' open C_9_1 fetch next from C_9_1 into @s,@d,@g while @@fetch_status=0 begin print' ' print @s+@d+cast(@g as char(4)) fetch next from C_9_1 into @s,@d,@g end close C_9_1 deallocate C_9_1（2）统计每个系的男生人数和女生人数，并按如下形式显示结果数据。
系名 性别 人数====================计算机系 男 2 计算机系 女 1 数学系 男 1 数学系 女 1 信息系 男 2 信息系 女 1 print '系名 性别 人数' print '====================' declare @d char(10),@s char(2),@c int declare C_9_2 cursor for select dept,sex,count(*) 人数 from Student group by dept,sex order by dept open C_9_2 fetch next from C_9_2 into @d,@s,@c while @@fetch_status=0 begin print' ' print @d+' '+@s+' '+cast(@c as char(4)) fetch next from C_9_2 into @d,@s,@c end close C_9_2 deallocate C_9_2 （3）列出每个系的学生信息，要求首先列出一个系的系名，然后在该系名下列出本系学生的姓名和性别；再列出下一个系名，然后在此系名下再列出该系的学生姓名和性别；以此类推，直至列出全部系。
要求按如下形式显示结果数据:计算机系学生： 李勇 计算机系 刘晨 计算机系 王敏 计算机系 =====================数学系学生： 钱小平 数学系 王大力 数学系 =====================信息系学生： 张立 信息系 吴宾 信息系 张海 信息系 ===================== declare @dept varchar(20),@sname char(10) declare C_9_3 cursor for select distinct dept from student open C_9_3 fetch next from C_9_3 into @dept while @@fetch_status=0 begin print @dept+':' declare C_3 cursor for select sname,dept from student where dept=@dept open C_3 fetch next from C_3 into @sname,@dept while @@fetch_status=0 begin print @sname+@dept fetch next from C_3 into @sname,@dept end close C_3 deallocate C_3 print'======================' fetch next from C_9_3 into @dept end close C_9_3 deallocate C_9_3（4）设有工作表，结构如下：Job( Jobid char(4) primary key, --工作编号 desc varchar(40), --工作描述 lvl tinyint) --工作级别设此表包含的数据如表7-2所示。
表7-2 Job表的数据JobiddesclvlJ01软件开发10J02硬件开发12J03软件测试10J04硬件维护8J05硬件测试12用游标实现对此表数据的如下修改：将工作级别相同的工作只保留工作编号较小的一项工作，同时，将这些工作的工作描述拼接为一个工作描述，中间用逗号分隔修改后的数据示意如下：JobidJ_desclvlJ01软件开发，软件测试10J02硬件开发，硬件测试12J04硬件维护8create table #Job(Jobid char(4)primary key,[desc] varchar(40),lvl int) insert into #Job values('J01','软件开发',10) insert into #Job values('J02','硬件开发',12) insert into #Job values('J03','软件测试',10) insert into #Job values('J04','硬件维护',8) insert into #Job values('J05','硬件测试',12)create table #N_Job(Jobid char(4)primary key,[desc] varchar(40),lvl int)drop table #N_Jobdeclare @Jobid char(4),@desc varchar(40),@lvl intdeclare C_9_4 cursor for select jobid,[desc],lvl from #jobopen C_9_4fetch next from C_9_4 into @Jobid,@desc,@lvlwhile @@fetch_status=0beginif(exists(select * from #N_Job where lvl=@lvl))beginupdate #N_Job set [desc]=[desc]+','+@descwhere lvl=@lvlendelsebegininsert into #N_Job select @Jobid,@desc,@lvlendfetch next from C_9_4 into @Jobid,@desc,@lvlendclose C_9_4deallocate C_9_4select * from #N_Job三. 实验总结。