SQL Server创建用户定义函数详解

UDF的定义与类型

用户定义函数 (User-Defined Function, UDF) 是一组经过预先优化和编译的 T-SQL 语句集合，可以像内置系统函数一样被调用。与存储过程相比，UDF 主要通过返回值传递数据，不支持传出参数。

根据返回值的不同，SQL Server 中的 UDF 主要分为以下两类：

• 标量值函数：返回单个数据值（如整数、字符串、日期等），但不支持 BLOB、游标或时间戳类型。
• 表值函数：返回一个表对象，可以直接在查询中被当作表使用，支持 JOIN 和 WHERE 过滤操作。这又细分为“内联表值函数”和“多语句表值函数”。

通用创建语法：

CREATE FUNCTION [<schema name>.]<function name>
(
    [ <@parameter name> [AS] [<schema name>.]<data type> [= <default value> [READONLY]] ]
)
RETURNS { <scalar type> | TABLE [(<table definition>)] }
[ WITH [ENCRYPTION] | [SCHEMABINDING] | ... ]
[AS] 
BEGIN
    -- 函数体逻辑
    RETURN <value_or_select_statement>
END

标量值函数的实现

标量值函数返回单一数据。其核心优势在于可以在 SELECT 语句或 WHERE 条件中直接调用，使得复杂逻辑可以作为列的一部分参与查询计算。

1. 基础示例：日期转换

假设需要将 DateTime 字段转换为 'YYYY.MM.DD' 格式的字符串用于精确匹配：

CREATE FUNCTION DateOnly(@Date DateTime)
RETURNS varchar(12)
AS
BEGIN
    RETURN CONVERT(varchar(12), @Date, 102)
END

在查询中使用该函数时，必须加上架构名称（通常是 dbo）以消除歧义：

SELECT * FROM Nx_comment 
WHERE dbo.DateOnly(com_posttime) = '2012.04.28'

2. 进阶应用：组合列计算

当需要在查询结果中动态计算某一列的值时，可以使用多个 UDF 相互嵌套。例如，计算人员年龄与平均年龄的差值：

-- 第一步：创建获取平均年龄的函数
CREATE FUNCTION dbo.AvgAge() RETURNS int
AS BEGIN
    RETURN (SELECT AVG(Age) FROM Person)
END
GO

-- 第二步：创建计算差值的函数
CREATE FUNCTION dbo.AgeDifference(@Age int) RETURNS int
AS BEGIN
    -- 在自定义函数内部引用另一个 UDF
    RETURN @Age - dbo.AvgAge()
END

执行查询：

SELECT Name, Age, 
       dbo.AvgAge() AS AvgAge, 
       dbo.AgeDifference(Age) AS Difference 
FROM Person

内联表值函数的应用

内联表值函数 (Inline Table-Valued Functions, iTVF) 返回的是一个由单条 SELECT 语句生成的表。它的语法相对简单，类似于创建一个视图，但允许传入参数进行动态过滤。这使得 iTVF 比视图更具灵活性，且通常比多语句函数有更好的性能表现。

示例：基于参数的表查询

创建一个函数，根据姓氏前缀筛选员工信息：

CREATE FUNCTION dbo.fnNameLike(@LName varchar(20))
RETURNS TABLE
AS
RETURN (
    SELECT Id, LastName + ',' + FirstName AS Name 
    FROM Man
    WHERE LastName LIKE @LName + '%'
)

调用方式如同访问普通表：

SELECT * FROM dbo.fnNameLike('刘')

多语句表值函数的实现

当无法用单条 SQL 语句生成结果集时，需要使用多语句表值函数 (Multi-statement TVF)。它通过预定义的表变量来暂存中间数据，最后将表变量作为结果返回。虽然功能强大，但由于执行计划较为复杂，性能通常低于内联函数。

示例：十进制转二进制/八进制/十六进制

此例展示了如何使用公用表表达式 (CTE) 递归计算位数，并利用循环逻辑拼接字符串：

Create FUNCTION F_TConversion(@NUM INT)
RETURNS @t_table TABLE (
    [Binary] varchar(64),
    Octal varchar(16),
    Hexadecimal varchar(8)
)
AS
BEGIN
    DECLARE @RESULT2 VARCHAR(500)='', @RESULT8 VARCHAR(500)='', @RESULT16 VARCHAR(500)=''; 
    
    -- 使用 CTE 进行递归除法取余运算
    WITH CTE AS( 
        SELECT @NUM/2 D2, @NUM%2 S2, @NUM/8 D8, @NUM%8 S8, @NUM/16 D16, @NUM%16 S16, 1 [INDEX] 
        UNION ALL 
        SELECT D2/2 , D2%2, D8/8 , D8%8, D16/16 , D16%16, [INDEX]+1 
        FROM CTE WHERE D2>0 
    ) 
    -- 从高位到低位拼接结果，注意处理空值和十六进制字母转换
    SELECT @RESULT2 += CAST(S2 AS VARCHAR(1))
          ,@RESULT8 += CASE WHEN D8=0 AND S8=0 THEN '' ELSE CAST(S8 AS VARCHAR(1)) END
          ,@RESULT16 += CASE WHEN D16=0 AND S16=0 THEN ''
                             ELSE CASE CAST(S16 AS VARCHAR(5))
                                  WHEN '10' THEN 'A' 
                                  WHEN '11' THEN 'B' 
                                  -- 其他字符省略...
                                  ELSE CAST(S16 AS VARCHAR(5))
                              END
                         END
    FROM CTE ORDER BY [INDEX] DESC
    
    -- 将最终结果插入到返回表中
    INSERT INTO @t_table SELECT @RESULT2, @RESULT8, @RESULT16
    RETURN 
END
GO

理解函数的确定性 (Determinism)

判断一个函数是否为“确定性”至关重要，因为这直接影响 SQL Server 能否为该函数涉及的表或视图建立索引。

• 确定性函数：给定相同的输入参数，每次调用永远返回相同的结果。例如：CONVERT(), LEN(), ABS()。
• 非确定性函数：输入相同，但输出可能随环境或时间变化。例如：GETDATE(), RAND(), USER_NAME()。

如果视图或计算列引用的函数是非确定性的，则不允许对其建立索引。可以通过 OBJECTPROPERTY 检查函数的属性：

SELECT OBJECTPROPERTY(OBJECT_ID('DateOnly'), 'IsDeterministic');

强制函数成为确定性：

如果在函数定义中使用了 WITH SCHEMABINDING（即架构绑定），函数将被锁定到其依赖的对象定义上，从而显著增加被判定为“确定性”的可能性。若发现之前的 DateOnly 函数被判为非确定性，可尝试重构：

ALTER FUNCTION dbo.DateOnly(@Date date)
RETURNS date
WITH SCHEMABINDING  -- 添加架构绑定
AS
BEGIN
    RETURN @Date
END

以上就是SQL Server创建用户定义函数（UDF）的详细内容，更多关于SQL Server数据库编程的资料请关注本站其它相关文章！