数据库原理与应用实验4

实验4 用户自定义函数实验
【实验目的】
1、掌握SQL语句中的用户自定义函数
【实验内容】
1.在企业管理器中还原student数据库，还原数据库所用的文件为studentbak
2.标量函数实验：
✧创建计算圆面积的函数area。

输入参数为圆的半径，类型为整形。

返回值为浮点型。

Create function dbo.area
(@radius int )
Returns float
As
Begin
Return (3.14*@radius*@radius)
End
Select dbo.area(4)
✧创建分段函数mark，输入参数为成绩，类型为整型。

返回值为该成绩所在的分数
段，分数段的划分为0-59，60-69，70-79，80-89，90-100
Create function dbo.mark
(@grade int )
Returns char(10)
As
Begin
Declare @res char(10)
Select @res = (case when @grade between 90 and 100 then '90-100'
when @grade between 80 and 89 then '80-89'
when @grade between 70 and 79 then '70-79'
when @grade between 60 and 69 then '60-69'
when @grade between 0 and 59 then '0-59'
End )
Return @res
End
✧创建指定课程指定专业的最高分的函数maxgrade，输入为课程名称和专业名称，
类型均为字符串，返回值为整型。

Create function dbo.maxgrade
(@cn char(10) , @major char (10) )
Returns int
As
Begin
Declare @res int
Select @res = max(grade)
From student s join sc on sc.sno = s.sno
Join course c on o = o
Where cname =@cn and smajor = @major
Return @res
End
3、内嵌表值函数实验
✧创建查询选修指定课程（课程名）的学生的姓名和所在专业的内嵌表值函数f1。

Create function dbo.f1
(@cn char (10) )
Returns table
As
Return (select sname, smajor
From student s join sc on s.sno=sc.sno
Join course c on o=o
Where cname = @cn)
Select * from dbo.f1 ('常微分方程')
✧创建查询在周几上课的课程清单的内嵌表值函数f2。

输入为形如一二三的字符串，
输出列包括tc表中的所有列，要求输出按照上课的时间排序。

例如先是12节，然
后是34节。

Create function dbo.f2
(@weekday char(4) )
Return table
As
Return ( select top 20 cno, cname, teacher, number, classtime, classposition, startweek, endweek, odd_even
From tc where substring( classtime, 2, 1) = @weekday
order by classtime)
4、多语句表值函数实验
创建多语句表值函数f3，查询指定课程指定成绩级别的学生考试情况。

输入参数为课程名称和成绩级别，成绩级别分为优、良、中、及格和不及格五档，分别对应
90-100，80-89，70-79，60-69，<60。

输出列包括学号，姓名，专业，课程名，成
绩
create function dbo.f3
(@cnamec char(20),@level char(20))
returns @gradelevel table
(sno char(20),sname char(20),smajor char(20),cname char(20),grade int)
as
begin
if @level ='优'
insert into @gradelevel
select sc.sno,sname,smajor,cname,grade
from sc join student s on sc.sno=s.sno join course c on o=o
where @cnamec=cname and grade between 90 and 100
else if @level='良'
insert into @gradelevel
select sc.sno,sname,smajor,cname,grade
from sc join student s on sc.sno=s.sno join course c on o=o
where @cnamec=cname and grade between 80 and 89
else if @level='中'
insert into @gradelevel
select sc.sno,sname,smajor,cname,grade
from sc join student s on sc.sno=s.sno join course c on o=o
where @cnamec=cname and grade between 70 and 79
else if @level='及格'
insert into @gradelevel
select sc.sno,sname,smajor,cname,grade
from sc join student s on sc.sno=s.sno join course c on o=o
where @cnamec=cname and grade between 60 and 69
else if @level='不及格'
insert into @gradelevel
select sc.sno,sname,smajor,cname,grade
from sc join student s on sc.sno=s.sno join course c on o=o
where @cnamec=cname and grade <60
return
end。