CREATE FUNCTION (Azure Synapse Analytics et Microsoft Fabric)

S’applique à : point de terminaison d’analytique SQL (PDW) Azure Synapse Analytics Platform System (PDW) dans Microsoft Fabric Warehouse dans Microsoft Fabric

Crée une fonction définie par l’utilisateur (UDF) dans Azure Synapse Analytics, Analytics Platform System (PDW) ou Microsoft Fabric. Une fonction définie par l’utilisateur est une routine Transact-SQL qui accepte des paramètres, exécute une action, par exemple un calcul complexe, et retourne le résultat de cette action sous forme de valeur.

• Dans Analytics Platform System (PDW), la valeur de retour doit être une valeur scalaire (unique).

• Dans Azure Synapse Analytics, CREATE FUNCTION peut retourner une table suivant la syntaxe des fonctions table inline (préversion) ou bien une valeur unique suivant la syntaxe des fonctions scalaires.

• Dans Microsoft Fabric et les pools SQL serverless dans Azure Synapse Analytics, CREATE FUNCTION peut créer des fonctions de valeur de table inline, mais pas des fonctions scalaires. Les fonctions table définies par l’utilisateur (TVF) retournent un type de données table.

  Utilisez cette instruction pour créer une routine réutilisable, exploitable :

• Dans les instructions Transact-SQL telles que SELECT

• dans des applications appelant la fonction ;

• dans la définition d'une autre fonction définie par l'utilisateur ;

• pour définir une contrainte CHECK sur une colonne ;

• pour remplacer une procédure stockée.

• Utiliser une fonction inline comme prédicat de filtre pour une stratégie de sécurité

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe de la fonction scalaire

-- Transact-SQL Scalar Function Syntax  (in dedicated pools in Azure Synapse Analytics and Parallel Data Warehouse)
-- Not available in the serverless SQL pools in Azure Synapse Analytics or Microsoft Fabric

CREATE FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]  
  
<function_option>::=   
{  
    [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
}  

Syntaxe de la fonction table inline

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics and Microsoft Fabric
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Arguments

schema_name

Nom du schéma auquel appartient la fonction définie par l’utilisateur.

function_name

Nom de la fonction définie par l’utilisateur. Les noms de fonctions doivent respecter les règles applicables aux identificateurs et doivent être uniques dans la base de données et pour son schéma.

@parameter_name

Paramètre dans la fonction définie par l'utilisateur. Un ou plusieurs paramètres peuvent être déclarés.

Une fonction peut comprendre au maximum 2 100 paramètres. La valeur de chaque paramètre déclaré doit être fournie par l'utilisateur lors de l'exécution de la fonction, sauf si vous définissez une valeur par défaut pour le paramètre.

Spécifiez un nom de paramètre en utilisant un signe @ comme premier caractère. Le nom du paramètre doit respecter les règles applicables aux identificateurs. Un paramètre étant local à une fonction, vous pouvez utiliser le même nom dans d'autres fonctions. Les paramètres ne peuvent que prendre la place de constantes ; ils ne peuvent pas être utilisés à la place de noms de tables, de colonnes ou d'autres objets de base de données.

parameter_data_type

Type de données du paramètre. Pour les fonctions Transact-SQL, tous les types de données scalaires pris en charge dans Azure Synapse Analytics sont autorisés. Le type de données timestamp (rowversion) n’est pas un type pris en charge.

[ =default ]

Valeur par défaut pour le paramètre. Si une valeur default est définie, la fonction peut être exécutée sans spécifier de valeur pour ce paramètre.

Lorsque l'un des paramètres de la fonction possède une valeur par défaut, le mot clé DEFAULT doit être spécifié lors de l'appel de la fonction afin de récupérer la valeur par défaut. Ce comportement est différent de l'utilisation de paramètres avec des valeurs par défaut dans des procédures stockées pour lesquelles l'omission du paramètre implique également la prise en compte de la valeur par défaut.

return_data_type

Valeur de retour d'une fonction scalaire définie par l'utilisateur. Pour les fonctions Transact-SQL, tous les types de données scalaires pris en charge dans Azure Synapse Analytics sont autorisés. Le type de données timestamp rowversion/n’est pas un type pris en charge. Les types non scalaires cursor et table ne sont pas autorisés.

function_body

Série d’instructions Transact-SQL. Le function_body ne peut pas contenir d’instruction SELECT et ne peut pas référencer les données de base de données. Le function_body ne peut pas référencer des tables ou des vues. Le corps de la fonction peut appeler d’autres fonctions déterministes, mais ne peut pas appeler de fonctions non déterministes.

Dans les fonctions scalaires, function_body est une série d’instructions Transact-SQL qui, ensemble, prennent une valeur scalaire.

scalar_expression

Indique la valeur scalaire retournée par la fonction scalaire.

select_stmt

Instruction unique SELECT qui définit la valeur de retour d’une fonction table inline. Pour une fonction table inline, il n’existe aucun corps de fonction ; la table est le jeu de résultats d’une instruction unique SELECT .

TABLE

Indique que la valeur de retour de la fonction table est une table. Seules des constantes et des @local_variables peuvent être passés aux fonctions table.

Dans les fichiers TVFs inline (préversion), la valeur de retour TABLE est définie par le biais d’une instruction unique SELECT . Aucune variable retournée n'est associée à une fonction en ligne.

<function_option>

Spécifie que la fonction a une ou plusieurs des options suivantes.

SCHEMABINDING

Indique que la fonction est liée aux objets de base de données auxquels elle fait référence. Si SCHEMABINDING est précisé, les objets de base ne peuvent pas être modifiés d'une manière susceptible d'affecter la définition de la fonction. Cette dernière doit d'ailleurs être modifiée ou supprimée au préalable pour supprimer les dépendances par rapport à l'objet qui doit être modifié.

La liaison de la fonction aux objets auxquels elle fait référence est supprimée uniquement lorsqu'une des actions suivantes se produit :

• La fonction est supprimée.

• La fonction est modifiée, avec l'instruction ALTER, sans spécification de l'option SCHEMABINDING.

Une fonction peut être liée au schéma uniquement si les conditions suivantes sont vérifiées :

• Toute fonction définie par l’utilisateur référencée par la fonction est également liée au schéma.

• Les fonctions et autres fonctions définies par l’utilisateur référencées par la fonction sont référencées à l’aide d’un nom en une ou deux parties.

• Seules les fonctions intégrées et les autres fonctions définies par l’utilisateur dans la même base de données peuvent être référencées dans le corps des fonctions définies par l’utilisateur.

• L’utilisateur qui a exécuté l’instruction CREATE FUNCTION dispose de l’autorisation REFERENCES sur les objets de base de données référencés par la fonction.

Pour supprimer SCHEMABINDING, utilisez ALTER.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Spécifie l’attribut OnNULLCall d’une fonction scalaire. S’il n’est pas spécifié, CALLED ON NULL INPUT est implicite par défaut et le corps de la fonction s’exécute même s’il NULL est passé en tant qu’argument.

Bonnes pratiques

Si une fonction définie par l'utilisateur n'est pas créée avec la clause SCHEMABINDING, les modifications apportées aux objets sous-jacents peuvent affecter la définition de la fonction et produire des résultats inattendus en cas d'appel. Nous vous recommandons d'implémenter l'une des méthodes suivantes pour vous assurer que la fonction ne devient pas obsolète en raison des modifications apportées à ses objets sous-jacents :

• Spécifiez la clause WITH SCHEMABINDING lors de la création de la fonction. Vous vous assurez ainsi que les objets référencés dans la définition de la fonction ne peuvent pas être modifiés sauf si la fonction est également modifiée.

Interopérabilité

Les instructions suivantes sont valides dans une fonction scalaire :

• Instructions d'affectation

• Instructions de contrôle de flux à l'exception des instructions TRY...CATCH

• Instructions DECLARE définissant des variables de données locales.

Dans une fonction table inline (préversion), une seule instruction SELECT est autorisée.

Limites

Les fonctions définies par l'utilisateur ne permettent pas d'exécuter des actions qui modifient l'état des bases de données.

Les fonctions définies par l'utilisateur peuvent être imbriquées ; en d'autres termes, une fonction définie par l'utilisateur peut en appeler une autre. Le niveau d'imbrication est incrémenté lorsque la fonction appelée commence à s'exécuter, et décrémenté lorsque l'exécution est terminée. Les fonctions définies par l'utilisateur peuvent être imbriquées jusqu'à 32 niveaux. Le dépassement des niveaux d'imbrication maximum autorisés, provoque l'échec de la totalité de la chaîne de fonctions appelantes.

Les objets, y compris les fonctions, ne peuvent pas être créés dans la base de données master de votre pool SQL serverless dans Azure Synapse Analytics.

Métadonnées

Cette section répertorie les vues de catalogue système que vous pouvez utiliser pour retourner des métadonnées sur les fonctions définies par l’utilisateur.

• sys.sql_modules : affiche la définition des fonctions définies par l’utilisateur Transact-SQL. Par exemple :

  SELECT definition, type   
  FROM sys.sql_modules AS m  
  JOIN sys.objects AS o   
      ON m.object_id = o.object_id   
      AND type = ('FN');  
  

• sys.parameters : Affiche des informations sur les paramètres définis dans les fonctions définies par l'utilisateur.

• sys.sql_expression_dependencies : Affiche les objets sous-jacents référencés par une fonction.

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 en cours de création.

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

A. Utiliser une fonction scalaire définie par l’utilisateur pour modifier un type de données

Cette fonction simple prend un type de données int comme entrée et retourne un type de données decimal(10,2) comme sortie.

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN  
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  
  
SELECT dbo.ConvertInput(15) AS 'ConvertedValue';  

Exemples : Azure Synapse Analytics

A. Créer une fonction table incluse

L’exemple suivant crée une fonction table inline pour retourner des informations clés sur les modules, en filtrant selon le paramètre objectType . Elle inclut une valeur par défaut pour retourner tous les modules lorsque la fonction est appelée avec le DEFAULT paramètre. Cet exemple utilise certains des affichages catalogue système mentionnés dans Métadonnées.

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

La fonction peut alors être appelée pour retourner tous les objets de vue (V) avec :

select * from dbo.ModulesByType('V');

B. Combiner les résultats d’une fonction table inline

Cet exemple simple utilise la fonction table inline qui a été créée pour montrer comment ses résultats peuvent être combinés avec d’autres tables à l’aide de CROSS APPLY. Ici, nous sélectionnons toutes les colonnes à partir des deux sys.objects et les résultats de ModulesByType toutes les lignes correspondant à la colonne de type . Pour plus d’informations sur APPLY, consultez clause FROM plus JOIN, APPLY, PIVOT.

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

Étape suivante