Opret tabelværdifunktioner

Tabelværdifunktioner lader dig indkapsle kompleks forespørgselslogik i genanvendelige komponenter, der returnerer resultatsæt. Du kan kalde disse funktioner direkte i forespørgsler, ligesom tabeller eller visninger, hvilket gør din kode mere modulær og vedligeholdelsesvenlig.

Når du bygger databaseapplikationer, skal du ofte hente filtrerede eller beregnede datasæt baseret på inputparametre. Tabelværdifunktioner løser dette problem ved at pakke forespørgselslogik ind i funktioner, der accepterer parametre og returnerer tabeller. I modsætning til lagrede procedurer kan du bruge tabelværdiede funktioner i JOIN klausuler og SELECT sætninger, hvilket giver dig fleksibilitet til at behandle funktionsresultater som datakilder.

Forstå tabelværdiede funktionstyper

SQL Server tilbyder to typer tabelværdifunktioner, som hver især er egnet til forskellige scenarier.

Inline tabel-værdifunktion

Indeholder en enkelt SELECT sætning og returnerer resultater direkte. Med inline-funktioner definerer du ikke tabelstrukturen – SQL Server udleder den ud fra din SELECT sætning. Forespørgselsoptimeringen behandler inline tabel-værdifunktioner som visninger med parametre og producerer ofte bedre eksekveringsplaner.

Multi-statement tabel-værdifunktion

Bruger en BEGIN...END blok og erklærer eksplicit strukturen af den returnerede tabel. Denne type giver dig mere kontrol, når du skal udføre flere sætninger, udføre komplekse beregninger eller bygge resultatsættet iterativt. Denne fleksibilitet medfører dog en ydelsesmæssig afvejning, da optimereren behandler disse funktioner forskelligt.

Valget mellem disse funktioner afhænger af dine specifikke behov. For simple forespørgsler med parametre giver inline-funktioner bedre ydeevne. Når du har brug for procedurelogik eller flere trin for at opbygge dit resultatsæt, bliver multi-statement funktioner nødvendige.

Opret inline tabel-værdifunktioner

Inline tabelværdifunktioner tilbyder en kortfattet måde at parametrisere forespørgsler på. Du definerer dem med en enkelt RETURN-sætning efterfulgt af en SELECT-forespørgsel.

Følgende eksempel viser en inline-funktion, der henter ordrer for en specifik kunde:

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

Du kan nu bruge denne funktion i forespørgsler ligesom i en tabel:

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

Funktionen accepterer kunde-ID-parameteren og returnerer kun kundens ordrer. Du kan yderligere filtrere, JOIN, eller aggregere resultaterne efter behov. Denne tilgang holder din hovedforespørgsel ren, samtidig med at den indkapsler kundefiltreringslogikken.

Opret tabel-værdifunktioner med flere sætninger

Multi-statement tabel-værdifunktioner giver mere fleksibilitet, når du skal udføre flere operationer for at opbygge dit resultatsæt.

Overvej en funktion, der beregner produktsalgsresuméer 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;

Bemærk, hvordan du eksplicit erklærer tabelvariablen @SalesSummary med specifikke kolonner og datatyper. Funktionskroppen indsætter data i denne tabelvariabel og returnerer dem derefter. Denne struktur giver dig mulighed for at tilføje yderligere behandlingslogik, fejlhåndtering eller betingede sætninger efter behov.

Brug tabelværdifunktioner i forespørgsler

Tabelværdiede funktioner integreres problemfrit i dine forespørgsler, hvilket muliggør kraftfulde mønstre for datahentning.

Du kan sammenflette funktionsresultater 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 kalder funktionen for hver række fra Customers-tabellen, selvom funktionsparametrene i dette eksempel er konstanter. Når du sender kolonneværdier som parametre, CROSS APPLY bliver det særligt nyttigt:

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

Denne forespørgsel henter alle færdiggjorte ordrer for hver kunde og demonstrerer, hvordan tabel-værdifunktioner muliggør række-for-række-behandling inden for dine forespørgsler. Funktionen fungerer som en korreleret delforespørgsel, men med bedre læsbarhed og genanvendelighed.

For inline tabel-værdide funktioner, der ikke kræver række-for-række-evaluering, kan du også bruge 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 teknikker kan du bygge komplekse forespørgsler ud fra enklere, testede funktionskomponenter, hvilket forbedrer både kodevedligeholdelse og udviklingseffektivitet.