Créer des fonctions définies par l’utilisateur

  • 5 minutes

Les fonctions définies par l’utilisateur sont similaires aux procédures stockées dans la mesure où elles sont stockées séparément des tables de la base de données. Ces fonctions acceptent les paramètres, effectuent une action, puis retournent le résultat de l’action sous la forme d’une valeur unique (scalaire) ou d’un jeu de résultats (table). Vous pouvez ensuite utiliser la fonction à la place d’une table lorsque vous écrivez une instruction SELECT. Les fonctions définies par l’utilisateur sont conçues pour effectuer des calculs et utiliser ce résultat dans une autre instruction. Alors que les procédures stockées peuvent encapsuler la fonction et l’instruction, et même modifier les données dans la base de données.

Nous allons étudier trois types de fonctions définies par l’utilisateur. Pour plus d’informations sur les différentes fonctions, consultez la documentation de référence de T-SQL.

Fonctions table incluses

Les fonctions table incluses (TVF) sont les fonctions les plus simples créées sur la base d’une instruction SELECT et constituent le meilleur choix pour les performances.

Dans l'exemple suivant, une fonction table est créée avec un paramètre d'entrée pour unitprice.

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost;

Lorsque la fonction table est exécutée avec une valeur pour le paramètre, tous les produits dont le prix unitaire est supérieur à cette valeur sont retournés.

Le code suivant utilise la fonction table au lieu d’une table.

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

Fonctions table multi-instructions

Contrairement à la fonction TVF incluse, une fonction table multi-instruction (MSTVF) peut avoir plusieurs instructions et a des conditions de syntaxe différentes.

Notez comment dans le code suivant, nous utilisons un BEGIN/END en plus de RETURN :

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

Une fois créé, vous référencez la fonction MSTVF à la place d’une table tout comme avec la fonction incluse précédente ci-dessus. Vous pouvez également référencer la sortie dans la clause FROM et la joindre à d’autres tables.

SELECT *
FROM Sales.mstvf_OrderStatus();

Considérations relatives aux performances

L’optimiseur de requête ne peut pas estimer le nombre de lignes retournées pour une fonction table multi-instruction, mais le peut pour une fonction table incluse. Par conséquent, utilisez la fonction TVF incluse lorsque cela est possible pour de meilleures performances. Si vous n’avez pas besoin de joindre la fonction MSTVF à d’autres tables et/ou si vous savez que le résultat ne sera que quelques lignes, l’impact sur les performances n’est pas aussi important. Si vous attendez un grand jeu de résultats et que vous devez joindre d’autres tables, envisagez plutôt d’utiliser une table temporaire pour stocker les résultats, puis de joindre la table temporaire.

Dans SQL Server versions 2017 et ultérieures, Microsoft a introduit des fonctionnalités pour le traitement intelligent des requêtes afin d’améliorer les performances des fonctions MSTVF. Pour plus d’informations sur les fonctionnalités de traitement intelligent des requêtes, consultez la documentation de référence de T-SQL.

Fonctions définies par l’utilisateur scalaires

Une fonction définie par l’utilisateur scalaire retourne une seule valeur contrairement aux fonctions table et est donc souvent utilisée pour les instructions simples et fréquentes.

Voici un exemple pour obtenir le prix de la liste des produits pour un produit spécifique un jour donné :

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

Pour cette fonction, les deux paramètres doivent être fournis pour obtenir la valeur. Selon la fonction, vous pouvez lister la fonction dans l’instruction SELECT dans une requête plus complexe.

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

Lier une fonction à des objets référencés

SCHEMABINDING est facultatif lors de la création de la fonction. Lorsque vous spécifiez SCHEMABINDING, il lie la fonction aux objets référencés, puis les objets ne peuvent pas être modifiés sans modifier la fonction. La fonction doit être modifiée ou supprimée au préalable pour supprimer les dépendances avant de modifier l’objet.

SCHEMABINDING est supprimé si l’une des opérations suivantes se produit :

  • La fonction est supprimée
  • La fonction est modifiée avec l’instruction ALTER sans spécifier SCHEMABINDING