SQL Server tabellenwertige Funktionen

Zusammenfassung: In diesem Tutorial lernen Sie, wie Sie die SQL Server tabellenwertige Funktion verwenden, einschließlich der Inline tabellenwertenden Funktion und der tabellenwertenden Funktionen mit mehreren Anweisungen.

Was ist eine tabellenwertige Funktion in SQL Server

Eine tabellenwertige Funktion ist eine benutzerdefinierte Funktion, die Daten eines Tabellentyps zurückgibt. Der Rückgabetyp einer tabellenwertigen Funktion ist eine Tabelle, daher können Sie die tabellenwertige Funktion genauso verwenden, wie Sie eine Tabelle verwenden würden.

Erstellen einer tabellenwertigen Funktion

Das folgende Anweisungsbeispiel erstellt eine tabellenwertige Funktion, die eine Liste von Produkten einschließlich Produktname, Modelljahr und den Listenpreis für ein bestimmtes Modelljahr zurückgibt:

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)

Die Syntax ist ähnlich wie die, die eine benutzerdefinierte Funktion erstellt.

Das RETURNS TABLE gibt an, dass die Funktion eine Tabelle zurückgeben soll. Wie Sie sehen können, gibt es keine BEGIN...END-Anweisung. Die Anweisung fragt einfach Daten aus der production.products Tabelle ab.

Die udfProductInYear Funktion akzeptiert einen Parameter namens @model_year vom Typ INT. Es gibt die Produkte zurück, deren Modelljahre dem Parameter @model_year entsprechen.

Nachdem die tabellenwertige Funktion erstellt wurde, finden Sie sie unter Programmierbarkeit > Funktionen > tabellenwertige Funktionen wie im folgenden Bild gezeigt:

Die obige Funktion gibt die Ergebnismenge einer einzelnen SELECT-Anweisung zurück, daher wird sie auch als Inline Table-valued Function bezeichnet.

Ausführen einer tabellenwertigen Funktion

Um eine tabellenwertige Funktion auszuführen, verwenden Sie sie in der FROM-Klausel der SELECT-Anweisung:

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

In diesem Beispiel haben wir die Produkte ausgewählt, deren Modelljahr 2017 ist.

Sie können auch festlegen, welche Spalten von der tabellenwertigen Funktion wie folgt zurückgegeben werden sollen:

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

Hier ist die Teilausgabe:

Modifizieren einer table-valued function

Um eine table-valued function zu modifizieren, verwenden Sie das Schlüsselwort ALTER anstelle von CREATE. Der Rest des Skripts bleibt gleich.

Die folgende Anweisung ändert beispielsweise das udfProductInYear, indem sie den vorhandenen Parameter ändert und einen weiteren Parameter hinzufügt:

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)

Die Funktion udfProductInYear gibt nun Produkte zurück, deren Modelljahr zwischen einem Anfangsjahr und einem Endjahr liegt.

Die folgende Anweisung ruft die udfProductInYear-Funktion auf, um die Produkte zu erhalten, deren Modelljahre zwischen 2017 und 2018 liegen:

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

Hier ist die Teilausgabe:

Multi-statement table-valued functions (MSTVF)

Eine Multi-Statement Table-Valued Function oder MSTVF ist eine tabellenwertige Funktion, die das Ergebnis mehrerer Anweisungen zurückgibt.

Die tabellenbewertete Funktion mit mehreren Anweisungen ist sehr nützlich, da Sie innerhalb der Funktion mehrere Abfragen ausführen und die Ergebnisse in der zurückgegebenen Tabelle aggregieren können.

Um eine tabellenbewertete Funktion mit mehreren Anweisungen zu definieren, verwenden Sie eine Tabellenvariable als Rückgabewert. Innerhalb der Funktion führen Sie eine oder mehrere Abfragen aus und fügen Daten in diese Tabellenvariable ein.

Die folgende udfContacts()-Funktion fasst Mitarbeiter und Kunden in einer einzigen Kontaktliste zusammen:

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)

Die folgende Anweisung veranschaulicht die Ausführung einer tabellenwertigen Funktion mit mehreren Anweisungen udfContacts:

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

Ausgabe:

Wann verwendet man tabellenwertige Funktionen

Typischerweise verwenden wir tabellenwertige Funktionen als parametrisierte Views. Im Vergleich zu gespeicherten Prozeduren sind die tabellenwertigen Funktionen flexibler, da wir sie überall dort einsetzen können, wo Tabellen verwendet werden.

In diesem Lernprogramm haben Sie die tabellenwertigen Funktionen von SQL Server kennengelernt, einschließlich der tabellenwertigen Inline-Funktionen und der tabellenwertigen Funktionen mit mehreren Anweisungen.