SQL Server funzioni valutate in tabella

Sommario: in questo tutorial, imparerete come usare SQL Server funzione valutata in tabella, inclusa la funzione valutata in linea in tabella e le funzioni valutate multi-statement.

Cos’è una funzione valutata in tabella in SQL Server

Una funzione valutata in tabella è una funzione definita dall’utente che ritorna dati di tipo tabella. Il tipo di ritorno di una funzione table-valued è una tabella, quindi potete usare la funzione table-valued proprio come usereste una tabella.

Creazione di una funzione table-valued

L’esempio di dichiarazione seguente crea una funzione table-valued che restituisce una lista di prodotti che include il nome del prodotto, l’anno del modello e il prezzo di listino per uno specifico anno del modello:

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)

La sintassi è simile a quella che crea una funzione definita dall’utente.

Il RETURNS TABLE specifica che la funzione restituirà una tabella. Come potete vedere, non c’è nessuna dichiarazione BEGIN...END. La dichiarazione interroga semplicemente i dati dalla tabella production.products.

La funzione udfProductInYear accetta un parametro chiamato @model_year di tipo INT. Restituisce i prodotti il cui anno di modello è uguale al parametro @model_year.

Una volta creata la funzione a valore tabellare, la si può trovare sotto Programmabilità > Funzioni > Funzioni a valore tabellare come mostrato nell’immagine seguente:

La funzione di cui sopra restituisce l’insieme dei risultati di una singola SELECT dichiarazione, quindi, è anche conosciuta come una funzione valorizzata da tabella in linea.

Esecuzione di una funzione con valore in tabella

Per eseguire una funzione con valore in tabella, la si usa nella clausola FROM dell’istruzione SELECT:

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

In questo esempio, abbiamo selezionato i prodotti il cui anno di modello è 2017.

Potete anche specificare quali colonne devono essere restituite dalla funzione tabella-valori come segue:

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

Ecco l’output parziale:

Modificare una funzione valorizzata da tabella

Per modificare una funzione valorizzata da tabella, si usa il ALTER invece della parola chiave CREATE. Il resto dello script è lo stesso.

Per esempio, l’istruzione seguente modifica la udfProductInYear cambiando il parametro esistente e aggiungendo un altro parametro:

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)

La funzione udfProductInYear ora restituisce i prodotti il cui anno modello è compreso tra un anno iniziale e un anno finale.

La seguente dichiarazione chiama la funzione udfProductInYear per ottenere i prodotti il cui anno di modello è compreso tra 2017 e 2018:

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

Ecco l’output parziale:

Multi-statement table-valued functions (MSTVF)

Una multi-statement table-valued function o MSTVF è una funzione table-valued che restituisce il risultato di più dichiarazioni.

La funzione multi-statement-table-valued è molto utile perché è possibile eseguire più query all’interno della funzione e aggregare i risultati nella tabella restituita.

Per definire una funzione multi-statement table-valued, si usa una variabile di tabella come valore di ritorno. All’interno della funzione, si eseguono una o più query e si inseriscono dati in questa variabile di tabella.

La seguente funzione udfContacts() combina personale e clienti in una singola lista di contatti:

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)

La seguente dichiarazione illustra come eseguire una funzione multi-statement table-valued udfContacts:

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

Output:

Quando usare le funzioni valutate in tabella

In genere si usano le funzioni valutate in tabella come viste parametrizzate. Rispetto alle stored procedure, le funzioni valutate in tabella sono più flessibili perché possiamo usarle ovunque si usino le tabelle.

In questo tutorial, avete imparato a conoscere le funzioni valutate in tabella di SQL Server, comprese le funzioni valutate in linea e le funzioni valutate in tabella multi-statement.