Partager via

Créer des fonctions définies par l’utilisateur (moteur de base de données)

Cette rubrique explique comment créer une fonction définie par l’utilisateur dans SQL Server à l’aide de Transact-SQL.

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • Les fonctions définies par l’utilisateur ne peuvent pas être utilisées pour effectuer des actions qui modifient l’état de la base de données.

  • Les fonctions définies par l’utilisateur ne peuvent pas contenir une clause OUTPUT INTO qui a une table comme cible.

  • Les fonctions définies par l’utilisateur ne peuvent pas retourner plusieurs jeux de résultats. Utilisez une procédure stockée si vous devez retourner plusieurs jeux de résultats.

  • La gestion des erreurs est limitée dans une fonction définie par l’utilisateur. Une fonction UDF ne prend pas en charge TRY... CATCH ou @ERROR RAISERROR.

  • Les fonctions définies par l’utilisateur ne peuvent pas appeler une procédure stockée, mais peuvent appeler une procédure stockée étendue.

  • Les fonctions définies par l’utilisateur ne peuvent pas utiliser des tables SQL ou temporaires dynamiques. Les variables de table sont autorisées.

  • Les instructions SET ne sont pas autorisées dans une fonction définie par l’utilisateur.

  • La clause FOR XML n’est pas autorisée

  • Les fonctions définies par l’utilisateur peuvent être imbriquées ; autrement dit, une fonction définie par l’utilisateur peut appeler une autre. Le niveau d’imbrication est incrémenté lorsque la fonction appelée démarre l’exécution et décrémentée lorsque la fonction appelée termine l’exécution. Les fonctions définies par l’utilisateur peuvent être imbriquées jusqu’à 32 niveaux. Le dépassement des niveaux maximal d’imbrication entraîne l’échec de l’ensemble de la chaîne de fonctions appelantes. Toute référence au code managé à partir d’une fonction Transact-SQL définie par l’utilisateur compte comme un niveau par rapport à la limite d’imbrication de 32 niveaux. Les méthodes appelées à partir du code managé ne comptent pas sur cette limite.

  • Les instructions Service Broker suivantes ne peuvent pas être incluses dans la définition d’une fonction Transact-SQL définie par l’utilisateur :

    • COMMENCER UNE CONVERSATION DE BOÎTE DE DIALOGUE

    • FIN DE LA CONVERSATION

    • Obtenir Groupe de conversation

    • DÉPLACER UNE CONVERSATION

    • RECEVOIR

    • ENVOYER

Sécurité

Autorisations

Nécessite l’autorisation CREATE FUNCTION dans la base de données et l’autorisation ALTER sur le schéma dans lequel la fonction est créée. Si la fonction spécifie un type défini par l’utilisateur, nécessite l’autorisation EXECUTE sur le type.

Fonctions scalaires

L’exemple suivant crée une fonction scalaire à plusieurs états dans la base de données AdventureWorks2012. La fonction prend une valeur d’entrée, un ProductIDet retourne une valeur de données unique, la quantité agrégée du produit spécifié dans l’inventaire.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
GO  
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int   
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)   
    FROM Production.ProductInventory p   
    WHERE p.ProductID = @ProductID   
        AND p.LocationID = '6';  
     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END;  
GO

L’exemple suivant utilise la ufnGetInventoryStock fonction pour retourner la quantité actuelle d’inventaire pour les produits dont ProductModelID la valeur est comprise entre 75 et 80.

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply  
FROM Production.Product  
WHERE ProductModelID BETWEEN 75 and 80;

FONCTIONS Table-Valued

L’exemple suivant crée une fonction table inline dans la base de données AdventureWorks2012. La fonction prend un paramètre d’entrée, un ID client (store) et retourne les colonnes ProductID, Nameet l’agrégat des ventes d’année à jour comme YTD Total pour chaque produit vendu au magasin.

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL  
    DROP FUNCTION Sales.ufn_SalesByStore;  
GO  
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
    FROM Production.Product AS P   
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
);

L’exemple suivant appelle la fonction et spécifie l’ID client 602.

SELECT * FROM Sales.ufn_SalesByStore (602);

L’exemple suivant crée une fonction table dans la base de données AdventureWorks2012. La fonction prend un paramètre d'entrée unique, un EmployeeID, et retourne une liste de tous les employés qui se rapportent directement ou indirectement à l'employé spécifié. La fonction est ensuite appelée en spécifiant l’ID d’employé 109.

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL  
    DROP FUNCTION dbo.ufn_FindReports;  
GO  
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)  
RETURNS @retFindReports TABLE   
(  
    EmployeeID int primary key NOT NULL,  
    FirstName nvarchar(255) NOT NULL,  
    LastName nvarchar(255) NOT NULL,  
    JobTitle nvarchar(50) NOT NULL,  
    RecursionLevel int NOT NULL  
)  
--Returns a result set that lists all the employees who report to the   
--specific employee directly or indirectly.*/  
AS  
BEGIN  
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns  
    AS (  
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n  
        FROM HumanResources.Employee e   
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        WHERE e.BusinessEntityID = @InEmpID  
        UNION ALL  
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor  
        FROM HumanResources.Employee e   
            INNER JOIN EMP_cte  
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode  
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        )  
-- copy the required columns to the result of the function   
   INSERT @retFindReports  
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
   FROM EMP_cte   
   RETURN  
END;  
GO  
-- Example invocation  
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);

Voir aussi

Fonctions définies par l'utilisateur
CRÉER FONCTION (Transact-SQL)

  • Last updated on