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

platí pro:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)databáze SQL v Microsoft Fabric

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

Omezení

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, @ERRORnebo 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.

SET příkazy nejsou povoleny v uživatelem definované funkci (například SET NOCOUNT ON;). Přiřazení proměnné hodnoty může použít SET.

Klauzule FOR XML není povolená.

Vnořené uživatelem definované funkce

Funkce definované uživatelem 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í.

Příkazy Service Broker

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

Funkce s vedlejšími účinky

Následující nedeterministické předdefinované funkce nelze použít v Transact-SQL uživatelem definované funkci (UDF).

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Pokud odkazujete na některou z těchto funkcí v UDF, zobrazí se následující chyba:

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

Seznam deterministických a nedeterministických předdefinovaných systémových funkcí naleznete v tématu Deterministické a nedeterministické funkce.

Chcete-li tento problém vyřešit, můžete funkci způsobující vedlejší efekty zabalit do zobrazení a volat toto zobrazení z funkce.

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 vytváří vícepříkazovou skalární funkci (skalární UDF) v databázi AdventureWorks2025. 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 AS INT;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory AS 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 vytváří inline tabulkovou funkci (TVF) v databázi AdventureWorks2025. 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
          INNER JOIN Sales.SalesOrderDetail AS SD
              ON SD.ProductID = P.ProductID
          INNER JOIN Sales.SalesOrderHeader AS SH
              ON SH.SalesOrderID = SD.SalesOrderID
          INNER 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 vytváří vícepříkazovou tabulkovou funkci (MSTVF) v databázi AdventureWorks2025. 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 INT)
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 (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
               e.OrganizationNode,
               p.FirstName,
               p.LastName,
               e.JobTitle,
               0
        FROM HumanResources.Employee AS e
             INNER JOIN Person.Person AS 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 AS e
             INNER JOIN EMP_cte
                 ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
             INNER JOIN Person.Person AS 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 dočasných souborech naleznete v části Logické a fyzické operátory v showplanu – přehled. 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 mají pevný odhad kardinality 100 počínaje SQL Serverem 2014 (12.x) a 1 pro starší verze SQL Serveru.

V SQL Serveru 2017 (14.x) a novějších verzích může optimalizace plánu provádění, který používá MSTVFs, využít prokládané provádění, což vede k použití skutečné kardinality místo dříve zmíněný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ý.

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-11-18