创建用户定义函数(数据库引擎)Create User-defined Functions (Database Engine)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题介绍了如何通过使用 SQL ServerSQL ServerTransact-SQLTransact-SQL中创建用户定义函数 (UDF)。This topic describes how to create a user-defined function (UDF) in SQL ServerSQL Server by using Transact-SQLTransact-SQL.

开始之前Before You Begin

限制和局限Limitations and restrictions

  • 用户定义函数不能用于执行修改数据库状态的操作。User-defined functions cannot be used to perform actions that modify the database state.

  • 用户定义函数不能包含将表作为其目标的 OUTPUT INTO 子句。User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

  • 用户定义函数不能返回多个结果集。User-defined functions can not return multiple result sets. 如果您需要返回多个结果集,请使用存储过程。Use a stored procedure if you need to return multiple result sets.

  • 在用户定义函数中,错误处理受限制。Error handling is restricted in a user-defined function. UDF 不支持 TRY...CATCH@ERRORRAISERRORA UDF does not support TRY...CATCH, @ERROR or RAISERROR.

  • 用户定义函数不能调用存储过程,但是可调用扩展存储过程。User-defined functions cannot call a stored procedure, but can call an extended stored procedure.

  • 用户定义函数不能使用动态 SQL 或临时表。User-defined functions cannot make use of dynamic SQL or temp tables. 允许表变量。Table variables are allowed.

  • 在用户定义函数中不允许 SET 语句。SET statements are not allowed in a user-defined function.

  • 不允许使用 FOR XML 子句。The FOR XML clause is not allowed.

  • 用户定义函数可以嵌套;也就是说,用户定义函数可相互调用。User-defined functions can be nested; that is, one user-defined function can call another. 被调用函数开始执行时,嵌套级别将增加;被调用函数执行结束后,嵌套级别将减少。The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. 用户定义函数的嵌套级别最多可达 32 级。User-defined functions can be nested up to 32 levels. 如果超出最大嵌套级别数,整个调用函数链将失败。Exceeding the maximum levels of nesting causes the whole calling function chain to fail. 从 Transact-SQL 用户定义函数对托管代码的任何引用都将根据 32 级嵌套限制计入一个级别。Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. 从托管代码内部调用的方法不根据此限制进行计数。Methods invoked from within managed code do not count against this limit.

  • 下列 Service Broker 语句不能包含在 Transact-SQLTransact-SQL 用户定义函数的定义中:The following Service Broker statements cannot be included in the definition of a Transact-SQLTransact-SQL user-defined function:

    • BEGIN DIALOG CONVERSATION

    • END CONVERSATION

    • GET CONVERSATION GROUP

    • MOVE CONVERSATION

    • RECEIVE

    • SEND

权限Permissions

需要在数据库中具有 CREATE FUNCTION 权限,并对创建函数时所在的架构具有 ALTER 权限。Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. 如果函数指定用户定义类型,则需要对该类型具有 EXECUTE 权限。If the function specifies a user-defined type, requires EXECUTE permission on the type.

标量函数Scalar Functions

下面的示例在 AdventureWorks2012AdventureWorks2012 数据库中创建一个多语句标量函数( 标量 UDF)。The following example creates a multi-statement scalar function (scalar UDF) in the AdventureWorks2012AdventureWorks2012 database. 此函数输入一个值 ProductID,而返回一个单个数据值(指定库存产品的聚合量)。The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
GO  
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int   
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)   
    FROM Production.ProductInventory p   
    WHERE p.ProductID = @ProductID   
        AND p.LocationID = '6';  
     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END; 

下例使用 ufnGetInventoryStock 函数返回 ProductModelID 介于 75 和 80 之间的产品的当前库存量。The following example uses the ufnGetInventoryStock function to return the current inventory quantity for products that have a ProductModelID between 75 and 80.

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply  
FROM Production.Product  
WHERE ProductModelID BETWEEN 75 and 80;  

备注

有关详细信息,请参阅 CREATE FUNCTION (Transact-SQL)For more information and examples of scalar functions, see CREATE FUNCTION (Transact-SQL).

表值函数Table-Valued Functions

下面的示例在 AdventureWorks2012AdventureWorks2012 数据库中创建内联表值函数 (TVF)。The following example creates an inline table-valued function (TVF) in the AdventureWorks2012AdventureWorks2012 database. 此函数的输入参数为客户(商店)ID,而返回 ProductIDName以及 YTD Total (销售到商店的每种产品的本年度节截止到现在的销售总额)列。The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL  
    DROP FUNCTION Sales.ufn_SalesByStore;  
GO  
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
    FROM Production.Product AS P   
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
);  

下面的示例调用此函数并指定客户 ID 为 602。The following example invokes the function and specifies customer ID 602.

SELECT * FROM Sales.ufn_SalesByStore (602);  

下面的示例在 AdventureWorks2012AdventureWorks2012 数据库中创建多语句表值函数 (MSTVF)。The following example creates a multi-statement table-valued function (MSTVF) in the AdventureWorks2012AdventureWorks2012 database. 此函数具有一个输入参数 EmployeeID ,它返回直接或间接向指定员工报告的所有员工的列表。The function takes a single input parameter, an EmployeeID and returns a list of all the employees who report to the specified employee directly or indirectly. 然后在指定雇员 ID 109 的情况下调用此函数。The function is then invoked specifying employee ID 109.

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL  
    DROP FUNCTION dbo.ufn_FindReports;  
GO  
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)  
RETURNS @retFindReports TABLE   
(  
    EmployeeID int primary key NOT NULL,  
    FirstName nvarchar(255) NOT NULL,  
    LastName nvarchar(255) NOT NULL,  
    JobTitle nvarchar(50) NOT NULL,  
    RecursionLevel int NOT NULL  
)  
--Returns a result set that lists all the employees who report to the   
--specific employee directly or indirectly.*/  
AS  
BEGIN  
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns  
    AS (  
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n  
        FROM HumanResources.Employee e   
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        WHERE e.BusinessEntityID = @InEmpID  
        UNION ALL  
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor  
        FROM HumanResources.Employee e   
            INNER JOIN EMP_cte  
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode  
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        )  
-- copy the required columns to the result of the function   
   INSERT @retFindReports  
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
   FROM EMP_cte   
   RETURN  
END;  
GO  

下面的示例调用此函数并指定员工 ID 为 1。The following example invokes the function and specifies employee ID 1.

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);  

备注

有关内联表值函数(内联 TVF)和多语句表值函数 (MSTVF) 的详细信息和示例,请参阅 CREATE FUNCTION (Transact-SQL)For more information and examples of inline table-valued functions (inline TVFs) and multi-statement table-valued functions (MSTVFs), see CREATE FUNCTION (Transact-SQL).

最佳实践Best Practices

如果用户定义函数 (UDF) 不是使用 SCHEMABINDING 子句创建的,则对基础对象进行的任何更改可能会影响函数定义并在调用函数时可能导致意外结果。If a user-defined function (UDF) is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. 我们建议您实现以下方法之一,以便确保函数不会由于对于其基础对象的更改而过期:We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

  • 创建 UDF 时指定 WITH SCHEMABINDING 子句。Specify the WITH SCHEMABINDING clause when you are creating the UDF. 这确保除非也修改了函数,否则无法修改在函数定义中引用的对象。This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

  • 在修改在 UDF 定义中指定的任何对象后执行 sp_refreshsqlmodule 存储过程。Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the UDF.

如果要创建不访问数据的 UDF,请指定 SCHEMABINDING 选项。If creating a UDF that does not access data, specify the SCHEMABINDING option. 这将阻止查询优化器为涉及这些 UDF 的查询计划生成不必要的 spool 运算符。This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these UDFs. 有关 spool 的详细信息,请参阅 Showplan 逻辑运算符和物理运算符参考For more information on spools, see Showplan Logical and Physical Operators Reference. 有关创建架构绑定函数的详细信息,请参阅架构绑定函数For more information on creating a schema bound function, see Schema-bound functions.

可以在 FROM 子句中加入 MSTVF,但是会降低性能。Joining to an MSTVF in a FROM clause is possible, but can result in poor performance. SQL ServerSQL Server 无法对可以加入 MSTVF 的某些语句使用所有优化技术,导致生成的查询计划不佳。is unable to use all the optimized techniques on some statements that can be included in a MSTVF, resulting in a suboptimal query plan. 若要获得最佳的性能,尽可能在基表之间使用联接而不是函数。To obtain the best possible performance, whenever possible use joins between base tables instead of functions.

重要

SQL Server 2014 (12.x)SQL Server 2014 (12.x) 起,MSTVF 的固定基数猜测为“100”,而早期 SQL ServerSQL Server 版本为“1”。MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 1 for earlier SQL ServerSQL Server versions.
SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,如果要优化使用 MSTVF 的执行计划,可以利用交错执行,这样会使用实际基数而不是上述启发。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), optimizing an execution plan that uses MSTVFs can leverage interleaved execution, which results in using actual cardinality instead of the above heuristics.
有关详细信息,请参阅多语句表值函数的交错执行For more information, see Interleaved execution for multi-statement table valued functions.

备注

在传递存储过程或用户定义函数中的参数时,或在声明和设置批语句中的变量时,不会遵守 ANSI_WARNINGS。ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. 例如,如果将一个变量定义为 char(3),然后将其值设置为大于三个字符,则数据会被截断为定义的大小,并且 INSERTUPDATE 语句可以成功执行。For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

另请参阅See Also

用户定义的函数 User-Defined Functions
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
ALTER FUNCTION (Transact-SQL) ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL) DROP FUNCTION (Transact-SQL)
DROP PARTITION FUNCTION (Transact-SQL) DROP PARTITION FUNCTION (Transact-SQL)
社区中提供了更多示例More examples in the community