UDF的定义

定义：和存储过程很类似，用户自定义函数是一组有序的T-SQL语句，这些语句被预先优化和编译，并且昨晚一个单元进行调用。Upf和存储过程的主要区别在于结果的返回方式，为了能支持多种不同的返回值，UDF比存储过程有更多的限制。使用存储过程时可传入参数，也可以传出参数。可以返回值，不过该值用于指示成功或失败，而非返回数据。也可以返回结果集，但是在没有将结果集插入到某种表（通常是临时表）中以供后面的情况下，不能查询中真正使用它们。然而，使用UDF时可传入参数，但不可传出参数。输入参数的概念被更为健壮的返回值取代了。和系统函数一样，可以返回标量值，这个值的好处是它并不像在存储过程中那样只限于整型数据，而是可以返回大多数的SQLServer 数据类型。

UDF返回值有以下两种类型。

●返回标量值的UDF

●返回表的UDF

返回标量值的UDF

UDF的真正有点在于返回值并不限于整数，而是可以返回除了BLOB、游标（cursor）和时间戳(timestamp)以外的任何有效的SQL Server数据类型（包括用户自定义类型）。即使想返回整数，UDF也有一下两个吸引人的方面：

●与存储过程不同，用户自定义函数返回值的目的是提供有意义的数据；而对于存储过程来说，返回值只是说明成功和失败，如果失败，则会提供一些关于失败性质的特定信息。

●可在查询中内联执行函数(例如，作为SELECT语句的一部分包含它)，而使用存储过程则不行。

案例：

--select * from Orders where OrderDate between DATEADD(mm,3,SYSDATETIME()) and DATEADD(mm,15,SYSDATETIME())
--declare @YearEnding date = DATEADD(MONTH,15,SYSDATETIME());
--select * from Orders where OrderDate between DATEADD(day,1,DATEADD(year,-1,@YearEnding)) and @YearEnding;

创建标量值的UDF

declare @YearEnding DATE = DATEADD(MONTH,15,SYSDATETIME());

测试标量值的UDF
select *from Orders where dbo.IsInFiscalYear(@YearEnding,OrderDate)=1;

注意：标量值函数必须采用架构

嵌套的内置函数

---UDF嵌套使用
/*select
Name,
ListPrice,
(select AVG(ListPrice) from Production.Product) as Average,
ListPrice-(select AVG(ListPrice) from Production.Product) as Differences
from Production.Product
where ProductSubcategoryID = 1;*/

/*--创建平均值udf
create function dbo.AvarigeUdf()
returns money
with schemabinding--一旦视图被指定了WITH SCHEMABINDING 选项，那么，在修改用于生成当前视图的表或视图时，一旦对当前视图产生影响（导致视图失效），则不允许修改。
as
begin
return (select AVG(ListPrice) from Production.Product);
end

--创建价格减去平均值
GO
create function dbo.DifferencesUdf(@ListPrice money)
returns money
as
begin
return @ListPrice- dbo.AvarigeUdf();
end
GO*/
select Name,ListPrice,dbo.AvarigeUdf() as Avarige,dbo.DifferencesUdf(ListPrice) as Differences from Production.Product where ProductSubcategoryID = 1;

返回表的UDF

内联UDF

案例1：创建简单的返回表的UDF

--创建返回表的UDF
create function dbo.fnContactList()
returns table
as
--不能用begin/end
return (select BusinessEntityID,LastName+','+FirstName as Name from Person.Person);
go
--测试返回内容
select *from dbo.fnContactList();

案例2：给返回表添加where条件

--创建视图
create view vFullContactName
as
select p.BusinessEntityID,LastName+','+FirstName as Name,ea.EmailAddress from Person.Person as p left join Person.EmailAddress ea on ea.BusinessEntityID = p.BusinessEntityID;
go
--视图不能参数化
select * from vFullContactName where Name LIKE 'Ad%';

注意：视图不能参数化，视图比较适合静态的

--创建内置函数
create function udfFullContactName(@StrWhere varchar(50))
returns table
as
return (select p.BusinessEntityID,
LastName+','+FirstName as Name,
ea.EmailAddress
from Person.Person as p
left join Person.EmailAddress ea on
ea.BusinessEntityID = p.BusinessEntityID
where LastName like @StrWhere+'%');
go
--测试函数
select * from udfFullContactName('Ad');

注意：虽然存储过程也可以实现但是不能把存储过程的结果和其他表连起来。

内置函数可以返回使用多条创建的表——唯一的区别在于必须对需要返回的元数据（就像临时表一样）进行命名和定义。

案例：递归调用

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

create function dbo.fnGetReports
(@EmployeeID AS int)
returns @Reports table(
EmployeeID int not null,
ManagerID int null
)
as
begin
declare @Employee as int;
insert into @Reports
select EmployeeID,ManagerID
from HumanResources.Employee2
where EmployeeID =@EmployeeID;
select @Employee = MIN(@EmployeeID)
from HumanResources.Employee2 where ManagerID = @EmployeeID;
while @Employee is not null
begin
insert into @Reports
select * from fnGetReports(@Employee);
select @Employee = MIN(EmployeeID)
from HumanResources.Employee2
where EmployeeID > @Employee
and ManagerID = @EmployeeID;
end
return;
end
go
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
测试

select * from fnGetReports(3);

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
declare @EmployeeID int;
select
@EmployeeID = EmployeeID
from HumanResources.Employee2 e where LastName = 'Huntington'
and FirstName = 'Karla';
select
e.EmployeeID,
e.LastName,
e.FirstName,
m.LastName as ReportTo
from HumanResources.Employee2 as e
join dbo.fnGetReports(@EmployeeID) as r
on e.EmployeeID = r.EmployeeID
join HumanResources.Employee2 as m
on m.EmployeeID = r.ManagerID;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

理解确定性

目的：有一些函数需要为将创建索引的对象（例如计算列或索引视图）提供数据。

定义：用户自定义函数可以是确定的，也可以是非确定性的。确定性并不是根据任何参数类型定义的，而是根据SQL Server 表名的函数的功能定义的，如果给定了医嘱特定的有效输入，每次函数都能返回相同的结果，就是该函数是有确定性的，SUN()就是一个确定性的内置函数。但是GETDATE()的值是非确定性的，因为每次调用它的时候GETDATE()都会改变。

为了达到确定性的要求，函数必须满足一下4个条件。

● 函数必须是架构绑定的。这意味着函数所依赖的任何对象会有一个依赖记录，并且在没有删除这个依赖函数之前都不允许改变这些对象。

● 函数应用的所有其他函数，无论其是用户定义的还是系统定义的，都必须是确定性的。

● 不能引用在函数外部定义的表（可以使用表变量和临时表，只要他们是在函数作用域内定义就行）。

●不能使用扩展存储过程。

确定性的重要性在于它显示了是否在视图或计算机列上建立索引。只有在能够可靠的确定视图或计算列的结构时，才允许在视图或计算咧上建立索引。这意味着，如果视图或计算机引用非确定性函数，则在该视图或裂伤将不允许建立任何索引。

判断函数是否是确定性：

select OBJECTPROPERTY(OBJECT_ID('函数名-不包含括号和参数'),'IsDeterministic');

结果中，0为假非0为真

确定函数案例：

alter function IsInFiscalYear(@YearEnding DATE,@CompareDate DATE)
returns bit
with schemabinding
as
begin
declare @out bit =0;
if @CompareDate between
DATEADD(day,1,DATEADD(YEAR,-1,@YearEnding)) and @YearEnding
set @out =1

return @out;
end

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create function dbo.salesOrderDate(@SalesOrderID int)
returns date
with schemabinding
as
begin
declare @SODate DATE;
SELECT @SODate = OrderDate from Sales.SalesOrderHeader
where SalesOrderID = @SalesOrderID;
return @SoDate;
end
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--select OBJECTPROPERTY(OBJECT_ID('salesOrderDate'),'IsDeterministic');--检验是否是确定性函数

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create function dbo.salesOrderAge(@SalesOrderID int,@CurrentDate date)
returns int
with schemabinding
as
begin
declare @SODate Date,@SOAge INT;
set @SODate = dbo.salesOrderDate(@SalesOrderID);
set @SOAge = DATEDIFF(DAY,@SODate,@CurrentDate);
return @SOAge;
end
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select objectproperty(object_id('salesOrderAge'),'IsDeterministic');

Biegral Blog

用户自定义函数