par Robert Vieira
Maîtrisez les UDF de SQL Server 2000
J'ai commencé à travailler avec SQL Server en version 6.0. Bien que j'aie antérieurement
déjà utilisé le langage SQL en environnement VMS en 1989, la majeure partie de
mon expérience relative aux bases de données s'était déroulée dans un environnement
de fichiers plats. J'ai été étonné de constater que les opérations sur les enregistrements
de type curseur ne me manquaient pas autant que la capacité de définir simplement
des fonctions. Avec SQL Server 2000, je peux enfin créer des UDF (User-Defined
Functions : fonctions définies par l'utilisateur) utilisables non seulement dans
les scripts, mais aussi en ligne dans des instructions DML (Data Manipulation
Language). SQL Server 2000 possède deux types d'UDF. Le premier type renvoie une
valeur scalaire et le second des tables. Dans le présent article, je présente
des exemples de chacun des deux types et décris brièvement la manière d'utiliser
les UDF dans des applications SQL Server 2000.
à€ bien des égards, les UDF ressemblent à des procédures cataloguées
Les fonctions définies par l’utilisateur
Les UDF qui renvoient une valeur scalaire sont probablement le type le plus demandé.
A l’instar de GETDATE(), USER() et de plusieurs autres fonctions intégrées propres
à SQL Server, ce type d’UDF permet de définir du code renvoyant une valeur scalaire
au script, à la procédure ou à la ligne de code appelant.
à€ bien des égards, les UDF ressemblent à des procédures cataloguées. Comme ces
dernières, les UDF admettent des paramètres et renvoient une valeur. Cependant,
contrairement aux procédures cataloguées, les UDF ne peuvent pas disposer de paramètres
en sortie. Plus important, la valeur de retour d’une UDF n’est pas limitée à un
nombre entier. Au contraire, cette valeur peut être constituée de tout type de
données SQL Server valide, à l’exception des BLOB, curseurs et horodateurs.
Voyons un exemple qui présente comment construire simplement une fonction permettant
de gérer un problème auquel les développeurs SQL Server sont souvent confrontés,
à savoir vérifier si la saisie d’un champ datetime s’est produite à une date spécifique.
Un champ de type datetime possède habituellement des informations spécifiques
de type date et heure qui rendent les comparaisons uniquement sur les saisies
de dates difficiles. Plus spécifiquement, deux champs possédant la même date mais
des heures différentes ne seront pas considérés comme égaux.
Une solution classique consiste à convertir la date en une chaîne de caractères,
puis de nouveau en date après avoir tronqué les données relatives à l’heure. Si
on réalise cette conversion sur les deux champs de type datetime que l’on souhaite
comparer, la comparaison sur la seule date (à l’exclusion de l’heure) fonctionnera.
La solution peut ressembler au code présenté dans le listing 1, dont voici le
résultat :
(1 row(s) affected)
KeyCol TestDate
——- ———–
(0 row(s) affected)
KeyCol TestDate
——- ———–
12000-02-10 21:29:43.140
(1 row(s) affected)
La deuxième requête est un peu plus complexe; plus on imbrique de fonctions dans
une requête, moins le code est lisible. Il m’est quelquefois arrivé de reformater
plusieurs dates pour en extraire uniquement le jour, sans l’heure. Cependant,
le code résultant était difficile à lire.
Maintenant, je fais les mêmes opérations avec une UDF simple. D’abord, j’utilise
la nouvelle commande CREATE FUNCTION pour créer la fonction. Je formate cette
commande de manière très analogue à une procédure cataloguée. Je pourrais écrire
la requête de la manière suivante :
CREATE FUNCTION DayOnly(@Date datetime)
RETURNS varchar(12)
AS
BEGIN
RETURN CONVERT(varchar(12), @Date, 101)
END
Désormais, je reformate la requête légèrement pour tirer profit de la nouvelle
fonction :
SELECT *
FROM TestTable
WHERE dbo.DayOnly(TestDate) = dbo.DayOnly(GETDATE())
Même pour cette requête simple, le nouveau code est beaucoup plus lisible. L’appel
de la fonction est structuré de la même manière que dans la plupart des langages
de programmation qui supportent les fonctions, avec toutefois une particularité
: le propriétaire (schéma) est nécessaire pour exécuter la fonction. Pour quelque
obscure raison, SQL Server ne résout pas les fonctions comme il résout les autres
objets.
Les UDF présentent d’autres avantages que la simple lisibilité. On peut imbriquer
des requêtes dans des UDF et ainsi les utiliser pour encapsuler des sous-requêtes.
En fait, il est possible d’encapsuler pratiquement toute requête renvoyant une
valeur discrète. Pour illustrer ce point, encapsulons l’exemple de sous-requête
de stock provenant de SQL Server Books Online (BOL). La version utilisant la sous-requête
ressemble au listing 2. Les versions encapsulées des sous-requêtes calculant la
moyenne et la différence de prix sont proposées dans les fonctions du listing
3. Notez qu’il est permis d’imbriquer une UDF dans une autre.
Le listing 4 contient le résultat de la requête BOL que j’ai modifiée pour utiliser
les nouvelles fonctions au lieu du vieux modèle de sous-requête. L’exécution de
cette requête génère les mêmes résultats que l’exécution de la sous-requête. En
plus d’être plus lisible, l’UDF est réutilisable, ce qui permet de gagner du temps
au fur et à mesure que les fonctions deviennent plus complexes.
Téléchargez cette ressource
Solutions Cloud & Services Managés Simplifiés
Comment capitaliser sur son existant tout en bénéficiant, dès à présent, des promesses de flexibilité et de scalabilité du cloud ? Découvrez les bonnes pratiques pour répondre aux défis de simplification du Cloud dans ce nouveau TOP 5.
Les articles les plus consultés
Les plus consultés sur iTPro.fr
- Le spatial dans le viseur des cyberattaquants
- Connaître son client : exploiter les API des réseaux pour offrir des services personnalisés et sur mesure
- Architecte cloud : applications de chatbot & Azure OpenAI Service
- Le LLMjacking : quand les cyberattaques utilisent illicitement des comptes LLM
- Les identités des développeurs doivent être prises en compte !