建立使用者定義函數 (Database Engine)Create User-defined Functions (Database Engine)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主題描述如何使用 SQL ServerSQL Server ,在 Transact-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. 依照 32 個層級巢狀限制,Transact-SQL 使用者定義函數之 Managed 程式碼的任何參考都算是一個層級。Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. 從 Managed 程式碼內叫用的方法,不列入這項限制。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. 這個函數使用了一個輸入參數,也就是客戶 (商店) 識別碼,並傳回 ProductIDName資料行,以及從年初至今將每項產品銷售給商店的彙總銷售額 YTD TotalThe 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  
);  

下例會叫用這個函數並指定客戶識別碼 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. 然後叫用此函數並指定員工識別碼 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  

下列範例會叫用此函式並指定員工識別碼 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

如果未以 SCHEMABINDING 子句建立使用者定義函式 (UDF),叫用該函式時,對基礎物件所進行的變更可能會影響函式的定義並產生非預期的結果。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 的查詢計劃,產生不必要的多工緩衝處理運算子。This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these UDFs. 如需多工緩衝處理的詳細資訊,請參閱執行程序表邏輯和實體運算子參考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.

重要

MSTVF 從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始具有固定的基數估計值 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