创建用户定义的函数

5 分钟

用户定义的函数（UDF）类似于存储过程，因为它们与数据库中的表分开存储。这些函数接受参数、执行动作，然后将动作结果作为单个（标量）值或结果集（表值化）返回。然后，可以在编写 SELECT 语句时使用该函数代替表。用户定义的函数旨在执行计算并在另一个语句中使用该结果。而存储过程可以封装函数和语句，甚至可以修改数据库中的数据。

我们将查看三种类型的用户定义函数。有关不同函数的更多详细信息，请查看 T-SQL 参考文档。

内联表值函数

内联表值函数（TVF）是基于 SELECT 语句创建的最简单的函数，是性能的首选。

在以下示例中，使用 unitprice 的输入参数创建表值函数。

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost;

当表值函数使用参数的值运行时，将返回大于此值的单价的所有产品。

以下代码使用表值函数代替表。

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

多语句表值函数

与内联 TVF 不同，多语句表值函数（MSTVF）可以具有多个语句，并且具有不同的语法要求。

请注意以下代码的用法，除了 RETURN 之外，我们还使用了 BEGIN/END。

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

创建后，可以引用 MSTVF 来替代表，就像上文中的上一个内联函数一样。还可以引用 FROM 子句中的输出，并将其与其他表联接。

SELECT *
FROM Sales.mstvf_OrderStatus();

性能注意事项

查询优化器无法估计多语句表值函数将返回多少行，但可以估计内联表值函数返回的行数。因此，尽可能使用内联 TVF 来提高性能。如果不需要将 MSTVF 与其他表联接，或者知道结果只有几行，那么性能影响并不令人担忧。如果需要大型结果集并且需要与其他表联接，请考虑使用临时表来存储结果，然后联接到临时表。

在 SQL Server 版本 2017 及更高版本中，Microsoft引入了智能查询处理功能，以提高 MSTVF 的性能。请参阅 T-SQL 参考文档中有关智能查询处理功能的更多详细信息。

标量用户定义函数

标量用户定义函数仅返回与表值函数不同的一个值，因此通常用于简单、频繁的语句。

下面举例说明了如何获取特定产品在特定日期的产品列表价格：

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

对于此函数，必须提供这两个参数才能获取值。根据函数，可以在更复杂的查询中列出 SELECT 语句中的函数。

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

将函数绑定到引用的对象

创建函数时，SCHEMABINDING 是可选的。指定 SCHEMABINDING 时，它会将函数绑定到引用的对象，然后不能在不修改函数的情况下修改对象。必须先修改或删除该函数，才能在修改对象之前删除依赖项。

如果发生以下任一情况，则删除 SCHEMABINDING：

该函数已被弃用
使用 ALTER 语句修改函数，而无需指定 SCHEMABINDING

反馈

此页面是否有帮助？