建立使用者定義函式 (資料庫引擎)

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

本文說明如何使用 Transact-SQL 在 SQL Server 中建立使用者定義的函式 (UDF)。

限制事項

• 使用者定義的函式不能用來執行修改資料庫狀態的動作。

• 使用者定義的函式不得包含具有資料表作為其目標的 OUTPUT INTO 子句。

• 使用者定義的函式無法傳回多個結果集。 如果您需要傳回多個結果集，請使用預存程序。

• 使用者定義函數中限制錯誤處理。 UDF 不支援 TRY...CATCH、@ERROR 或 RAISERROR。

• 使用者定義的函式無法呼叫預存程序，但是可以呼叫擴充預存程序。

• 使用者定義的函式無法利用動態 SQL 或暫存資料表。 允許使用資料表變數。

• 使用者定義的函式中不允許使用 SET 陳述式。

• 不允許 FOR XML 子句。

• 使用者定義函數可以具有巢狀結構；也就是說，某個使用者定義函數可以呼叫另一個使用者定義函數。 被呼叫的函數開始執行時，巢狀層級會遞增；被呼叫的函數完成執行時，巢狀層級會遞減。 使用者定義函數所建立的巢狀結構最多可以有 32 個層級。 超過巢狀層級上限會導致整個呼叫函數鏈結失敗。 依照 32 個層級巢狀限制，Transact-SQL 使用者定義函數之 Managed 程式碼的任何參考都算是一個層級。 若是從受控碼內叫用的方法，則不計入這項限制。

• 下列 Service Broker 陳述式 無法包含在 Transact-SQL 使用者定義函數的定義中：

  • BEGIN DIALOG CONVERSATION
  • END CONVERSATION
  • GET CONVERSATION GROUP
  • MOVE CONVERSATION
  • RECEIVE
  • SEND

權限

需要資料庫中的 CREATE FUNCTION 權限，以及建立此函式所在結構描述上的 ALTER 權限。 如果此函式指定使用者定義型別，則需要該型別的 EXECUTE 權限。

純量函數範例

純量函式 (純量 UDF)

下列範例會在 AdventureWorks2022 資料庫中建立多重陳述式的純量函式 (純量 UDF)。 這個函數使用了一個輸入值 ProductID，並傳回單一資料值，也就是指定產品的彙總存貨量。

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 之間的產品目前的存貨量。

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

如需純量函數的詳細資訊和範例，請參閱＜CREATE FUNCTION＞。

資料表值函式範例

內嵌資料表值函式 (TVF)

下列範例會在 AdventureWorks2022 資料庫中建立內嵌資料表值函式 (TVF)。 這個函數使用了一個輸入參數，也就是客戶 (商店) 識別碼，並傳回 ProductID和 Name資料行，以及從年初至今將每項產品銷售給商店的彙總銷售額 YTD Total 。

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。

SELECT * FROM Sales.ufn_SalesByStore (602);

多重陳述式資料表值函式 (MSTVF)

下列範例會在 AdventureWorks2022 資料庫中建立多重陳述式的資料表值函式 (MSTVF)。 這個函數使用單一輸入參數 EmployeeID ，並傳回一份清單，列出這位指定員工的所有直接或間接下屬。 然後叫用此函數並指定員工識別碼 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。

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

如需內嵌資料表值函數 (內嵌 TVF) 和多重陳述式資料表值函數 (MSTVF) 的詳細資訊，請參閱＜CREATE FUNCTION＞。

最佳作法

如果未以 SCHEMABINDING 子句建立使用者定義函式 (UDF)，叫用該函式時，對基礎物件所進行的變更可能會影響函式的定義並產生非預期的結果。 建議您實作下列其中一個方法，以確保函式不會因為其基礎物件的變更而變成過期：

• 當您要建立 UDF 時，指定 WITH SCHEMABINDING 子句。 這可以確保系統無法修改函式定義中參考的物件 (除非同時修改函式)。

• 在修改 UDF 定義中指定的任何物件之後，執行 sp_refreshsqlmodule 預存程序。

如果建立無法存取資料的 UDF，請指定 SCHEMABINDING 選項以防止查詢最佳化工具針對涉及這些 UDF 的查詢計劃產生不必要的多工緩衝處理運算子。 如需多工緩衝處理的詳細資訊，請參閱執行程序表邏輯和實體運算子參考。 如需建立結構描述繫結函式的詳細資訊，請參閱結構描述繫結的函式。

雖然您可以聯結至 FROM 子句中的 MSTVF，但是效能可能不佳。 SQL Server 無法針對可包含在 MSTVF 中的某些陳述式使用所有最佳化技術，因此會產生次佳查詢計劃。 若要獲得最佳效能，請盡可能在基底資料表而非函數之間使用聯結。

MSTVF 在 SQL Server 2014 (12.x) 的固定基數估計值為 100，更舊的 SQL Server 版本則為 1。

從 SQL Server 2017 (14.x) 開始，最佳化使用 MSTVF 的執行計畫可以使用交錯執行，這會導致使用實際的基數，而不是上述啟發學習法。

如需詳細資訊，請參閱交錯執行多重陳述式資料表值函式。

在預存程序或使用者定義函式中傳遞參數，或在批次陳述式中宣告和設定變數時，不接受 ANSI_WARNINGS。 例如，若將變數定義為 char(3) ，然後將其設為大於三個字元的值，資料便會被截斷成定義的大小，且 INSERT 或 UPDATE 陳述式會執行成功。

相關內容

• 使用者定義的函式
• CREATE FUNCTION (Transact-SQL)
• ALTER FUNCTION (Transact-SQL)
• DROP FUNCTION (Transact-SQL)
• DROP PARTITION FUNCTION (Transact-SQL)