Opprette brukerdefinerte funksjoner

Fullført

Brukerdefinerte funksjoner (UDF) ligner på lagrede prosedyrer ved at de lagres separat fra tabeller i databasen. Disse funksjonene godtar parametere, utfører en handling og returnerer deretter handlingsresultatet som én enkelt (skalar) verdi eller et resultatsett (tabellverdi). Deretter kan du bruke funksjonen i stedet for en tabell når du skriver en SELECT-setning. Brukerdefinerte funksjoner er ment å utføre beregninger og bruke dette resultatet i en annen setning. Mens lagrede prosedyrer kan innkapsle funksjonen og setningen, og til og med endre data i databasen.

Vi går gjennom tre typer brukerdefinerte funksjoner. Hvis du vil ha mer informasjon om de ulike funksjonene, kan du se referansedokumentasjonen for T-SQL.

Innebygde tabellverdifunksjoner

Innebygde tabellverdifunksjoner (TVF) er den enkleste funksjonen som opprettes basert på en SELECT-setning, og de er det foretrukne valget for ytelse.

I eksemplet nedenfor opprettes en tabellverdifunksjon med en inndataparameter for enhetspris.

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost; 

Når funksjonen tabellverdi kjøres med en verdi for parameteren, returneres alle produkter med en enhetspris mer enn denne verdien.

Følgende kode bruker funksjonen tabellverdi i stedet for en tabell.

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

Tabellverdifunksjoner med flere setninger

I motsetning til den innebygde TVF-en kan en tabellverdifunksjon med flere setninger (MSTVF) ha mer enn én setning og ha andre syntakskrav.

Legg merke til hvordan i følgende kode bruker vi en BEGIN/END i tillegg til RETURN:

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

Når du er opprettet, refererer du til MSTVF i stedet for en tabell, akkurat som med den forrige innebygde funksjonen ovenfor. Du kan også referere til utdataene i FROM-setningsdelen og koble det sammen med andre tabeller.

SELECT *
FROM Sales.mstvf_OrderStatus();

Ytelseshensyn

Spørringsoptimalisering kan ikke beregne hvor mange rader som skal returneres for en tabellverdifunksjon med flere setninger, men kan med den innebygde tabellverdifunksjonen. Bruk derfor innebygd TVF når det er mulig for bedre ytelse. Hvis du ikke trenger å bli med i MSTVF med andre tabeller og/eller du vet at resultatet bare blir noen få rader, er ikke ytelseseffekten like bekymringsfull. Hvis du forventer et stort resultatsett og trenger å bli med i andre tabeller, bør du i stedet vurdere å bruke en midlertidig tabell til å lagre resultatene og deretter bli med i temp-tabellen.

I SQL Server-versjoner 2017 og nyere introduserte Microsoft funksjoner for intelligent spørringsbehandling for å forbedre ytelsen for MSTVF. Se mer informasjon om intelligente spørringsbehandlingsfunksjoner i referansedokumentasjonen for T-SQL.

Skalarbrukerdefinerte funksjoner

En skalarbrukerdefinert funksjon returnerer bare én verdi i motsetning til tabellverdifunksjoner, og brukes derfor ofte til enkle, hyppige setninger.

Her er et eksempel for å få produktlisteprisen for et bestemt produkt på en bestemt dag:

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

For denne funksjonen må begge parameterne angis for å få verdien. Avhengig av funksjonen kan du vise funksjonen i SELECT-setningen i en mer kompleks spørring.

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

Bind funksjon til refererte objekter

SKJEMABINDING er valgfritt når du oppretter funksjonen. Når du angir SKJEMABINDING, binder den funksjonen til de refererte objektene, og objekter kan ikke endres uten også å endre funksjonen. Funksjonen må først endres eller slippes for å fjerne avhengigheter før du endrer objektet.

SKJEMABINDING fjernes hvis noe av følgende skjer:

• Funksjonen slippes
• Funksjonen endres med ALTER-setning uten å angi SCHEMABINDING