Partager via

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

  • Article

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.

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

Dans SQL Server 2005, les fonctions intégrées non déterministes suivantes sont utilisables 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 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.

Voir aussi

Concepts

Création de fonctions CLR
Création d'agrégats définis par l'utilisateur
Exécution de fonctions définies par l'utilisateur (Moteur de base de données)
Affichage des fonctions définies par l'utilisateur

Autres ressources

CREATE FUNCTION (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005