Partager via

  • Article

Création de fonctions définies par l'utilisateur (moteur de base de données)

Les fonctions définies par l'utilisateur sont créées, modifiées et supprimées respectivement à l'aide des instructions CREATE FUNCTION, ALTER FUNCTION et DROP FUNCTION. Chaque nom complet de fonction définie par l'utilisateur (schema_name.function_name) doit être unique.

Consignes

Les erreurs Transact-SQL qui provoquent l'annulation d'une instruction et continuent avec l'instruction suivante dans le module (comme les déclencheurs ou les procédures stockées) sont traitées différemment à l'intérieur d'une fonction. Dans les fonctions, ces erreurs provoquent l'arrêt de l'exécution de la fonction, lequel provoque à son tour l'annulation de l'instruction qui a invoqué la fonction.

Les instructions contenues dans un bloc BEGIN...END ne peuvent pas avoir d'effets secondaires. Les effets secondaires d'une fonction sont toutes les modifications définitives de l'état d'une ressource dont la portée s'étend hors de la fonction, comme la modification d'une table de base de données. Les instructions d'une fonction ne peuvent modifier que les objets locaux de cette fonction, tels les variables ou les curseurs locaux. Les modifications apportées aux tables de base de données, les opérations portant sur des curseurs non locaux par rapport à la fonction, l'envoi de courrier électronique, les tentatives de modification de catalogue et la génération d'un ensemble de résultats retourné à l'utilisateur sont autant d'actions qui ne peuvent pas être exécutées dans une fonction.

Notes

Si une instruction CREATE FUNCTION produit des effets secondaires contre des ressources qui n'existent pas lorsque l'instruction CREATE FUNCTION est publiée, SQL Server exécute l'instruction. Toutefois, SQL Server n'exécute pas la fonction lorsqu'elle est appelée.

Le nombre d'exécutions effectives d'une fonction spécifiée dans une requête peut varier d'un plan d'exécution de l'optimiseur à l'autre. C'est par exemple le cas d'une fonction invoquée par une sous-requête dans une clause WHERE. Le nombre d'exécutions de la sous-requête et de sa fonction peut varier en fonction du chemin d'accès choisi par l'optimiseur.

Instructions valides dans une fonction

Les types d'instructions valides dans une fonction sont les suivants :

  • les instructions DECLARE permettant de définir des curseurs et des variables de données locaux de la fonction ;

  • les affectations de valeurs à des objets locaux de la fonction, comme l'attribution de valeurs à des variables locales scalaires ou de table à l'aide de SET ;

  • les opérations de curseur faisant référence à des curseurs locaux déclarés, ouverts, fermés et désalloués dans la fonction. Les instructions FETCH qui retournent des données au client ne sont pas autorisées (seules celles qui affectent des valeurs à des variables locales à l'aide de la clause INTO le sont) ;

  • les instructions de contrôle de flux, à l'exception des instructions TRY...CATCH ;

  • les instructions SELECT contenant des listes de sélection avec des expressions qui affectent des valeurs à des variables locales de la fonction ;

  • les instructions UPDATE, INSERT et DELETE modifiant les variables table locales de la fonction ;

  • les instructions EXECUTE appelant une procédure stockée étendue.

Fonctions système intégrées

Les fonctions intégrées non déterministes suivantes peuvent être utilisées dans les fonctions Transact-SQL définies par l'utilisateur.

CURRENT_TIMESTAMP

@@MAX_CONNECTIONS

GET_TRANSMISSION_STATUS

@@PACK_RECEIVED

GETDATE

@@PACK_SENT

GETUTCDATE

@@PACKET_ERRORS

@@CONNECTIONS

@@TIMETICKS

@@CPU_BUSY

@@TOTAL_ERRORS

@@DBTS

@@TOTAL_READ

@@IDLE

@@TOTAL_WRITE

@@IO_BUSY

 

Les fonctions intégrées non déterministes suivantes ne peuvent pas être utilisées dans les fonctions Transact-SQL définies par l'utilisateur.

NEWID

RAND

NEWSEQUENTIALID

TEXTPTR

Pour obtenir la liste des fonctions système intégrées déterministes et non déterministes, consultez Fonctions déterministes et non déterministes.

Fonctions liées au schéma

L'instruction CREATE FUNCTION prend en charge une clause SCHEMABINDING qui lie la fonction au schéma de tout objet auquel elle fait référence, tel qu'une table, une vue ou une fonction définie par l'utilisateur. Toute tentative de modification (ALTER) ou de suppression (DROP) d'un objet référencé par une fonction liée au schéma est vouée à l'échec.

Les conditions suivantes doivent être respectées pour que la clause SCHEMABINDING puisse être spécifiée dans CREATE FUNCTION :

  • Toutes les vues et fonctions définies par l'utilisateur référencées par la fonction doivent être liées au schéma.

  • Tous les objets référencés par la fonction doivent figurer dans la même base de données que la fonction. Les objets doivent être référencés à l'aide de noms à une ou deux composantes.

  • Vous devez posséder l'autorisation REFERENCES sur tous les objets (tables, vues et fonctions définies par l'utilisateur) référencés dans la fonction.

Vous pouvez utiliser l'instruction ALTER FUNCTION pour supprimer la liaison au schéma. Cette instruction doit redéfinir la fonction sans spécifier WITH SCHEMABINDING.

Spécification des paramètres

Une fonction définie par l'utilisateur accepte ou n'accepte pas de paramètres d'entrée et retourne une valeur scalaire ou une table. Une fonction peut comprendre jusqu'à 1 024 paramètres d'entrée. Lorsqu'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 d'obtenir la valeur par défaut. Ce comportement est différent de celui des paramètres avec valeurs par défaut des procédures stockées définies par l'utilisateur pour lesquelles l'omission du paramètre implique également la prise en compte de la valeur par défaut. Les fonctions définies par l'utilisateur ne prennent pas en charge les paramètres de sortie.