SQL Server 2000 Episode 1 : les fonctions

Par Frédéric Colin, posté le 12/02/2001

Profil : Développeur | Niveau : Intermédiaire (200)

Tags : SQL Server | Partager :

Préambule

L'objectif des articles qui vont se succéder sur Microsoft SQL Server 2000 est de se focaliser sur les nouvelles fonctionnalités offertes par le produit. Il est à noter que ces articles sont principalement orientés développement.

Il y aurait beaucoup de choses à dire sur Microsoft SQL Server 2000. Parmi les nouveautés, nous citerons dans le désordre et de façon non-exhaustive :

• Prise en compte de plusieurs instances sur une même machine
• Prise en compte des intégrités référentielles en cascade
• Prise en charge de XML en natif
• Création de vues partitionnées distribuées
• Indexation possible des vues
• Installation sans assistance (fichier ISS)
• Création de tables en mémoire
• Classements par base de données, par colonne de table
• Possibilité de créations de fonctions

Aujourd'hui, je parlerai volontiers de la notion de fonctions définies par l'utilisateur et des tables en mémoire. Mais rassurez-vous (!) les autres points seront abordés dans les prochains articles.

Introduction

Les fonctions sont des routines écrites en Transact-SQL qui permettent de regrouper des actions au sein d'entités réutilisables. Elles permettent d'étendre le panel de celles offertes par Transact-SQL. Il existe 3 types de fonctions :

• Fonctions scalaires paramétrées renvoyant des données de types simples
• Fonctions mono-instruction renvoyant des données de type table
• Fonctions multi-instructions renvoyant des données de type table

Les deux derniers types de fonction ouvrent de nouvelles perspectives pour ce qui est du travail avec des données temporaires au sein de tables temporaires en mémoire. Cette possibilité est illustrée au sein du paragraphe suivant.

Les fonctions peuvent être fortement liées à un schéma relationnel. En effet, puisqu'elles peuvent utiliser des éléments de la base de données sur lesquelles elles sont définies (tables, vues, champs et autres fonctions), il devient intéressant de ne pas pouvoir modifier ces mêmes éléments s'ils sont référencés par des fonctions. C'est ce que l'on appelle le «SCHEMABINDING». Bien entendu, un certain nombre de conditions doivent être remplies pour lier ces fonctions aux éléments de base :

• Avoir les droits de références sur les éléments
• Tous les éléments liés à la fonction doivent appartenir à la même base de données
• Toutes les vues référencées par une fonction liée au schéma relationnel doivent elles-aussi l'être

Mais entrons dans le vif du sujet et parlons un peu syntaxe.

3 Les fonctions définies par l'utilisateur (UDF : User Defined Functions)

Les UDFs sont créées au niveau de chaque base de données installées sur une instance SQL Server 2000 donnée. Il est à noter qu'elle ne viennent pas remplacer les procédures stockées SQL Server, mais étendre les possibilités offertes par ces dernières. En effet, il sera maintenant possible de créer ses propres fonctions Transact-SQL qui viendront compléter le panel de celle fournies par le système. De plus, ces fonctions pourront se comporter comme des sources de données et apparaître ainsi après la clause « FROM » d'un « SELECT ». Il sera ainsi possible d'obtenir ce que l'on pourrait qualifier de vue paramétrée.

Voici le prototype d'une fonction générée par « l'enterprise manager » de SQL Server 2000 :

   1:  CREATE FUNCTION [OWNER].[FUNCTION NAME] 

   2:  (PARAMETER LIST)  

   3:  RETURNS (return_type_spec) 

   4:  AS  

   5:  BEGIN 

   6:  (FUNCTION BODY) 

   7:  END

Les noms des fonctions ainsi que les paramètres suivent les mêmes règles syntaxiques que les procédures stockées. Les fonctions admettent jusqu'à 1024 paramètres (en entrée) et peuvent renvoyer des données scalaires ou bien une table dans le sens SQL du terme.

Voici un exemple de fonction très simple :

   1:  CREATE FUNCTION MyFunction 

   2:      (@parLngValue1 int, @parLngValue2 int, @parLngIncrement Int) 

   3:  RETURNS Int 

   4:  AS  

   5:  BEGIN 

   6:   

   7:      Declare @LocLngReturnValue Int

   8:   

   9:      If @parLngValue1 > @parLngValue2

  10:      Begin

  11:          Select @LocLngReturnValue = @parLngValue1 - @parLngIncrement

  12:      End

  13:      Else

  14:      Begin

  15:          Select @LocLngReturnValue = @parLngValue2 - @parLngIncrement

  16:      End

  17:   

  18:      Return (@LocLngReturnValue)

  19:  END

Voici un exemple d'utilisation à partir de l'analyseur de requêtes :

   1:  SELECT dbo.MyFunction(1, 2, 10)

   2:  SELECT dbo.MyFunction(2, 10, 10)

Le premier appel retourne bien sûr « -8 », le second « 0 ».

Voici les possibilités pour l'utilisation de fonctions. A noter qu'elles sont utilisables dans toutes les expressions « ORDER BY » et « GROUP BY » et qu'elle peuvent intervenir en tant que source de données dans la clause « FROM » si elles renvoient des données de type table.

   1:  SELECT  f(c1)

   2:  FROM NomTable

   3:   

   4:  SELECT c1

   5:  FROM NomTable

   6:  WHERE f2(c2) = 3

   7:   

   8:  SELECT c1 

   9:  FROM f(arg)

  10:   

Les instructions que l'on peut retrouver dans une fonction sont de plusieurs ordres :

• Déclarations locales de variables, de curseurs
• Assignement de valeurs à des objets locaux (variables, tables temporaires)
• Opérations sur des curseurs locaux correctement déclarés et ouverts
• Opérations de contrôle de flux (conditions, boucles)
• Ordres « Select » qui permettent d'affecter des variables locales à la fonction
• Ordres « Insert », « Update » et « Delete » modifiant des variables de type « Table » locales à la fonction
• Exécution de procédures stockées étendues

Remarque : il n'est pas possible d'appeler, dans le corps d'une fonction une autre fonction qui ne renverrait pas systématiquement la même valeur entre différents appels (à paramètres égaux). Par exemple : « GETDATE ».

Voici un exemple de définition d'une table temporaire créée au sein d'une fonction :

   1:  CREATE FUNCTION GetDaltonID 

   2:      (@parStrNom varchar(30)) 

   3:  RETURNS int

   4:  AS  

   5:  BEGIN 

   6:      Declare @LocLngReturnValue Int

   7:   

   8:      Declare @t table(tab_LngID Int, tab_StrNom Varchar(30))

   9:   

  10:      Insert @t values(10, 'Joe')

  11:      Insert @t values(20, 'Jack')

  12:      Insert @t values(30, 'William')

  13:      Insert @t values(40, 'Averell')

  14:   

  15:      Select @LocLngReturnValue = tab_LngID 

  16:      From @t

  17:      Where tab_StrNom = @parStrNom

  18:   

  19:      Return (@LocLngReturnValue)

  20:  END

Une utilisation possible de la fonction « GetDaltonID » :

   1:  select dbo.GetDaltonID('Joe')

   2:  select dbo.GetDaltonID('Averell')

Les résultats renvoyés sont bien entendu « 10 » et « 40 ».

Voici un exemple de définition dans le cadre d'une vue paramétrée :

   1:  CREATE FUNCTION GetTable

   2:      (@parLngID Int)

   3:  RETURNS @TableName table (Nom Varchar(50)) AS  

   4:  BEGIN 

   5:      Insert @TableName Select Nom From tTable Where tab_LngID = @parLngID

   6:      Return

   7:  END

Voici une utilisation possible :

   1:  Select * from dbo.GetTable(10)

Conclusion

L'ajout des fonctions au sein de SQL Server 2000 est un apport majeur dans la souplesse d'utilisation de Transact-SQL. Il devient en effet possible d'adopter les règles standards de programmation des langages traditionnels au sein d'un requêtage SQL. Si les fonctions courantes offertes par Transact-SQL ne répondent pas entièrement à nos besoins, les UDF permettront d'atteindre la fonctionnalité exacte recherchée de façon simple et rapide. Enfin, le dernier point sur lequel j'aimerais insister est l'utilisation des tables en mémoire qui ouvre de nouvelles possibilités dans l'utilisation de données temporaires.

Commentaires