Skapa tabellvärdesfunktioner
Med tabellvärdesfunktioner kan du kapsla in komplex frågelogik i återanvändbara komponenter som returnerar resultatuppsättningar. Du kan anropa dessa funktioner direkt i frågor, precis som tabeller eller vyer, vilket gör koden mer modulär och underhållsbar.
När du skapar databasprogram behöver du ofta hämta filtrerade eller beräknade datauppsättningar baserat på indataparametrar. Tabellvärdesfunktioner löser det här problemet genom att paketera frågelogik i funktioner som accepterar parametrar och returnerar tabeller. Till skillnad från lagrade procedurer kan du använda tabellvärdesfunktioner i JOIN satser och SELECT instruktioner, vilket ger dig flexibiliteten att behandla funktionsresultat som datakällor.
Förstå funktionstyper för tabellvärde
SQL Server innehåller två typer av tabellvärdesfunktioner som var och en passar för olika scenarier.
Infogad tabellvärdesfunktion
Innehåller en enda SELECT instruktion och returnerar resultat direkt. Med infogade funktioner definierar du inte tabellstrukturen – SQL Server härleder den från instruktionen SELECT . Frågeoptimeraren behandlar infogade tabellvärdefunktioner som vyer med parametrar, vilket ofta leder till bättre exekveringsplaner.
Tabellvärdefunktion med flera satser
Använder ett BEGIN...END block och deklarerar uttryckligen strukturen för den returnerade tabellen. Den här typen ger dig mer kontroll när du behöver köra flera instruktioner, utföra komplexa beräkningar eller skapa resultatuppsättningen iterativt. Den här flexibiliteten medför dock en prestandavägning eftersom optimeraren behandlar dessa funktioner på olika sätt.
Valet mellan dessa funktioner beror på dina specifika krav. För enkla frågor med parametrar ger infogade funktioner bättre prestanda. När du behöver procedurlogik eller flera steg för att skapa resultatuppsättningen blir funktioner med flera instruktioner nödvändiga.
Skapa infogade tabellvärdesfunktioner
Infogade tabellvärdesfunktioner ger ett koncist sätt att parametrisera frågor. Du definierar dem med en enda RETURN-instruktion följt av en SELECT-fråga.
I följande exempel visas en infogad funktion som hämtar beställningar för en specifik kund:
CREATE FUNCTION dbo.GetCustomerOrders
(
@CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
OrderID,
OrderDate,
TotalAmount,
Status
FROM Sales.Orders
WHERE CustomerID = @CustomerID
);
Nu kan du använda den här funktionen i frågor precis som en tabell:
SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';
Funktionen accepterar parametern kund-ID och returnerar endast kundens beställningar. Du kan filtrera, JOINeller aggregera resultatet efter behov. Den här metoden håller huvudfrågan ren medan du kapslar in kundens filtreringslogik.
Skapa tabellvärdesfunktioner med flera statements
Tabellvärdesfunktioner med flera instruktioner ger större flexibilitet när du behöver utföra flera åtgärder för att skapa din resultatuppsättning.
Överväg en funktion som beräknar produktförsäljningssammanfattningar med flera sammansättningar:
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;
Observera hur du uttryckligen deklarerar tabellvariabeln @SalesSummary med specifika kolumner och datatyper. Funktionstexten infogar data i den här tabellvariabeln och returnerar dem sedan. Med den här strukturen kan du lägga till ytterligare bearbetningslogik, felhantering eller villkorssatser efter behov.
Använda tabellvärdesfunktioner i frågor
Tabellvärdesfunktioner integreras sömlöst i dina frågor, vilket möjliggör kraftfulla datahämtningsmönster.
Du kan koppla funktionsresultat till andra 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;
Operatorn CROSS APPLY anropar funktionen för varje rad från tabellen Kunder, men i det här exemplet är funktionsparametrarna konstanter. När du skickar kolumnvärden som parametrar CROSS APPLY blir det särskilt användbart:
SELECT
c.CustomerName,
o.OrderID,
o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';
Den här frågan hämtar alla slutförda beställningar för varje kund, vilket visar hur tabellvärdesfunktioner möjliggör bearbetning rad för rad i dina frågor. Funktionen fungerar som en korrelerad underfråga men med bättre läsbarhet och återanvändning.
För infogade tabellvärdesfunktioner som inte kräver utvärdering rad för rad kan du också använda INNER JOIN syntax:
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 dessa tekniker kan du skapa komplexa frågor från enklare, testade funktionskomponenter, vilket förbättrar både kodunderhållbarheten och utvecklingseffektiviteten.