计科09-数据库技术实践-第三部分

实 验 报 告

课程名称 数据库技术实践 实验项目 存储过程、触发器、用户自定义函数与游标 实验仪器 SQL Server 2008

系 别____计算机科学与技术系 _ 专 业____计算机科学与技术____ 班级/学号_______________________

学生姓名 _______________________ 实验日期 ___ _______ 成 绩 _______________________

指导教师 ___ _______ _______

[在内容说明部分请总体说明在本部分实践过程中，具体都完成了哪些内容]

一．内容说明

[请按照下面练习题的要求，完成各项内容，并说明每个题目完成的情况，是否存在问题，如何解决等]

二. 实验步骤与内容

如无特别说明，以下各题均利用之前建立的Students数据库以及Student、Course和SC表实现。

1．创建满足下述要求的存储过程，并查看存储过程的执行结果。 （1） 查询每个学生的修课总学分，要求列出学生学号及总学分。

create proc SumCredit as

select sno 学号,sum(credit) 总学分 from sc join course c on sc.cno=c.cno

group by sno go

exec SumCredit

（2） 查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在

系作为输入参数，默认值为“计算机系”。执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。 create proc StudentInformation

@dept varchar(50)='计算机系',@sname char(50) as

select sc.sno 学号,sname 姓名,sc.cno 课程号,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 sc.cno=c.cno 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 sc.cno=c.cno

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 sc.cno=c.cno select *from inserted print @x

print '选课门数超过门' rollback end

5．在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） 限制职工的基本工资和浮动工资之和必须大于等于2000。

create trigger TR_Salary

on 职工表 after insert,update as

declare @x SMALLINT,@y SMALLINT,@z SMALLINT

set @x=(select 基本工资 from 职工表 where 职工号 in(select 职工号 from inserted))

set @y=(select 浮动工资 from 职工表 where 职工号 in(select 职工号 from inserted)) set @z=@x+@y if(@z>=2000) begin

print'操作符合要求' end else begin print @x print @y print @z

print'请注意，职工的基本工资和浮动工资之和小于' select * from 职工表 select * from inserted rollback end

insert 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 end

6．创建满足下述要求的用户自定义标量函数。

（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 sc.cno=c.cno where sc.sno=@sno and grade>=60) return @sum end

select sname 姓名,sc.cno 课程名,credit 课程学分,grade 考试成绩,dbo.Sum_Credit(sc.sno) as 总学分 from sc join course c on sc.cno=c.cno

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 sc.cno=@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 sc.cno='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(sc.cno) as b from student s left join sc on s.sno=sc.sno

where dept=@dept and sex='男' group by sc.sno

having count(sc.cno)>@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,sc.cno,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) as begin

insert into @ret_F_8_1

select top 2 WITH TIES sname,year(GETDATE())-year(Birthday) age from student where dept=@dept order by age DESC return end

select 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)) as begin

insert into @ret_F_8_2

select sname,dept,cname,case when 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'很糟糕'

end

from Student s join SC on s.Sno=sc.Sno join Course c on c.Cno = SC.Cno

where sname=@sname return end

select 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所示。

Jobid J01 J02 J03 J04 J05 表7-2 Job表的数据 desc 软件开发 10 硬件开发 12 软件测试 10 硬件维护 8 硬件测试 12 lvl 用游标实现对此表数据的如下修改：将工作级别相同的工作只保留工作编号

较小的一项工作，同时，将这些工作的工作描述拼接为一个工作描述，中间用逗号分隔。修改后的数据示意如下： Jobid J_desc lvl J01 软件开发，软件测试 10 J02 硬件开发，硬件测试 12 J04 硬件维护 8 create 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_Job

declare @Jobid char(4),@desc varchar(40),@lvl int

declare C_9_4 cursor for select jobid,[desc],lvl from #job open C_9_4

fetch next from C_9_4 into @Jobid,@desc,@lvl while @@fetch_status=0 begin

if(exists(select * from #N_Job where lvl=@lvl)) begin

update #N_Job set [desc]=[desc]+','+@desc where lvl=@lvl end else begin

insert into #N_Job select @Jobid,@desc,@lvl end

fetch next from C_9_4 into @Jobid,@desc,@lvl end

close C_9_4

deallocate C_9_4

select * from #N_Job

三. 实验总结