SQL Server tabel-gewaardeerde functies

Samenvatting: in deze tutorial leert u hoe u SQL Server tabel-gewaardeerde functie kunt gebruiken, inclusief inline tabel-gewaardeerde functie en multi-statement gewaardeerde functies.

Wat is een tabel-gewaardeerde functie in SQL Server

Een tabel-gewaardeerde functie is een door de gebruiker gedefinieerde functie die gegevens van een tabel type retourneert. Het return type van een tabel-gewaardeerde functie is een tabel, daarom kunt u de tabel-gewaardeerde functie gebruiken net zoals u een tabel zou gebruiken.

Een tabel-gewaardeerde functie maken

Het volgende voorbeeld van een verklaring maakt een tabel-gewaardeerde functie die een lijst met producten retourneert, inclusief productnaam, modeljaar en de catalogusprijs voor een specifiek modeljaar:

CREATE FUNCTION udfProductInYear ( @model_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year;
Code language: SQL (Structured Query Language) (sql)

De syntaxis is vergelijkbaar met die waarmee een door de gebruiker gedefinieerde functie wordt gemaakt.

De RETURNS TABLE specificeert dat de functie een tabel zal retourneren. Zoals u kunt zien, is er geen BEGIN...END statement. Het statement vraagt eenvoudig gegevens op uit de production.products tabel.

De udfProductInYear functie accepteert één parameter met de naam @model_year van het type INT. Het geeft de producten terug waarvan het modeljaar gelijk is aan de @model_year parameter.

Als de tabelgewaardeerde functie eenmaal is gemaakt, kunt u deze vinden onder Programmeerbaarheid > Functies > Tabelgewaardeerde functies, zoals in de volgende afbeelding wordt getoond:

De bovenstaande functie retourneert de resultatenset van een enkel SELECT statement, daarom staat deze ook bekend als een inline table-valued functie.

Uitvoeren van een tabelgewaardeerde functie

Om een tabelgewaardeerde functie uit te voeren, gebruikt u deze in de FROM-clausule van het SELECT-instructie:

SELECT * FROM udfProductInYear(2017);
Code language: SQL (Structured Query Language) (sql)

In dit voorbeeld hebben we de producten geselecteerd waarvan het modeljaar 2017 is.

U kunt ook als volgt opgeven welke kolommen uit de tabelfunctie moeten worden geretourneerd:

SELECT product_name, list_priceFROM udfProductInYear(2018);
Code language: SQL (Structured Query Language) (sql)

Hier ziet u de gedeeltelijke uitvoer:

Wijzigen van een tabel-gewaardeerde functie

Om een tabel-gewaardeerde functie te wijzigen, gebruikt u het sleutelwoord ALTER in plaats van CREATE. De rest van het script is hetzelfde.

Het volgende statement wijzigt bijvoorbeeld de udfProductInYear door de bestaande parameter te wijzigen en een extra parameter toe te voegen:

ALTER FUNCTION udfProductInYear ( @start_year INT, @end_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year BETWEEN @start_year AND @end_year
Code language: SQL (Structured Query Language) (sql)

De udfProductInYear functie retourneert nu producten waarvan het modeljaar tussen een beginjaar en een eindjaar ligt.

Het volgende statement roept de udfProductInYear functie aan om de producten te krijgen waarvan het modeljaar tussen 2017 en 2018 ligt:

SELECT product_name, model_year, list_priceFROM udfProductInYear(2017,2018)ORDER BY product_name;
Code language: SQL (Structured Query Language) (sql)

Hier is de gedeeltelijke uitvoer:

Multi-statement tabel-gewaardeerde functies (MSTVF)

Een tabelgewaardeerde functie met meerdere opgaven of MSTVF is een tabelgewaardeerde functie die het resultaat van meerdere opgaven retourneert.

De multi-statement-tabel-gewaardeerde functie is zeer nuttig omdat u meerdere query’s binnen de functie kunt uitvoeren en de resultaten in de geretourneerde tabel kunt aggregeren.

Om een multi-statement-tabel-gewaardeerde functie te definiëren, gebruikt u een tabelvariabele als de retourwaarde. Binnen de functie voert u een of meer query’s uit en voegt u gegevens in deze tabelvariabele in.

De volgende udfContacts() functie combineert medewerkers en klanten in één lijst met contactpersonen:

CREATE FUNCTION udfContacts() RETURNS @contacts TABLE ( first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(255), phone VARCHAR(25), contact_type VARCHAR(20) )ASBEGIN INSERT INTO @contacts SELECT first_name, last_name, email, phone, 'Staff' FROM sales.staffs; INSERT INTO @contacts SELECT first_name, last_name, email, phone, 'Customer' FROM sales.customers; RETURN;END;
Code language: SQL (Structured Query Language) (sql)

Het volgende statement illustreert hoe een multi-statement table-valued functie udfContacts moet worden uitgevoerd:

SELECT * FROM udfContacts();
Code language: SQL (Structured Query Language) (sql)

Uitvoer:

Wanneer gebruiken we tabelgewaardeerde functies

We gebruiken tabelgewaardeerde functies gewoonlijk als geparametriseerde weergaven. In vergelijking met opgeslagen procedures zijn tabelgewaardeerde functies flexibeler omdat we ze overal kunnen gebruiken waar tabellen worden gebruikt.

In deze zelfstudiegids hebt u meer geleerd over SQL Server-tabelgewaardeerde functies, waaronder inline tabelgewaardeerde functies en tabelgewaardeerde functies met meerdere instructies.