Partager via

  • Article

Fonctions définies par l'utilisateur incluses

Les fonctions définies par l'utilisateur incluses sont un sous-ensemble de fonctions définies par l'utilisateur qui retournent un type de données table. Les fonctions inline peuvent offrir une fonctionnalité équivalente à celle des vues paramétrées.

L'exemple suivant retourne les noms des magasins et les villes de la région spécifiée :

USE AdventureWorks;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

Cette vue serait plus performante si elle était plus générale et permettait aux utilisateurs de spécifier la région qu'ils souhaitent afficher. Les vues, toutefois, ne prennent pas en charge les paramètres dans les conditions de recherche spécifiées dans la clause WHERE. Les fonctions définies par l'utilisateur inline peuvent être utilisées pour prendre en charge ces paramètres. L'exemple suivant illustre une fonction inline qui permet aux utilisateurs de spécifier la région de leur choix dans leur requête :

USE AdventureWorks;
GO
IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;
GO
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT S.Name AS Store, A.City
        FROM Sales.Store AS S
        JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
        JOIN Person.Address AS A ON A.AddressID = CA.AddressID
        JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
        WHERE SP.Name = @Region
       );
GO
-- Example of calling the function for a specific region
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington');
GO

Règles des fonctions définies par l'utilisateur inline

Les fonctions définies par l'utilisateur inline suivent les règles ci-après :

  • La clause RETURNS contient uniquement le mot clé table. Vous n'avez pas besoin de définir le format d'une variable de retour car il est déterminé par le format du jeu de résultats de l'instruction SELECT figurant dans la clause RETURN.

  • Aucun function_body n'est délimité par BEGIN et END.

  • La clause RETURN contient une seule instruction SELECT entre parenthèses. L'ensemble de résultats de cette instruction constitue la table retournée par la fonction. L'instruction SELECT utilisée dans une fonction inline est soumise aux mêmes restrictions que les instructions SELECT utilisées dans les vues.

  • La fonction table accepte uniquement des constantes ou des arguments @local_variable.

Fonctions inline et vues indexées

Les fonctions inline permettent également d'accroître la puissance des vues indexées. La vue indexée ne peut pas elle-même utiliser de paramètres dans les conditions de recherche de sa clause WHERE pour adapter l'ensemble de résultats stocké à des utilisateurs spécifiques. Vous pouvez toutefois définir une vue indexée qui stocke le jeu complet de données correspondant à la vue, puis définir une fonction inline portant sur la vue indexée et contenant les conditions de recherche paramétrées qui permettent aux utilisateurs de personnaliser leurs résultats. Si la définition de la vue est complexe, la majeure partie du processus d'élaboration d'un ensemble de résultats implique des opérations telles que la création d'agrégats ou la liaison de plusieurs tables lorsque l'index cluster est créé sur la vue. Si vous créez alors une fonction inline qui fait référence à la vue indexée, celle-ci peut appliquer les filtres paramétrés de l'utilisateur pour retourner des lignes spécifiques de l'ensemble de résultats matérialisé de la vue indexée. Par exemple :

  1. Définissez une vue vw_QuarterlySales qui rassemble toutes les données de ventes pour former un ensemble de résultats indiquant les ventes trimestrielles totales pour l'ensemble des magasins.

  2. Créez un index cluster sur la vue vw_QuarterlySales pour matérialiser un ensemble de résultats contenant les données totalisées.

  3. Créez une fonction inline pour filtrer les données agrégées :

    CREATE FUNCTION dbo.ufn_QuarterlySalesByStore
     ( @StoreID int )
RETURNS table
AS
RETURN (
        SELECT *
        FROM SalesDB.dbo.vw_QuarterlySales
        WHERE StoreID = @StoreID
       )

  4. Les utilisateurs peuvent ensuite obtenir les données relatives à leur magasin en opérant un choix à partir de la fonction inline :

    SELECT *
FROM fn_QuarterlySalesByStore(14432)

La majeure partie du travail requis pour satisfaire les requêtes émises à l'étape 4 consiste à agréger les données de ventes par trimestre. Ce travail est réalisé en une seule fois à l'étape 2. Chaque instruction SELECT de l'étape 4 utilise la fonction fn_QuarterlySalesByStore pour filtrer les données agrégées en fonction du magasin correspondant.