Fonctions table-valued de SQL Server

Résumé : dans ce tutoriel, vous apprendrez à utiliser la fonction table-valued de SQL Server, y compris la fonction table-valued inline et les fonctions valuées multi-statement.

Qu’est-ce qu’une fonction table-valued dans SQL Server

Une fonction table-valued est une fonction définie par l’utilisateur qui renvoie des données d’un type table. Le type de retour d’une fonction à valeur de table est une table, par conséquent, vous pouvez utiliser la fonction à valeur de table comme vous le feriez avec une table.

Création d’une fonction à valeur de tableau

L’exemple d’instruction suivant crée une fonction à valeur de tableau qui renvoie une liste de produits comprenant le nom du produit, l’année du modèle et le prix de liste pour une année de modèle spécifique:

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 syntaxe est similaire à celle qui crée une fonction définie par l’utilisateur.

La RETURNS TABLE précise que la fonction retournera un tableau. Comme vous pouvez le voir, il n’y a pas de déclaration BEGIN...END. L’instruction interroge simplement les données du tableau production.products.

La fonction udfProductInYear accepte un paramètre nommé @model_year de type INT. Il renvoie les produits dont l’année du modèle est égale au paramètre @model_year.

Une fois la fonction à valeur de tableau créée, vous pouvez la trouver sous Programmabilité > Fonctions > Fonctions à valeur de tableau comme le montre l’image suivante :

La fonction ci-dessus renvoie l’ensemble des résultats d’une seule SELECT déclaration, par conséquent, elle est également connue sous le nom de fonction inline Table-valued.

Exécution d’une fonction à valeur de tableau

Pour exécuter une fonction à valeur de tableau, vous l’utilisez dans la clause FROM de l’instruction SELECT :

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

Dans cet exemple, nous avons sélectionné les produits dont l’année-modèle est 2017.

Vous pouvez également spécifier les colonnes à renvoyer par la fonction de valeur de tableau comme suit :

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

Voici la sortie partielle :

Modification d’une fonction table-valued

Pour modifier une fonction table-valued, vous utilisez le mot-clé ALTER au lieu de CREATE. Le reste du script est le même.

Par exemple, l’instruction suivante modifie la fonction udfProductInYear en changeant le paramètre existant et en ajoutant un paramètre supplémentaire :

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 fonction udfProductInYear renvoie désormais les produits dont l’année modèle est comprise entre une année de début et une année de fin.

L’instruction suivante appelle la fonction udfProductInYear pour obtenir les produits dont l’année modèle est comprise entre 2017 et 2018:

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

Voici la sortie partielle :

Multi-statement table-.valued functions (MSTVF)

Une fonction table-valued multi-statement ou MSTVF est une fonction table-valued qui renvoie le résultat de plusieurs déclarations.

La fonction à valeur de table multi-états est très utile car vous pouvez exécuter plusieurs requêtes au sein de la fonction et agréger les résultats dans la table retournée.

Pour définir une fonction à valeur de table multi-états, vous utilisez une variable de table comme valeur de retour. À l’intérieur de la fonction, vous exécutez une ou plusieurs requêtes et insérez des données dans cette variable de table.

La fonction udfContacts() suivante combine les employés et les clients en une seule liste de contacts:

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)

L’instruction suivante illustre la façon d’exécuter une fonction à valeurs de table à plusieurs états udfContacts :

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

Sortie:

Quand utiliser les fonctions évaluées par table

Nous utilisons généralement les fonctions évaluées par table comme des vues paramétrées. Par rapport aux procédures stockées, les fonctions à valeur de table sont plus flexibles car nous pouvons les utiliser partout où des tables sont utilisées.

Dans ce tutoriel, vous avez appris à connaître les fonctions à valeur de table de SQL Server, y compris les fonctions à valeur de table en ligne et les fonctions à valeur de table multi-états.

Les fonctions à valeur de table sont des fonctions à valeur de table.