用户定义函数

一、为何使用用户定义函数

用户定义函数可以让用户针对特定应用程序问题提供解决方案。这些函数可以简单到计算一个值，也可以复杂到定义和实现业务处理规则。定义了这些函数以后，在需要的时候调用即可，如果业务处理的规则发生变化，只需要修改相应的函数，只要这些接口未发生变化，就可以保持原来的函数调用。

二、用户定义函数的类型

SQL Server有2种自定义函数：表值函数、标量值函数。

用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有1124个输人参数。当函数的参数有默认值时，调用该函数时必须指定默认DEFAULT关键字才能获取默认值。用户定义函数不支持输出参数。

• 标量函数。标量函数返回在RETURNS子句中定义的类型的单个数据值。返回类型可以是除text、 ntext、image、cursor 和 timestamp 之外的任何数据类型。不支持用户定义数据类型和非标量类型(如table或cursor)。
• 表值函数。表值函数返回table。对于内联表值函数，没有函数主体；表是单个SELECT语句的结果集。对于多语句表值函数，在 BEGIN…END语句块中定义的函数体包含一系列Transact-SQL语句，这些语句可生成行并将其插入将返回的表中。

1. 创建一个内嵌表值函数S_Score_FUN，根据学生姓名查询该生所有选课的成绩。

USE jxsk
GO
CREATE FUNCTION S_Score_FUN(@SNAME_IN CHAR(10))
RETURNS TABLE
AS
RETURN(SELECT CN,SCORE FROM SC,S,C
WHERE  SC.CNO=C.CNO AND SC.SNO=S.SNO AND SN=@SNAME_IN)
GO

SELECT * FROM SN_SCORE('赵亦')

2. 创建一个多语句表值函数ALL_Score_FUN，根据课程名查询所有选择该课程学生的成绩信息，包括:学号SNO、姓名SN、性别SEX、成绩SCROE。结果按成绩降序排列。

create function ALL_Score_FUN (@CNAME_IN char(10))
returns @ALL_SCORE_TAB table (SNO char(2) primary key,
                              SN char(8) not null,
                              SEX char(2),
                              SCORE tinyint)
as
begin
insert into @ALL_SCORE_TAB
select S.SNO, SN, SEX, SCORE
from S, SC, C
where S.SNO = SC.SNO and C.CNO = SC.CNO and CN = @CNAME_IN
return
end
go
 
select * from ALL_Score_FUN ('微机原理')
go

 3.设计一个距离计算函数fnGetDistance “根据手机上已知的客户经纬度坐标，查询5公里范围内的酒店”。在《酒店信息表》中有经度和纬度两个字段存储酒店坐标，计算客户坐标与酒店坐标之间的距离，返回5公里内的所有酒店信息。

USE jxsk
GO
CREATE FUNCTION fnGetDistance( @Longitude_IN FLOAT,@Latitude_IN FLOAT )
RETURNS TABLE
AS 
RETURN(SELECT HN FROM HOTEL
		WHERE SQRT(SQUARE(Longitude-@Longitude_IN)+SQUARE(@Latitude_IN-Latitude))<=5)
GO

USE jxsk
GO
SELECT * FROM fnGetDistance (12,13)
GO