Creează funcții cu valori de tabel

Funcțiile cu valori de tabel permit încapsularea logicii complexe de interogare în componente reutilizabile care returnează seturi de rezultate. Poți chema aceste funcții direct în interogări, la fel ca tabele sau vizualizările, făcând codul tău mai modular și mai ușor de întreținut.

Când construiești aplicații de baze de date, de multe ori trebuie să recuperezi seturi de date filtrate sau calculate pe baza parametrilor de intrare. Funcțiile cu valori de tabel rezolvă această problemă prin ambalarea logicii de interogare în funcții care acceptă parametri și tabele de returnare. Spre deosebire de procedurile stocate, poți folosi funcții cu valori de tabel în JOIN clauze și SELECT instrucțiuni, oferindu-ți flexibilitatea de a trata rezultatele funcțiilor ca surse de date.

Înțelegerea tipurilor de funcții cu valori de tabel

SQL Server oferă două tipuri de funcții cu valori de tabel, fiecare potrivită pentru scenarii diferite.

Funcție cu valori de tabel în linie

Conține o singură SELECT instrucțiune și returnează rezultatele direct. Cu funcțiile inline, nu definești structura tabelului—SQL Server o deduce din afirmația ta SELECT . Optimizatorul de interogări tratează funcțiile cu valori de tabel inline ca pe niște vizualizări cu parametri, adesea producând planuri de execuție mai bune.

Funcție cu valori de tabel cu mai multe instrucțiuni

Folosește un BEGIN...END bloc și declară explicit structura tabelului returnat. Acest tip îți oferă mai mult control atunci când trebuie să execuți mai multe instrucțiuni, să faci calcule complexe sau să construiești setul de rezultate iteriv. Totuși, această flexibilitate vine cu un compromis de performanță, deoarece optimizatorul tratează aceste funcții diferit.

Alegerea între aceste funcții depinde de cerințele tale specifice. Pentru interogări simple cu parametri, funcțiile inline oferă performanțe mai bune. Când ai nevoie de logică procedurală sau de mai mulți pași pentru a-ți construi setul de rezultate, devin necesare funcțiile cu mai multe instrucțiuni.

Creează funcții cu valori de tabel inline

Funcțiile cu valori de tabel în linie oferă o modalitate concisă de a parametriza interogările. Le definești cu o singură instrucțiune RETURN urmată de o interogare SELECT.

Următorul exemplu arată o funcție inline care preia comenzile pentru un client specific:

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

Acum poți folosi această funcție în interogări la fel ca un tabel:

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

Funcția acceptă parametrul ID clientului și returnează doar comenzile acelui client. Poți filtra suplimentar, JOIN, sau agrega rezultatele după necesitate. Această abordare menține interogarea principală curată, încapsulând logica de filtrare a clientului.

Creează funcții cu valori de tabel cu mai multe instrucțiuni

Funcțiile cu valori de tabel cu mai multe instrucțiuni oferă mai multă flexibilitate atunci când trebuie să efectuezi mai multe operații pentru a-ți construi setul de rezultate.

Luați în considerare o funcție care calculează rezumate de vânzări de produse cu multiple agregații:

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;

Observă cum declari explicit variabila @SalesSummary tabelului cu coloane și tipuri de date specifice. Corpul funcției inserează date în această variabilă de tabel, apoi le returnează. Această structură îți permite să adaugi logică suplimentară de procesare, gestionarea erorilor sau instrucțiuni condiționate după necesitate.

Folosiți funcțiile cu valori de tabel în interogări

Funcțiile cu valori de tabel se integrează perfect în interogările tale, permițând tipare puternice de recuperare a datelor.

Poți asocia rezultatele funcțiilor cu alte tabele:

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;

Operatorul CROSS APPLY apelează funcția pentru fiecare rând din tabelul Clienți, deși în acest exemplu, parametrii funcției sunt constante. Când treci valori de coloană ca parametri, CROSS APPLY devine deosebit de util:

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

Această interogare recuperează toate comenzile finalizate pentru fiecare client, demonstrând cum funcțiile cu valori de tabel permit procesarea rând cu rând în interogările tale. Funcția acționează ca o subquery corelată, dar cu o lizibilitate și reutilizare mai bune.

Pentru funcțiile cu valori de tabel în linie care nu necesită evaluare rând cu rând, poți folosi INNER JOIN și sintaxa:

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;

Cu aceste tehnici, poți construi interogări complexe din componente funcționale mai simple, testate, îmbunătățind atât mentenanța codului, cât și eficiența dezvoltării.