Introduction aux Fonctions

1. Assurez-vous que vous disposez d'un compte d'utilisateur nommé pkatts (créé dans la leçon 1).
   Assurez-vous que vous avez créé une connexion pour le compte de pkatts (créé en leçon 3).
   Démarrer le SQL Server Management Studio.
2. Pour créer une nouvelle base de données, cliquez avec le bouton droit sur bases de données, puis cliquez sur nouvelle base de données...
3. Dans la zone de texte Nom, tapez RealEstate1
4. Définissez le propriétaire à nom_ordinateur \pkatts (Windows XP ou Vista) ou DomainName \pkatts (Windows Server)
5. Définissez les chemins pour le développement de bases de données de serveur SQL de C:\Microsoft
6. Cliquez sur OK
7. Dans l'Explorateur d'objets, développez le nœud de bases de données, suivi par le nœud RealEstate1
8. Sous RealEstate1, cliquez avec le bouton droit de la souris sur Sécurité, positionnez la souris sur Nouveau, puis cliquez sur schéma...
   
   
9. Tapez le nom comme Payroll et appuyez sur la touche Entrée

Il existe différentes façons que vous pouvez démarrer la création d'une fonction :

• Dans l'Explorateur d'objets, développez la base de données souhaitée. Développez le nœud par programme. Développez le nœud de fonctions. Cliquez avec le bouton droit de la souris sur Fonction Scalar-value et cliquez sur la fonction New Scalar-Valued... L'exemple de code serait générée pour vous :
  

  --================================================ -- Template generated from Template Explorer using : -- Create Scalar Function (New Menu) .SQL -- -- Use the Specify Values for Template Parameters -- Command (Ctrl-Shift-M) to fill in the parameter -- Values below. -- -- This block of comments will not be included in -- the definition of the function. --================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --============================================= -- Author : <Author,,Name> -- Create date : <Create Date, , > -- Description: < Description, > --============================================= CREATE FUNCTION < Scalar_Function_Name, sysname, FunctionName > ( --Add the parameters for the function here <@Param1, sysname, @p1> <Data_Type_For_Param1, ,int > ) RETURNS <Function_Data_Type, ,int > AS BEGIN -- Declare the return variable here DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, int> --Add the T-SQL statements to compute the return value here SELECT <@ResultVar, sysname, @Result > = <@Param1, sysname, @p1> --Return the result of the function RETURN <@ResultVar, sysname, @Result> END GO

  
  Vous pouvez ensuite modifier pour le personnaliser
• Ouvrez une fenêtre de requête vide. Affichez la fenêtre de l'Explorateur de modèles et développez le nœud de fonction. Faites glisser la création de fonctions de Scalar (nouveau menu) dans la fenêtre de requête
   
  

  --================================================ -- Template generated from Template Explorer using : -- Create Scalar Function (New Menu) .SQL -- -- Use the Specify Values for Template parameters -- commande (Ctrl-Maj-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the function. --================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO--============================================= -- Author : <Author,,Name> -- Create date: <Create Date, ,> -- Description: < Description, ,> --============================================= CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName > ( --Add the parameters for the function here <@ Param1, sysname, @p1> <Data_Type_For_Param1, int ,> ) RETURNS <Function_Data_Type, ,int> AS BEGIN --Declare the return variable here DECLARE <@ ResultVar, sysname, @Result> <Function_Data_Type, ,int > --Add the T-SQL statements to compute the return value here SELECT <@ResultVar, sysname, @ Result> = <@Param1, sysname, @p1> --Return the result of the function RETURN <@ResultVar, sysname, @Result> END GO

• Vous pouvez ouvrir une nouvelle fenêtre de requête vide et commencez à y taper votre code.

Dans Transact-SQL, la formule principale de la création d'une fonction est la suivante :

CREATE FUNCTION FunctionName ()

Nous avons déjà dit que, dans SQL, une fonction est créée sous la forme d'un objet. En tant que tel, elle doit avoir un nom. Dans nos leçons, voici les règles que nous utiliserons pour n'en nommer que nos fonctions :

• Un nom commence par un trait de soulignement ou une lettre. Exemples _n, act ou seconde
• Après le premier caractère comme un trait de soulignement ou une lettre, le nom aura des combinaisons de caractères de soulignement, de lettres et de chiffres. Les exemples sont _n24, act_52_t
• Un nom n'inclut pas les caractères spéciaux tels que!, @, #, $, %, ^, &, ou *
• Nous éviterons d'utiliser des espaces dans un nom, avec quelques exceptions
• Si le nom est une combinaison de mots, chaque mot commencera par des majuscules. Exemples : DateLoué, _RealSport ou DriversLicenseNumber

Pour qu'une fonction soit utile, elle doit produire un résultat. Cela est également dit que la fonction renvoie un résultat ou une valeur. Lors de la création d'une fonction, vous devez spécifier le type de valeur que la fonction renverrait. Pour fournir cette information, après le nom de la fonction, tapez le mot-clé RETURNS suivi d'une définition pour un type de données. Voici un exemple simple :

CREATE FUNCTION Addition() 
RETURNS Decimal(6,3)

Après avoir spécifié le type de valeur que la fonction renverrait, vous pouvez créer un corps de la fonction. Le corps d'une fonction commence par BEGIN et se termine avec les mots-clés END. Voici un exemple :

CREATE FUNCTION Addition() 
RETURNS Decimal(6,3) 
BEGIN 
 
 
END

Si vous le souhaitez, vous pouvez taper le mot clé AS avant le mot-clé BEGIN :

CREATE FUNCTION Addition()
RETURNS Decimal(6,3) 
AS 
BEGIN 
END

Entre les mots-clés BEGIN et END, qui est la section qui représente le corps de la fonction, vous pouvez définir l'affectation que doit exécuter la fonction. Après l'exécution de cette affectation, juste avant le mot-clé END, vous devez spécifier la valeur renvoyée par la fonction. Ceci est fait en tapant le mot-clé RETURN, suivi d'une expression. La formule d'échantillon est la suivante :

CREATE FUNCTION Addition() 
RETURNS Decimal(6,3) 
AS 
BEGIN 
    RETURN expression 
END

Voici un exemple

CREATE FUNCTION  GetFullName() 
RETURNS varchar(100) 
AS 
BEGIN 
       RETURN 'Doe, John' 
END

1. Dans l'Explorateur d'objets, cliquez avec le bouton droit sur RealEstate1, puis cliquez sur Nouvelle requête...
2. Pour créer une fonction, saisissez l'instruction suivante :
   

   CREATE FUNCTION CalculateWeeklySalary() RETURNS Decimal(8, 2) AS BEGIN RETURN 880.44 ; END GO

3. Pour exécuter l'instruction, sur la barre d'outils de l'éditeur SQL, cliquez sur le bouton Exécuter
4. Pour enregistrer le fichier qui contient le code de la fonction, sur la barre d'outils standard, cliquez sur le bouton Enregistrer
5. Tapez Calculate comme le nom du fichier
6. Cliquez sur le bouton Enregistrer
7. Dans l'Explorateur d'objets, développez le nœud RealEstate1, développez Programmabilité. Développez les fonctions. Et développez des fonctions scalaires. Notez la présence du nœud CalculateWeeklySalary

Après qu'une fonction ait été créée, vous pouvez utiliser la valeur qu'elle renvoie. L'aide d'une fonction est également sollicitée pour l'appeler. Pour appeler une fonction, vous devez qualifier son nom. Pour le faire, saisissez le nom de la base de données dans laquelle il a été créé, suivi par l'opérateur de période, suivi de la dbo, suivi par l'opérateur période, suivi du nom de la fonction et ses parenthèses. La formule à utiliser est la suivante :

FunctionName DatabaseName .dbo.()

Parce qu'une fonction renvoie à une valeur, vous pouvez utiliser cette valeur comme bon vous semble. Par exemple, vous pouvez utiliser PRINT ou SELECT pour afficher la valeur de la fonction dans une fenêtre de requête. Voici un exemple qui appelle la fonction Addition() ci-dessus :

PRINT Exercise.dbo.GetFullName() ;

Comme alternative, pour appeler une fonction dans l'Explorateur d'objets, cliquez avec le bouton droit de la souris sur son nom, positionnez la souris sur le Script Fonction As, SELECT To et cliquez sur New Query Editor Window.

1. Dans l'Explorateur d'objets, cliquez avec le bouton droit sur RealEstate1, puis cliquez sur Nouvelle Requête
2. Pour exécuter la fonction que nous venons de créer, exécutez l'instruction suivante :
   
   

   PRINT RealEstate1.dbo.CalculateWeeklySalary() ; GO

3. Pour spécifier un nom de colonne pour la valeur renvoyée d'une fonction, modifiez la fonction comme suit et exécutez-la :
    
   

   SELECT AS RealEstate1.dbo.CalculateWeeklySalary()AS [Weekly Salary]; GO

4. Pour enregistrer la fenêtre en cours, dans la barre d'outils, cliquez sur le bouton Enregistrer

Parce qu'une fonction dans Transact-SQL est traitée comme un objet, elle a besoin de maintenance. Certaines des actions que vous prendrez incluent de renommer, modifier ou supprimer une fonction.

Si vous créez une fonction et l'exécutez, elle est stockée dans le nœud de fonctions scalaires avec le nom que vous lui avez donné. Si vous le souhaitez, vous pouvez modifier ce nom mais conserver les fonctionnalités de la fonction.

Pour renommer une fonction, dans l'Explorateur d'objets, cliquez dessus avec le bouton droit de la souris et cliquez sur Renommer. Tapez le nouveau nom de votre choix, puis appuyez sur Entrée.

Si vous créez une fonction et vous décidez que vous n'avez plus besoin d'elle, vous pouvez la supprimer.

Pour supprimer une fonction dans l'Explorateur d'objets, localisez la fonction dans la section fonctions, cliquez dessus avec le bouton droit de la souris et cliquez sur Supprimer. La boîte de dialogue Supprimer un objet viendra. Si vous voulez supprimer la fonction, cliquez sur OK; dans le cas contraire, cliquez sur Annuler.

Pour supprimer par programmation une fonction :

• Dans une fenêtre de requête, tapez DROP FUNCTION, suivi du nom de la fonction et exécutez l'instruction
• Dans l'Explorateur d'objets, cliquez avec le bouton droit de la souris sur le nom de la fonction, positionnez la souris sur Script Fonction As, DROP To, puis cliquez sur New Query Editor Window
• Ouvrez une nouvelle fenêtre de requête associée à la base de données qui contient la fonction. Afficher l'Explorateur de modèles et développez le nœud de fonction. Faites glisser le nœud de fonction directe dans la fenêtre de requête vide

1. Dans l'Explorateur d'objets, sous le nœud de fonctions scalaires, cliquez avec le bouton droit de la souris sur dbo.CalculateWeeklySalary, puis cliquez sur Supprimer
2. Dans la boîte de dialogue Supprimer un Objet, cliquez sur OK

Comme nous l'avons déjà mentionné, dans le corps de la fonction, vous définissez ce dont la fonction est censée prendre soin. Au minimum, une fonction peut renvoyer un numéro simple, tapez sur le côté droit du mot-clé RETURN. Voici un exemple :

CREATE FUNCTION Addition() 
RETURNS int 
BEGIN 
    RETURN 1 
END

Vous pouvez également déclarer de nouvelles variables dans le corps de la fonction pour aider dans l'exercice de l'affectation. Une variable déclarée dans le corps d'une fonction est appelée une variable locale. Une fois qu'une telle variable a été déclarée, elle peut être utilisée comme n'importe quelle autre variable. Voici un exemple :

CREATE FUNCTION Addition()
RETURNS int 
BEGIN 
    DECLARE @Number1 int 
    SET @Numéro1 = 588 
    RETURN @Number1 + 1450
END

1. Dans la fenêtre de requête Calculate, modifiez le code comme suit (Notez l'ajout du schéma) :
   
   

   CREATE FUNCTION Payroll.CalculateWeeklySalary() 
   RETURNS Decimal(8, 2) 
   AS 
   BEGIN 
          DECLARE 
                  @HourlySalary Decimal(8, 2), 
                  @WeeklyHours Real, 
                  @FullName varchar(100) ; 
                  SET @HourlySalary = 24.15 ; 
                  SET @WeeklyHours = 42.50 ; 
          RETURN  @HourlySalary * @WeeklyHours 
   END ; 
   GO

2. Appuyez sur F5 pour exécuter l'instruction.
   
   
3. Pour appeler la fonction, sélectionnez et supprimez le code. Remplacez par le texte suivant :
   

   SELECT AS RealEstate1.Payroll.CalculateWeeklySalary() 
   AS [WeeklySalary]; 
   GO

4. Exécutezr le code en appuyant sur la touche F5

Afin de remplir ses missions, une fonction peut être fournie avec certaines valeurs. Lorsque vous créez une fonction, placer-la d'une autre façon au lieu d'additionner les variables locales, vous pouvez avoir besoin du code qui appelle la fonction pour fournir les valeurs nécessaires pour effectuer l'affectation. Par exemple, imaginez que vous souhaitez créer une fonction qui générerait des adresses de courriel des employés lorsqu'un utilisateur a saisi un nom et prénom. Du moment que vous créez la fonction, vous ne pouvez pas savoir ou prédire les noms des employés, y compris ceux qui n'ont pas encore été embauché encore. Dans ce cas, vous pouvez écrire la fonction entière mais fournir un ou plusieurs espaces réservés pour les valeurs qui pourraient être fournis lorsque la fonction est appelée.

Une valeur externe qui est fournie à une fonction est appelée un paramètre. Une fonction peut également prendre plusieurs paramètres. Par conséquent, lorsque vous créez une fonction, vous décidez également si votre fonction prendrait un ou plusieurs paramètres et que seraient ces paramètres, le cas échéant.

Nous avons déjà vu que le nom d'une fonction est également suivi de parenthèses. Si la fonction n'utilise pas une valeur externe, ses parenthèses peuvent être laissées vides. Si une fonction utilise une valeur externe, lorsque vous créez la fonction, vous devez spécifier le nom et le type de la valeur des paramètres. Le nom du paramètre est créé avec le symbole @, comme une variable, tel que nous l'avons vu dans la leçon précédente. Voici un exemple :

CREATE FUNCTION Addition(@Number1 Decimal(6,2))

Lorsqu'une fonction prend un paramètre, dans le corps de la fonction, vous pouvez utiliser le paramètre comme si vous connaissiez sa valeur, aussi longtemps que vous respectez ce type de valeur. Voici un exemple :

CREATE FUNCTION Addition(@Number1 Decimal(6,2)) 
RETURNS Decimal(6,2) 
BEGIN 
RETURN @Number1 + 1450 
END

Lorsque vous appelez une fonction qui prend un paramètre, vous devez fournir une valeur pour cet argument. Pour le faire, entrez la valeur du paramètre dans les parenthèses de la fonction. Voici un exemple :

Au lieu d'un seul paramètre, vous pouvez également créer une fonction qui prend plusieurs paramètres. Dans ce cas, séparez les arguments dans les parenthèses de la fonction par une virgule. Voici un exemple :

CREATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2))

Une fois de plus, dans le corps de la fonction, vous pouvez utiliser les paramètres comme si vous connaissiez déjà leur valeur. Vous pouvez également déclarer des variables locales et impliquer avec des paramètres comme bon vous semble. Voici un exemple :

CREATE FUNCTION Addition(@Number1 Decimal(6,2), 
                         @Number2 Decimal(6,2)) 
RETURNS Decimal(6,2) 
BEGIN 
    DECLARE @Result Decimal(6,2) 
    SET @Result = @Number1 + @Number2 
    RETURN @Result 
END; 
GO

Lorsque vous appelez une fonction qui prend plus d'un paramètre, dans les parenthèses de la fonction, fournissez une valeur pour chaque paramètre dans l'ordre exact qu'ils apparaissent dans les parenthèses de la fonction. Voici un exemple :

PRINT Variables1.dbo.Addition(1450, 228) ;

Vous pouvez également transmettre les noms des variables déjà déclarées et initialisées. Voici un exemple qui appelle la fonction ci-dessus :

DECLARE @Nbr1 Decimal(6,2) 
        @Nbr2 Decimal(6,2)
SET @Nbr1 = 4268.55 
SET @Nbr2 = 26.83 
SELECT @Nbr1 As First, 
       @Nbr2 As Second, 
       Variables1.dbo.Addition (@Nbr1, @Nbr2) AS result

Cela donnerait :

1. Dans l'Explorateur d'objets, sous le nœud de fonctions scalaires, cliquez avec le bouton droit de la souris sur Payroll.CalculateWeeklySalary, puis cliquez sur Supprimer
2. Dans la boîte de dialogue Supprimer un objet, cliquez sur OK
3. Pour ajouter des arguments, modifiez le code de la fonction Calculate() comme suit :
   

   CREATE FUNCTION Payroll.CalculateWeeklySalary(@WeeklyHours 
   Decimal(6,2),                         
                                          @HourlySalary SmallMoney) 
   RETURNS Decimal(8, 2) 
   AS 
   BEGIN 
       DECLARE @Weekly SmallMoney 
       SELECT  @Weekly = @WeeklyHours * @HourlySalary 
    
    
       RETURN @Weekly 
   END ; 
   GO

4. Appuyez sur la touche F5 pour créer la fonction
5. Supprimez le code dans la fenêtre et remplacez-le par le texte suivant :
   

   DECLARE @Hours Decimal(5,2), 
           @Hourly SmallMoney 
   SELECT  @Hours = 42.50 
   SELECT  @Hourly = 18.62 
   SELECT  'Hermine Singh' As [Employee Name], 
           @Hours As [Weekly Hours], 
           @Hourly As [Hourly Salary], 
           RealEstate1.Payroll.CalculateWeeklySalary (@Hours, @Hourly) 
                  AS [Weekly Salary]; 
   GO

6. Appuyez sur F5 pour exécuter l'instruction.
   
   
7. Fermez la fenêtre de requête sans enregistrer le fichier.
8. Dans l'Explorateur d'objets, sous le nœud de bases de données, cliquez avec le bouton droit de la souris sur RealEstate1, puis cliquez sur Supprimer
9. Dans la boîte de dialogue, cliquez sur OK (si vous n'êtes pas autorisé à supprimer la base de données, fermez Microsoft SQL Server, ouvrez-la et essayez à nouveau la suppression de la base de données)

Lorsqu'une fonction avec arguments est appelée, une valeur doit être transmise pour chaque argument. Voici un exemple d'une telle fonction :

USE Exercise ; 
GO 
CREATE FUNCTION CalculateTaxAmount (@Price money,  @Rate decimal(6, 2)) 
RETURNS decimal(6, 3) 
AS
BEGIN
    DECLARE @ResultVar Money
 
 
    SELECT @ResultVar = @Price * @Rate / 100 
    RETURN @ResultVar 
END 
GO

Voici des exemples de l'appel de la fonction :

USE Exercise ; 
GO 
PRINT dbo.CalculateTaxAmount(140.00, 5.75) ;
PRINT dbo.CalculateTaxAmount(195.95, 5.75) ; 
PRINT dbo.CalculateTaxAmount(250.00, 7.55) ; 
PRINT dbo.CalculateTaxAmount(125.95, 5.75) ; 
GO

Cela donnerait :

8.05 
11.27 
18.88
7.24 

Dans certains cas, si la fonction est généralement appelée avec la même valeur pour un argument, vous pouvez spécifier une valeur par défaut pour cet argument. Lorsqu'une telle fonction est appelée, vous (ou l'utilisateur) pouvez omettre la valeur de l'argument.

Pour spécifier une valeur par défaut pour un argument, entre les parenthèses de la fonction après le nom et le type de données de l'argument, tapez =, suivi de la valeur souhaitée. Voici un exemple :

USE exercise ; 
GO 
 
 
CREATE FUNCTION CalculateTaxAmount (@Price money, @Rate decimal (6, 2) = 5,75) 
RETURNS decimal(6, 2) 
AS
BEGIN
    DECLARE @ResultVar money 
 
 
    SELECT @ResultVar = @Price * @Rate / 100 
    RETURN @ResultVar
END 
GO 

Lorsque vous appelez une fonction qui a une valeur par défaut pour un argument, il est inutile de transmettre une valeur pour cet argument, même si vous pouvez, si vous le souhaitez. Lors de l'appel de la fonction, dans l'espace réservé à l'argument, tapez le mot clé DEFAULT. Voici des exemples de l'appel de la version la plus récente de notre fonction :

USE Exercise  
GO
 
PRINT dbo.CalculateTaxAmount (140.00, DEFAULT); 
PRINT dbo.CalculateTaxAmount (195.95, 5,75); 
PRINT dbo.CalculateTaxAmount (250,00, 7.55); 
PRINT dbo.CalculateTaxAmount (125.95, default) ; 
GO

1. Écrivez une fonction qui peut calculer le salaire annuel d'une tâche si une application est donnée par salaire horaire. Considérez que le salaire annuel est basé sur 40 heures par semaine. Dans votre fonction, si le salaire horaire est donné comme une valeur négative, n' effectuez pas le calcul
2. Écrivez une fonction qui peut calculer la surface d'un cercle si le rayon est donné
3. Écrivez une procédure (une fonction), qui traite d'un cylindre. Il calcule et affiche le diamètre de la base, la circonférence de la base, la superficie de la base, la zone côté, la superficie totale et le volume, étant donné le rayon et la hauteur d'un cylindre