Vytváření uživatelem definovaných funkcí (databázový stroj)

Článek
2025-03-05

platí pro:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Tento článek popisuje, jak vytvořit uživatelem definovanou funkci (UDF) v SQL Serveru pomocí jazyka Transact-SQL.

Omezení a restrikce

Uživatelem definované funkce nelze použít k provádění akcí, které upravují stav databáze.
Uživatelem definované funkce nemůžou obsahovat klauzuli OUTPUT INTO, která má jako cíl tabulku.
Uživatelem definované funkce nemůžou vracet více sad výsledků. Pokud potřebujete vrátit více sad výsledků, použijte uloženou proceduru.
Zpracování chyb je omezeno v uživatelem definované funkci. UDF nepodporuje TRY...CATCH, @ERROR ani RAISERROR.
Uživatelem definované funkce nemůžou volat uloženou proceduru, ale můžou volat rozšířenou uloženou proceduru.
Uživatelem definované funkce nemůžou používat dynamické tabulky SQL ani dočasné tabulky. Jsou povoleny proměnné tabulky.
Příkazy SET nejsou povoleny v uživatelsky definované funkci.
Klauzule FOR XML není povolená.
Uživatelem definované funkce lze vnořit; to znamená, že jedna uživatelem definovaná funkce může volat jinou. Úroveň vnoření se zvýší při spuštění vykonávání volané funkce a sníží se při dokončení provádění volané funkce. Uživatelem definované funkce je možné vnořit až do 32 úrovní. Překročení maximální úrovně vnoření způsobí selhání celého řetězce volaných funkcí. Všechny odkazy na spravovaný kód ze Transact-SQL uživatelem definované funkce se počítají jako jedna úroveň oproti limitu vnoření na úrovni 32. Metody vyvolané z spravovaného kódu se do tohoto limitu nezapočítávají.
Následující příkazy Service Broker nelze zahrnout do definice Transact-SQL uživatelem definované funkce:
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- SEND

Dovolení

Vyžaduje oprávnění CREATE FUNCTION v databázi a ALTER oprávnění ke schématu, ve kterém se funkce vytváří. Pokud funkce určuje typ definovaný uživatelem, vyžaduje EXECUTE oprávnění k typu.

Příklady skalárních funkcí

Skalární funkce (skalární funkce UDF)

Následující příklad vytvoří více příkazů skalární funkce (skalární UDF) v databázi AdventureWorks2022. Funkce přebírá jednu vstupní hodnotu, ProductIDa vrací jednu datovou hodnotu, agregované množství zadaného produktu v inventáři.

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;

Následující příklad používá funkci ufnGetInventoryStock k vrácení aktuálního množství zásob pro produkty, které mají ProductModelID mezi 75 a 80.

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

Další informace a příklady skalárních funkcí najdete v tématu CREATE FUNCTION.

Příklady funkcí s hodnotami tabulky

Funkce vracející hodnotu tabulky (TVF)

Následující příklad vytvoří vloženou tabulkovou funkci (TVF) v databázi AdventureWorks2022. Funkce přijímá jeden vstupní parametr, ID zákazníka (prodejny), a vrací sloupce ProductID, Namea agregaci prodejů od začátku roku jako YTD Total pro každý produkt prodávaný do prodejny.

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
);

Následující příklad vyvolá funkci a určuje ID zákazníka 602.

SELECT * FROM Sales.ufn_SalesByStore (602);

Víceprohlášková funkce vracející tabulku (MSTVF)

Následující příklad vytvoří více-výrazovou tabulkovou funkci (MSTVF) v databázi AdventureWorks2022. Funkce přebírá jeden vstupní parametr, EmployeeID a vrátí seznam všech zaměstnanců, kteří hlásí zadanému zaměstnanci přímo nebo nepřímo. Funkce se pak vyvolá zadáním ID zaměstnance 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

Následující příklad vyvolá funkci a určuje ID zaměstnance 1.

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

Pro více informací a příklady funkcí s hodnotami tabulky typu inline (TVF) a funkcí s více příkazy (MSTVF) viz CREATE FUNCTION.

Osvědčené postupy

Pokud se pomocí klauzule SCHEMABINDING nevytvoří uživatelem definovaná funkce (UDF), můžou změny provedené v podkladových objektech ovlivnit definici funkce a vygenerovat neočekávané výsledky při jejím vyvolání. Doporučujeme implementovat jednu z následujících metod, abyste zajistili, že funkce nebude zastaralá kvůli změnám jeho podkladových objektů:

Při vytváření UDF zadejte klauzuli WITH SCHEMABINDING. Tím se zajistí, že objekty odkazované v definici funkce nelze upravit, pokud se tato funkce také nezmění.
Spusťte sp_refreshsqlmodule uloženou proceduru po úpravě libovolného objektu zadaného v definici UDF.

Pokud vytváříte UDF, který nemá přístup k datům, zadejte možnost SCHEMABINDING, aby optimalizátor dotazů nevygeneroval zbytečné operátory spouštění pro plány dotazů zahrnující tyto uživatelsky definované funkce. Další informace o cívkách naleznete v tématu Odkaz na logické a fyzické operátory. Další informace o vytvoření funkce vázané na schéma naleznete v tématu Funkce vázané na schéma.

Připojení k MSTVF v klauzuli FROM je možné, ale může vést k nízkému výkonu. SQL Server nemůže použít všechny optimalizované techniky u některých příkazů, které lze zahrnout do MSTVF, což vede k neoptimálnímu plánu dotazů. Pokud chcete dosáhnout nejlepšího možného výkonu, kdykoli je to možné, používejte spojení mezi základními tabulkami místo funkcí.

MSTVFs má pevný odhad kardinality 100 počínaje SQL Serverem 2014 (12.x) a 1 pro starší verze SQL Serveru.

Počínaje SQL Serverem 2017 (14.x), optimalizace plánu provádění, který používá MSTVFs, může využít prokládané provádění, což vede k použití skutečné kardinality místo výše uvedených heuristik.

Další informace najdete v tématu Střídavé provádění pro funkce tabulky s vícenásobnými příkazy.

ANSI_WARNINGS se neuplatní, když předáte parametry v uložené proceduře, uživatelsky definované funkci nebo když deklarujete a nastavíte proměnné v dávkovém příkazu. Pokud je například proměnná definována jako char(3)a pak je nastavená na hodnotu větší než tři znaky, data se zkrátí na definovanou velikost a příkaz INSERT nebo UPDATE bude úspěšný.

Sdílet prostřednictvím