SQL SERVER 用户自定义函数（UDF）深入解析

本文内容概要：

1. udf 概念、原理、优缺点、udf 的分类

2. 详细讲述3种 udf 的创建、调用方法以及注意事项

3. udf 的实践建议

---

基本原理：

udf：user-defined functions，用户自定义函数的简称。

udf 是一个例程，它接受参数、执行操作并返回该操作的结果。根据定义，结果可以是标量值（单个）或表。

udf 的优点：

1. udf 可以把复杂的逻辑嵌入到查询中。udf 可以为复杂的表达式创建新函数。
2. udf 可以运用在一个表达式或 select 语句的 from 子句中，并且还可以绑定到架构。此外，udf 还可以接受参数。udf 有助于实施一致性和可重用性。

udf 的缺点：

该函数一旦误用会产生潜在的性能问题。必须针对where子句的每一行执行的任何函数，不管是用户定义的函数还是系统函数，都将减慢执行速度。

udf 的类型：

        udf 主要有 3 种类型（sql server management studio 把内联表值函数与多语句表值函数放到了一个组中）：

1. 标量函数
2. 内联表值函数
3. 多语句表值函数

一、标量函数

---

标量函数是返回一个具体值的函数。函数可以接收多个参数、执行计算然后返回一个值。返回值通过return命令返回。用户定义的函数中的每个可能代码路径都以return命令结尾。

标量函数可以运用于 sql server 中的任何表达式，甚至在 check 约束的表达式中也可以使用（但不推荐这种用法）。

• 函数限制

标量函数必须是确定性的，也就是说标量函数必须反复地为相同的输入参数返回相同的值。因此，如newid()函数和rand()函数不允许出现在标量函数中。不允许用户定义标量函数更新数据库、调用存储过程或调用dbcc命令，唯一的例外是可以更新表变量。用户定义函数不能返回blob（二进制大型对象）数据，如text、next、timestamp和image数据类型变量。也不能返回表变量可cursor数据类型。对于错误处理，udf 也不包含 try...catch 或 raiserror。

udf 可以调用嵌套深度为 32 层以内的其他用户定义函数，或者递归调用自己到 32 层的深度。当然，这只是理论限制，嵌套函数会严重影响性能，应尽可能避免使用嵌套函数。

• 创建方法

1 create function functionname (inputparameters) 
2 returns datatype 
3 as
4 begin
5   code;
6   return expression;
7 end;

inputparameters 输入参数包含数据类型定义。参数可以设置默认值（parameter = default )，需要注意的是在 udf 中有默认值的参数并不能成为可选参数，为在调用函数时请求到默认值，需要把关键字 default 传递到函数的默认值参数位置。

示例1：下面的 udf 执行一个简单的数学计算，其中第二个参数带有默认值。

create function dbo.ufncalculate
(@numer_a numeric(5,2),
 @numer_b numeric(5,2) = 1.0)
returns numeric(5,2)
as
begin
  return @numer_a / @numer_b ;
end;
go

select dbo.ufncalculate(15.3 , 6.54),
       dbo.ufncalculate(9.0 , default);

结果：
------  ------
2.38      9.00

示例2：计算并返回某个时间所在月份的天数。

create function [dbo].[getmonthday](@date datetime)
returns int
as 
begin
  declare @date1 datetime
  select @date1 =dateadd(mm,1,@date)
  return day(dateadd(dd,-day(@date1),@date1))
end;

• 调用方法

在接受单值的表达式中，标量函数可用于任何地方。用户定义的标量函数必须通过一个最少有两部分的名称（所有者.函数名）来调用。

下面的脚本演示了在数据库的订单表中调用示例2中的函数及其返回值。

select s.bil_dd,dbo.getmonthday(bil_dd) as days_m 
from orders s

结果
bil_dd        days_m
------        ------
2019-01-31     31
2019-02-15     28

二、内联表值函数

---

与视图相似，内联表值函数也是为一个存储的select语句封装。内联表值函数保留了视图的优点，还添加了一些参数。

• 创建方法

内联表值用户定义函数没有begin / end主体。select语句是作为一个虚拟数据表返回的：

create function functionname （inputparameters）
returns table 
as 
return (select statement)；

示例：下面的示例返回某个客户所订购产品的汇总情况。

create function dbo.ufngetproducttotalbycust (@custno varchar (10))
returns table 
as
return(
select h.cus_no,b.prd_no,sum(b.qty) as total_prd 
from tf_pos as b       --订单货品明细表
left join mf_pos  as h --订单客户信息表
       on h.os_no=b.os_no 
where h.cus_no=@custno  
group by h.cus_no,b.prd_no );
go

• 调用方法

通过dbo.ufngetproducttotalbycust查询客户代号为"ct060228" 的产品汇总数据，函数出现在select语句的from部分：

select prd_no,total_prd from 
dbo.ufngetproducttotalbycust('ct060228') 
order by prd_no desc 

返回结果（部分）：

prd_no           total_prd
------------   ------------------
10910030006      5792.00000000
10910040003      10776.00000000
10912060014      11442.00000000
10913040009      9276.00000000
11410030028      900.00000000
......

• 与视图的关系

与视图相比，内联表值函数的优势在于其可以使用参数。而视图不包含参数，而且在运行时想要限制结果需要把 where 子句添加到调用视图的 select 语句中来实现。

示图的调用示例，假设已经存在视图 dbo.vwproducttotalbycust，调用视图时，在 select 语句中添加了一个 where 子句限制：

select * from dbo.vwproducttotalbycust where cus_no='ct060228' 

• 关联方法

表值用户定义函数的关联可以使用 apply 命令，从而使 udf 针对由主查询处理的每一行接受一个不同的参数值。

apply 命令具有两种形式。最普通的一种形式是 cross apply，它运行起来更像一个内联接。cross apply 命令联接主查询的数据与来自用户自定义函数的任意表值数据集。如果未从udf 返回数据，那么主查询的行也不能返回，如下图的例子所示：

select t.prd_no,p.name,t.total_prd 
from prdt p --产品资料表
cross apply dbo.ufngetproducttotalbycust('ct060228')  t
order by t.prd_no desc 

结果:

prd_no         name               total_prd
------------  ------------        ------------------------
10910030006   3pcs storage jar    5792.00000000
10910040003   2pcs storage jar    10776.00000000
10912060014   4pcs spice jar      11442.00000000
10913040009   6pcs spice jar      9276.00000000
11410030028   salad dressing      900.00000000
......

cross apply 的第2种形式是 outer apply 命令，操作上与左联接相似。这种形式下，主查询的行将包含在结果集中，而不管 udf 返回的虚拟表是否为空。

标量函数和内联表值函数可生成完成相同的结果集，那么这两者的区别是什么呢？

标量函数针对每一行运行一次，而内联表值函数由查询优化器处理，非常类似于视图。因为内联表值函数会由查询优化器进行处理，所以建议尽可能优先使用内联表值函数，而非标量函数。

• 架构绑定

架构绑定阻止更改或删除函数所依赖的任何对象。如果架构绑定函数引用了某个表a，那么表a不可更改或删除，但可以将列添加到表a。

架构绑定的方法：在函数创建语句的 returns 之后和 as 之前添加选项 with schema binding，如下所示：

1 create function functionname (inputparameters) 
2 returns datatype 
3 with schema binding 
4 as
5 begin
6   code;
7   return expression;
8 end;

可以使用alter修改函数，使其不再包含架构绑定，以便可以修改引用对象。

三、多语句表值函数

---

将标量函数与内联表值函数的功能结合起来就构成了复杂的多语句表值函数。

特征：这种类型的函数创建了一个表变量，将它置于代码中，然后从函数返回，以便能在select语句中使用。

优点：可以代码内生成复杂结果集，以便在select语句中使用，在查询中构建复杂逻辑，并解决那些没有游标就很难解决的问题。

• 创建方法

创建多语句表值函数的语法与创建标量函数的语法相似：

create function functionname （inputparamenters）
returns @tablename table (columns)
as
begin; 
    code to populate table variable
    return;
end;

示例：下面的过程构建了一个返回基本结果集的多语句表值用户定义的函数，函数首先在 create function 头中创建了一个名为 @pruductlist 的表变量，在函数体中，两个 insert 语句置于@productlist 表变更中，如果函数执行完毕，表变更 @productlist 将作为函数的输出传回。

ufngetproductsandordertotals函数返回product表中的每个产品和每个产品的订单总数。

create function ufngetproductsandordertotals() 
returns @productlist table 
            (productid int,
             productname nvarchar(100),
             totalorders int)
as
begin;
        insert @productlist（productid，productname） 
        select productid，name 
        from product;

        update p1 
        set totalorders = 
            (select sum(sod.orderqty) 
             from @productlist ip1 
               join salesorderdetail sod 
               on ip1.productid = sod.productid
             where ip1.productid = p1.productid) 
        from @productlist p1 ;

        return;
end;

• 调用方法

只需要在select语句的from部分引用该函数，即可查询到函数的执行结果。下面的代码检索ufngetproductsandordertotals函数的结果：

select productid，productname，totalorders 
from ufngetproductsandordertotals（） 
order by totalorders desc 

结果集如下：

productid        productname                totalorders
------------       -------------------          --------------
715                 4 pcs storage jar           8311
780                 6 pcs spice jar             6800
......

---

四、udf 的实践建议

无疑 udf 为我们的 t-sql 选项添加了灵活性，但如果这些函数运用不当，带来的性能缺陷也是很严重的。udf 并不能成为子查询、视图或存储过程的替代物。

从上面的示例，我们不难看出，三种类型函数可以产生基本相同的结果集，实践中可以将自己的函数定义为其种任意一种。

建议一：性能最优化

如果选择 udf 来封装查询逻辑，则建议遵循下面的这些基本原则：

1. 相对于多语句表值函数，尽可能优先选择内联表值函数；
2. 尽量避免使用标量函数，尽可能使用内联表值函数取代它；
3. 如果需要使用多语句表值函数，则对比一下存储过程是不是更合适的解决文案。虽然需要花更多的时间，但考虑长期的性能影响，还是值得的。

建议二：命名一致性

为方便我们的t-sql更易于阅读更容易排除故障，我们应该确保为所有的 udf 创建某种统一类型的命名约束。最常用的方法是采用名称前缀，更进一步，可以让前缀表明 udf 是标量函数、内联表值函数还是多语句表值函数。例如，返回每个产品类别的月平均销售额的内联表值函数，可以将其命名为 udfavgmonsalespercategory 或 ifn_avgmonsalespercategory。