Käyttäjän määrittämien funktioiden luominen

  • 5 minuuttia

Käyttäjän määrittämät funktiot (UDF) muistuttavat tallennettuja toimintosarjoja siinä, että ne tallennetaan erillään tietokannan taulukoista. Nämä funktiot hyväksyvät parametreja, suorittavat toiminnon ja palauttavat sitten toiminnon tuloksen yksittäisenä (skalaariarvona) tai tulosjoukkona (taulukkoarvo). Voit sitten käyttää -funktiota taulukon sijasta, kun kirjoitat SELECT-lauseketta. Käyttäjän määrittämät funktiot on tarkoitettu suorittamaan laskutoimituksia ja käyttämään tätä tulosta toisessa lausekkeessa. Tallennetut toimintosarjat voivat tiivistää funktion ja lausekkeen ja jopa muokata tietokannan tietoja.

Tarkastelemme kolmea käyttäjän määrittämää funktiotyyppiä. Lisätietoja eri funktioista on T-SQL-viitedokumentaatiossa.

Sisäiset taulukkoarvoiset funktiot

Sisäiset taulukkoarvoiset funktiot (TVF) ovat yksinkertaisin funktio, joka on luotu SELECT-lauseen perusteella, ja ne ovat suositeltava valinta suorituskykyyn.

Seuraavassa esimerkissä luodaan taulukkoarvoinen funktio, joka käyttää unitprice-syöteparametria.

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

Kun taulukkoarvoinen funktio suoritetaan käyttäen parametrin arvoa, palautetaan kaikki tuotteet, joilla on tätä arvoa suurempi yksikköhinta.

Seuraava koodi käyttää taulukkoarvoista funktiota taulukon sijasta.

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

Monilauseketaulukkoarvoiset funktiot

Toisin kuin sisäisessä TVF:ssä, monilausekkeella taulukkoarvoisessa funktiossa (MSTVF) voi olla useampi kuin yksi lauseke ja sillä on erilaiset syntaksivaatimukset.

Huomaa, miten seuraavassa koodissa käytetään BEGIN/END-funktiota RETURN-funktion lisäksi:

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;

Kun toiminto on luotu, viittaat MSTVF:ään taulukon sijasta, aivan kuten yllä olevassa aiemmassakin sisäisessä funktiossa. Voit myös viitata FROM-lauseen tulosteeseen ja liittää sen muihin taulukoihin.

SELECT *
FROM Sales.mstvf_OrderStatus();

Suorituskykyyn liittyviä huomioita

Kyselyn optimointitoiminto ei pysty arvioimaan, kuinka monta riviä palauttaa monilausekkeen taulukkoarvoiselle funktiolle, mutta se voi käyttää rivissä olevaa taulukkoarvoista funktiota. Käytä siis tekstiin tallennettua TVF:tä, kun se on mahdollista suorituskyvyn parantamiseksi. Jos sinun ei tarvitse liittää MSTVF: ää muihin taulukoihin ja / tai tiedät, että tulos on vain muutama rivi, niin suorituskykyvaikutus ei ole yhtä huolestuttava. Jos odotat suurta tulosjoukkoa ja sinun on liityttävä muihin taulukoihin, harkitse tilapäistaulukon käyttämistä tulosten tallentamiseen ja sitten yhdistämiseen tilapäistaulukkoon.

Microsoft esitteli sql Serverin versioissa 2017 ja sitä uudemmissa versioissa ominaisuuksia älykkääseen kyselyiden käsittelyyn MSTVF:n suorituskyvyn parantamiseksi. Lisätietoja älykkään kyselynkäsittelyn ominaisuuksista on T-SQL:n viitedokumentaatiossa.

Käyttäjän määrittämät skalaarifunktiot

Skalaarinen käyttäjän määrittämä funktio palauttaa vain yhden arvon toisin kuin taulukkoarvoiset funktiot, joten sitä käytetään usein yksinkertaisissa, usein käytetyissä lausekkeissa.

Tässä on esimerkki, jonka avulla voit noutaa tietyn tuotteen tuoteluettelohinnan tiettynä päivänä:

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

Tälle funktiolle on annettava molemmat parametrit, jotta arvo saadaan. Funktiosta riippuen voit lisätä funktion SELECT-lauseeseen monimutkaisemmassa kyselyssä.

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

Sido funktio viitattuihin objekteihin

SCHEMABINDING on valinnainen, kun luot funktion. Kun määrität SCHEMABINDING-asetuksen, se sitoo funktion viitattuihin objekteihin, eikä objekteja voi muokata muokkaamatta myös funktiota. Funktiota on ensin muokattava tai poistettava riippuvuuksien poistamiseksi ennen objektin muokkaamista.

SCHEMABINDING poistetaan, jos jokin seuraavista tapahtuu:

  • Funktio pudotetaan
  • Funktiota muokataan ALTER-lausekkeella määrittämättä SCHEMABINDING-funktiota