自定义函数:根据返回值的类型可以分为标量函数(返回单个值)和表值函数(返回一个表)
Page 174;
SQL中的自定义函数总共有三种
(1)标量函数——标量值函数
语法结构:
CREATE FUNCTION function_name(@parameter_name parameter_data_type) --(@参数名 参数的数据类型)
RETURN date_type --返回返回值的数据类型
[WITH ENCRYPTION] --如果指定了 encryption 则函数被加密
[AS]
BEGIN
function_body --函数体
RETURN 表达式;
END
--标量函数实例
1)创建标量函数,计算指定课程号的课程的平均成绩,然后调用该函数查询某门课的平均成绩。*/
create function getavgdegreeofonecourse(@课程号 char(5))
returns float
begin
declare @result float
select @result=avg(degree)
from score
where score.cno=@课程号
return @result
end
(2)表值函数——内联表值函数
语法结构:
CREATE FUNCTION function_name(@parameter_name parameter_data_type) --(@参数名 参数的数据类型)
RETURN table --返回一张表
[AS]
RETURN( --返回下面空白部分所写语句中查询的表
)
特点:内敛表值函数支持在WHERE子句中使用参数
/*2)创建内联表值函数,查询指定学号的学生的选课情况(包括学号、姓名、课程号和成绩),然后调用该函数查询某位学生的选课情况,
再调用该函数将该生某门课的成绩改为100,重新查询该生成绩检查一下改过来了吗。 */
--创建内联表值函数
drop function getxuankeqingkuang
create function getxuankeqingkuang(@学号 char(5))
returns table
return(
select student.sno,student.sname,course.cno,score.degree
from student,course,score
where student.sno=score.sno and score.cno=course.cno
and student.sno=@学号
)
--查询该生选课情况
select *
from dbo.getxuankeqingkuang('101')
--修改该生某门课程的成绩
update dbo.getxuankeqingkuang('101') set degree=100 where cno='3-105'
--重新查询该生成绩
select *
from dbo.getxuankeqingkuang('101')
go
(3)表值函数——多语句表值函数
语法结构:
create function 名称
([{@参数名称 参数类型[=默认值]}[,n]])
returns @局部变量 table(参数名 参数类型)
[with encryption]
[as]
begin
函数体
return 函数返回值
end
相比于内联表值函数的优点:对数据进行多次筛选与合并,弥补了内联表值自定义函数的不足。
3)创建多语句表值函数,查询指定课程号的学生的选课情况(包括学号、姓名和成绩),
并将选了该课的学生成绩提高5分,然后调用该函数查询某门课的学生的选课及成绩修改情况。
create function duoyujuhanshu(@cno char(6))
returns @ss table(sn int,sname char(6),degree int)
begin
insert @ss select student.sno,sname,degree = degree + 5
from student,score
where student.sno = score.sno and score.cno = @cno
return
end
select * from duoyujuhanshu('3-105')
--drop function 自定义函数名 --删除标量函数