SQL Server Funkcje tabelaryczne

Podsumowanie: w tym poradniku dowiesz się jak używać funkcji tabelarycznych SQL Server, włączając w to funkcje tabelaryczne inline oraz funkcje wielostanowiskowe.

Co to jest funkcja tabelaryczna w SQL Server

Funkcja tabelaryczna jest funkcją zdefiniowaną przez użytkownika, która zwraca dane typu tabelarycznego. Typem zwrotnym funkcji typu tabela-wartość jest tabela, dlatego możesz używać funkcji typu tabela-wartość tak samo, jak używałbyś tabeli.

Tworzenie funkcji wartościowanej tabelarycznie

Następujący przykład instrukcji tworzy funkcję wartościowaną tabelarycznie, która zwraca listę produktów zawierającą nazwę produktu, rok modelu i cenę katalogową dla określonego roku modelu:

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)

Składnia jest podobna do tej, która tworzy funkcję zdefiniowaną przez użytkownika.

RETURNS TABLE określa, że funkcja będzie zwracać tablicę. Jak widać, nie ma tu żadnej instrukcji BEGIN...END. Instrukcja po prostu odpytuje dane z tabeli production.products.

Funkcja udfProductInYear przyjmuje jeden parametr o nazwie @model_year typu INT. Zwraca ona produkty, których lata modelowe są równe parametrowi @model_year.

Po utworzeniu funkcji tabelowo-wartościowej, można ją znaleźć w zakładce Programmability > Functions > Table-valued Functions, jak na poniższym rysunku:

Powyższa funkcja zwraca zbiór wyników pojedynczej SELECT instrukcji, dlatego znana jest również jako inline table-valued function.

Wykonanie funkcji wartościowanej tabelarycznie

Aby wykonać funkcję wartościowaną tabelarycznie, używamy jej w klauzuli FROM instrukcji SELECT:

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

W tym przykładzie wybraliśmy produkty, których rok modelowy to 2017.

Można również określić, które kolumny mają zostać zwrócone z funkcji wartości tabelarycznej w następujący sposób:

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

Tutaj znajduje się częściowe wyjście:

Modyfikowanie funkcji tabelowo-wartościowej

Aby zmodyfikować funkcję tabelowo-wartościową, używamy słowa kluczowego ALTER zamiast CREATE. Pozostała część skryptu jest taka sama.

Na przykład poniższa instrukcja modyfikuje funkcję udfProductInYear przez zmianę istniejącego parametru i dodanie jeszcze jednego parametru:

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)

Funkcja udfProductInYear zwraca teraz produkty, których rok modelowy mieści się między rokiem początkowym a rokiem końcowym.

Poniższa instrukcja wywołuje funkcję udfProductInYear, aby uzyskać produkty, których lata modelowe znajdują się pomiędzy 2017 a 2018:

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

Tutaj znajduje się częściowe wyjście:

Multi-statement table-wartości tabelaryczne (MSTVF)

Wielostanowiskowa funkcja tabelaryczna lub MSTVF jest funkcją tabelaryczną, która zwraca wynik wielu poleceń.

Wielostanowiskowa funkcja tabelowo-wartościowa jest bardzo użyteczna, ponieważ można wykonać wiele zapytań w ramach funkcji i agregować wyniki w zwracanej tabeli.

Aby zdefiniować wielostanowiskową funkcję tabelowo-wartościową, używamy zmiennej tabelarycznej jako wartości zwracanej. Wewnątrz funkcji wykonujemy jedno lub więcej zapytań i wstawiamy dane do tej zmiennej tabeli.

Następująca funkcja udfContacts() łączy pracowników i klientów w pojedynczą listę kontaktów:

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)

Następujące wyrażenie ilustruje sposób wykonania wielostanowiskowej funkcji tabelaryczno-wartościowej udfContacts:

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

Wyjście:

Kiedy używać funkcji table-valued

Funkcji table-valued używamy zazwyczaj jako sparametryzowanych widoków. W porównaniu z procedurami przechowywanymi, funkcje tabelaryczne są bardziej elastyczne, ponieważ możemy ich używać wszędzie tam, gdzie używane są tabele.

W tym poradniku dowiedziałeś się o funkcjach tabelarycznych SQL Server, w tym o funkcjach tabelarycznych inline i funkcjach tabelarycznych wielostanowiskowych.

W tym poradniku dowiedziałeś się o funkcjach tabelarycznych SQL Server, w tym o funkcjach tabelarycznych inline i funkcjach tabelarycznych wielostanowiskowych.