计科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~，则为“良好”；如果成绩在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  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所示。

表7-2 Job表的数据

    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

三. 实验总结