Vytvoření funkcí s hodnotami tabulky

Funkce hodnotné tabulkou umožňují zapouzdřovat složitou logiku dotazu do opakovaně použitelných komponent, které vracejí sady výsledků. Tyto funkce můžete volat přímo v dotazech, stejně jako tabulky nebo zobrazení, takže kód bude modulární a udržovatelný.

Při vytváření databázových aplikací často potřebujete načíst filtrované nebo počítané datové sady na základě vstupních parametrů. Funkce s hodnotou tabulky tento problém řeší zabalením logiky dotazu do funkcí, které přijímají parametry a vracejí tabulky. Na rozdíl oduložených procedur můžete v klauzulích a JOIN příkazech používat funkce SELECT s hodnotami tabulek, které umožňují pracovat s výsledky funkcí jako se zdroji dat.

Porozumění typům funkcí vracejících hodnoty tabulky

SQL Server poskytuje dva typy tabulkových funkcí, které jsou vhodné pro různé scénáře.

Vložená funkce s hodnotou tabulky

Obsahuje jeden SELECT příkaz a vrací výsledky přímo. U vložených funkcí nedefinujete strukturu tabulky – SQL Server ji odvodí z příkazu SELECT . Optimalizátor dotazů často vytváří lepší plány provádění, když zpracovává tabulkové funkce definované přímo podobně jako zobrazení s parametry.

Funkce s hodnotami tabulky s více příkazy

BEGIN...END Používá blok a explicitně deklaruje strukturu vrácené tabulky. Tento typ poskytuje větší kontrolu nad tím, kdy potřebujete provést více příkazů, provádět složité výpočty nebo sestavit sadu výsledků iterativním způsobem. Tato flexibilita ale přináší kompromis mezi výkonem, protože optimalizátor pracuje s těmito funkcemi odlišně.

Volba mezi těmito funkcemi závisí na vašich konkrétních požadavcích. U jednoduchých dotazů s parametry poskytují vložené funkce lepší výkon. Pokud potřebujete procedurální logiku nebo několik kroků k sestavení sady výsledků, stanou se nezbytné funkce s více příkazy.

Vytváření vložených funkcí s hodnotami tabulky

Tabulkově hodnotné funkce typu inline nabízejí stručný způsob, jak parametrizovat dotazy. Definujete je pomocí jednoho příkazu RETURN následovaného dotazem SELECT.

Následující příklad ukazuje vloženou funkci, která načítá objednávky pro konkrétního zákazníka:

CREATE FUNCTION dbo.GetCustomerOrders
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

Tuto funkci teď můžete použít v dotazech stejně jako v tabulce:

SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';

Funkce přijme parametr ID zákazníka a vrátí pouze objednávky zákazníka. Podle potřeby můžete výsledky dále filtrovat JOINnebo agregovat. Tento přístup udržuje hlavní dotaz přehledný při zahrnutí logiky filtrování zákazníků.

Vytváření funkcí s hodnotami tabulky s více příkazy

Tabulkové funkce vracející více příkazů poskytují větší flexibilitu, pokud potřebujete provést více operací ke sestavení sady výsledků.

Představte si funkci, která vypočítá souhrny prodeje produktů s více agregacemi:

CREATE FUNCTION dbo.GetProductSalesSummary
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @SalesSummary TABLE
(
    ProductID INT,
    ProductName NVARCHAR(100),
    TotalQuantity INT,
    TotalRevenue DECIMAL(18,2),
    AveragePrice DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @SalesSummary
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(od.Quantity) AS TotalQuantity,
        SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
        AVG(od.UnitPrice) AS AveragePrice
    FROM Production.Products p
    INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
    INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductID, p.ProductName;
    
    RETURN;
END;

Všimněte si, jak explicitně deklarujete proměnnou @SalesSummary tabulky s konkrétními sloupci a datovými typy. Tělo funkce vloží data do této proměnné tabulky a vrátí je. Tato struktura umožňuje podle potřeby přidat další logiku zpracování, zpracování chyb nebo podmíněné příkazy.

Použití tabulkových funkcí v dotazech

Funkce založené na tabulkách se bezproblémově integrují do dotazů a umožňují výkonné vzorce načítání dat.

Výsledky funkce můžete spojit s jinými tabulkami:

SELECT 
    c.CustomerName,
    s.ProductName,
    s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;

Operátor CROSS APPLY volá funkci pro každý řádek z tabulky Customers, i když v tomto příkladu jsou parametry funkce konstanty. Když předáte hodnoty sloupců jako parametry, CROSS APPLY se stane zejména užitečným:

SELECT 
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';

Tento dotaz načte všechny dokončené objednávky pro každého zákazníka a předvádí, jak funkce s hodnotou tabulky umožňují zpracování řádků po řádcích v rámci vašich dotazů. Funkce funguje jako korelovaný poddotaz, ale s lepší čitelností a opětovnou použitelností.

Pro vložené funkce s hodnotami tabulky, které nevyžadují vyhodnocení řádku po řádci, můžete použít syntaxi INNER JOIN také.

SELECT 
    c.CustomerName,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;

Pomocí těchto technik můžete vytvářet složité dotazy z jednodušších, testovaných součástí funkcí, což zlepšuje škálovatelnost kódu i efektivitu vývoje.