Opprett tabellverdifunksjoner

Fullført

Tabellverdifunksjoner lar deg kapsle inn kompleks spørringslogikk i gjenbrukbare komponenter som returnerer resultatsett. Du kan kalle disse funksjonene direkte i spørringer, akkurat som tabeller eller visninger, noe som gjør koden din mer modulær og vedlikeholdbar.

Når du bygger databaseapplikasjoner, må du ofte hente filtrerte eller beregnede datasett basert på inputparametere. Tabellverdifunksjoner løser dette problemet ved å pakke spørringslogikk inn i funksjoner som aksepterer parametere og returnerer tabeller. I motsetning til lagrede prosedyrer kan du bruke tabellverdifunksjoner i JOIN klausuler og SELECT setninger, noe som gir deg fleksibilitet til å behandle funksjonsresultater som datakilder.

Forstå tabellverdide funksjonstyper

SQL Server tilbyr to typer tabellverdifunksjoner, hver egnet for ulike scenarier.

Inline tabellverdifunksjon

Inneholder en enkelt SELECT setning og returnerer resultater direkte. Med inline-funksjoner definerer du ikke tabellstrukturen – SQL Server utleder den fra setningen din SELECT . Spørringsoptimalisatoren behandler innebygde tabellverdifunksjoner som visninger med parametere, og produserer ofte bedre utførelsesplaner.

Flersetnings tabellverdifunksjon

Bruker en BEGIN...END blokk og erklærer eksplisitt strukturen til den returnerte tabellen. Denne typen gir deg mer kontroll når du trenger å utføre flere setninger, utføre komplekse beregninger eller bygge resultatsettet iterativt. Denne fleksibiliteten har imidlertid et ytelseskompromiss, ettersom optimalisatoren behandler disse funksjonene forskjellig.

Valget mellom disse funksjonene avhenger av dine spesifikke behov. For enkle spørringer med parametere gir inline-funksjoner bedre ytelse. Når du trenger prosedyrelogikk eller flere steg for å bygge resultatsettet ditt, blir flersetningsfunksjoner nødvendige.

Lag inline-tabellverdifunksjoner

Innebygde tabellverdifunksjoner tilbyr en konsis måte å parameterisere spørringer på. Du definerer dem med én enkelt RETURN-setning etterfulgt av en SELECT-spørring.

Følgende eksempel viser en inline-funksjon som henter ordre for en spesifikk kunde:

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

Du kan nå bruke denne funksjonen i spørringer akkurat som en tabell:

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

Funksjonen aksepterer kunde-ID-parameteren og returnerer kun kundens bestillinger. Du kan videre filtrere, JOIN, eller aggregere resultatene etter behov. Denne tilnærmingen holder hovedspørringen ren samtidig som den kapsler inn kundefiltreringslogikken.

Lag flersetnings tabellverdifunksjoner

Tabellverdifunksjoner med flere setninger gir mer fleksibilitet når du må utføre flere operasjoner for å bygge resultatsettet ditt.

Tenk på en funksjon som beregner oppsummeringer av produktsalg med flere aggregeringer:

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;

Legg merke til hvordan du eksplisitt deklarerer tabellvariabelen @SalesSummary med spesifikke kolonner og datatyper. Funksjonskroppen setter inn data i denne tabellvariabelen, og returnerer den deretter. Denne strukturen lar deg legge til ekstra prosesseringslogikk, feilhåndtering eller betingede setninger etter behov.

Bruk tabellverdifunksjoner i spørringer

Tabellbaserte funksjoner integreres sømløst i spørringene dine, noe som muliggjør kraftige mønstre for datahenting.

Du kan slå sammen funksjonsresultater med andre tabeller:

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;

Operatoren CROSS APPLY kaller funksjonen for hver rad fra Customers-tabellen, selv om funksjonsparametrene i dette eksempelet er konstanter. Når du sender kolonneverdier som parametere, CROSS APPLY blir det spesielt nyttig:

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

Denne spørringen henter alle fullførte ordre for hver kunde, og demonstrerer hvordan tabellverdifunksjoner muliggjør rad-for-rad-behandling i spørringene dine. Funksjonen fungerer som en korrelert delspørring, men med bedre lesbarhet og gjenbrukbarhet.

For inline-tabellverdifunksjoner som ikke krever rad-for-rad-evaluering, kan du også bruke INNER JOIN syntaks:

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;

Med disse teknikkene kan du bygge komplekse spørringer fra enklere, testede funksjonskomponenter, noe som forbedrer både kodevedlikeholdbarhet og utviklingseffektivitet.