Home

Procédures Stockées

   

Principes de base de procédures stockées

 

Introduction

 
 

Dans la Leçon 7, nous avons eu une introduction à certains types d'actions qui pourraient être effectuées sur une base de données. Ces actions ont été appelées des fonctions. Le SQL fournit un autre type d'actions appelé une procédure stockée. Si vous avez développé des applications dans certains autres langages tels que Pascal ou Visual Basic, vous êtes probablement familiarisé avec l'idée d'une procédure. Comme une fonction, une procédure stockée est utilisée pour exécuter une action sur une base de données.

Practical LearningFormation pratique : Présentation des procédures stockées

  1. Démarrez Microsoft SQL Server Management Studio et connectez-vous à votre serveur
  2. Dans le menu principal, cliquez sur File-> New-> Query With Current Connection
  3. Pour créer une nouvelle base de données, copiez et collez le code suivant dans la fenêtre de requête :
    -- =============================================  
    -- Database: WattsALoan  
    -- =============================================  
    USE master  
    GO  
    -- Drop the database if it already exists  
    IF  EXISTS (  	
            SELECT name   	
                    FROM sys.databases   	
                    WHERE name = N'WattsALoan' 
    )  
    DROP DATABASE WattsALoan  
    GO    
     
     
    CREATE DATABASE WattsALoan  
    GO  
    -- =========================================  
    -- Table: Employees  
    -- =========================================  
    USE WattsALoan  
    GO    
    IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL    
     DROP TABLE dbo.Employees  
    GO    
     
     
    CREATE TABLE dbo.Employees  
    (      
        EmployeeID int identity(1,1) NOT NULL,      
        EmployeeNumber nchar(10) NULL,      
        FirstName nvarchar(20) NULL,      
        LastName nvarchar(10),      
        FullName AS ((LastName+ ', N') + FirstName),      
        Title nvarchar(100),      
        HourlySalary money,      
        Username nvarchar(20),      
        Password nvarchar(20),      
        CONSTRAINT PK_Employees PRIMARY KEY(EmployeeID)  
    )  
    GO  
    INSERT INTO dbo.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
    VALUES(N'293747', N'Jeanne', N'Tryler', N'Accounts Manager', 22.24);  
    GO  
    INSERT INTO dbo.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
    VALUES(N'492947', N'Helene', N'Gustman', N'Accounts Representative', 14.55);  
    GO  
    INSERT INTO dbo.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
    VALUES(N'804685', N'Ernest', N'Thomas', N'Accounts Representative', 12.75);  
    GO  
    -- =========================================  
    -- Table: LoanTypes  
    -- =========================================  
    USE WattsALoan  
    GO    
     
     
    IF OBJECT_ID(N'dbo.LoanTypes', N'U') IS NOT NULL    
     DROP TABLE dbo.LoanTypes  
    GO    
     
     
    CREATE TABLE dbo.LoanTypes  
    (      
        LoanTypeID int identity(1,1) NOT NULL,       
        LoanType nvarchar(50) NOT NULL,       
        CONSTRAINT PK_LoanTypes PRIMARY KEY(LoanTypeID)  
    );  
    GO  
    INSERT INTO LoanTypes(LoanType) VALUES(N'Personal Loan');  
    GO  
    INSERT INTO LoanTypes(LoanType) VALUES(N'Car Financing');  
    GO  
    INSERT INTO LoanTypes(LoanType) VALUES(N'Credit Card');  
    GO  
    INSERT INTO LoanTypes(LoanType) VALUES(N'Furniture Loan');  
    GO  
    -- =========================================  
    -- Table: Customers  
    -- =========================================  
    USE WattsALoan  
    GO    
     
    IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL    
     DROP TABLE dbo.Customers  
    GO    
     
    CREATE TABLE dbo.Customers  
    (      
        CustomerID int identity(1,1) NOT NULL,      
        DateCreated datetime2 NULL,      
        FullName nvarchar(50) NOT NULL,      
        BillingAddress nvarchar(100),      
        BillingCity nvarchar(50),      
        BillingState nvarchar(50),      
        BillingZIPCide nvarchar(10),      
        EmailAddress nvarchar(100),      
        CONSTRAINT PK_Customers PRIMARY KEY(CustomerID)  
    )  
    GO  
    INSERT INTO Customers(DateCreated, FullName,      
        BillingAddress, BillingCity, BillingState,      
        BillingZIPCide, EmailAddress)  
    VALUES(N'2/26/2004', N'Julius Ramse',         
           '927 Feuler Ave', N'Silver Spring',          
           'MD', N'20904', N'ramses1990@netscape.net');  
    GO  
    INSERT INTO Customers(DateCreated, FullName,      
        BillingAddress, BillingCity, BillingState,  
            BillingZIPCide)  
    VALUES(N'06/22/2006', N'Gertrude Vaillant',         
           '10055 Larsenic Rd', N'Takoma Park',          
           'MD', N'20910');  
    GO  
    INSERT INTO Customers(DateCreated, FullName,      
        BillingAddress,	BillingCity, BillingState,  
            BillingZIPCide, EmailAddress)  
    VALUES(N'12/3/2004', N'James Barrouch',         
           '4204 Fallon Drive', N'Silver Spring',         
           'MD', N'20906', N'barrouchj@hotmail.com');  
    GO  
    INSERT INTO Customers(DateCreated, FullName,      
        BillingAddress,	BillingCity, BillingState,  
            BillingZIPCide)  
    VALUES(N'08/02/2006', N'Christine Rougher',         
           '825 Manning Street', N'Alexandria',          
           'VA', N'22231');  
    GO  
    INSERT INTO Customers(DateCreated, FullName,      
        BillingAddress,	BillingCity, BillingState,  
            BillingZIPCide, EmailAddress)  
    VALUES(N'10/08/2006', N'Patrick Heller',         
           '2480 Clarington Drive NW', N'Washington',          
           'DC', N'20006', N'hellerp@yahooo.com');  
    GO  
    -- =========================================  
    -- Table: LoanAllocation  
    -- =========================================  
    USE WattsALoan  
    GO    
     
    IF OBJECT_ID(N'dbo.LoanAllocations', N'U') IS NOT NULL    
    DROP TABLE dbo.LoanAllocations  
    GO    
     
     
    CREATE TABLE dbo.LoanAllocations  
    (      
        LoanAllocationID int identity(1,1) NOT NULL,      
        DatePrepared datetime2 NOT NULL,       
        EmployeeID int NULL  	
                   CONSTRAINT FK_LoanPreparer  	
                   FOREIGN KEY REFERENCES Employees(EmployeeID),      
        CustomerID int NOT NULL  
                   CONSTRAINT FK_LoanReceiver  	
                   FOREIGN KEY REFERENCES Customers(CustomerID),     
        AccountNumber nchar(10),      
        LoanTypeID int NOT NULL  
                  CONSTRAINT FK_LoanTypes  	
                  FOREIGN KEY REFERENCES LoanTypes(LoanTypeID),      
        LoanAmount money NOT NULL,      
        InterestRate decimal(6,2) NOT NULL,      
        Periods decimal(6,2) NOT NULL,      
        InterestAmount AS ((LoanAmount*(InterestRate/(100)))*(Periods/(12))),      
        FutureValue AS (LoanAmount+(LoanAmount*(InterestRate/(100)))*(Periods/(12))),      
        MonthlyPayment AS ((LoanAmount+(LoanAmount*(InterestRate/(100)))*(Periods/(12)))/Periods),      
        Notes Text,      
        CONSTRAINT PK_LoanAllocations PRIMARY KEY(LoanAllocationID)  
    )  
    GO  
    INSERT INTO LoanAllocations(DatePrepared, EmployeeID, 
            CustomerID, AccountNumber, LoanTypeID, LoanAmount,      
        InterestRate, Periods, Notes)  
    VALUES(N'2/26/2004', 2, 1, N'9171394', 4, 6500.00, 12.65, 36,          
           'The loan will be delivered by our furniture business partner Helios Furnian');  
    GO  
    INSERT INTO LoanAllocations(DatePrepared, EmployeeID, 
            CustomerID, AccountNumber, LoanTypeID, LoanAmount,      
        InterestRate, Periods, Notes)  
    VALUES(N'06/22/2007', 2, 2, N'8628064', 2, 16500.00, 10.20, 60,          
           'For this car loan, our partner Arlington Honda will process and deliver the car.');  
    GO  
    INSERT INTO LoanAllocations(DatePrepared, EmployeeID, 
            CustomerID, AccountNumber, LoanTypeID, LoanAmount,      
        InterestRate, Periods, Notes)  
    VALUES(N'12/3/2006', 1, 3, N'8468364', 3, 500.00, 18.65, 48,          
           'This is a regular credit card.');  
    GO  
    INSERT INTO LoanAllocations(DatePrepared, EmployeeID, 
            CustomerID, AccountNumber, LoanTypeID, LoanAmount,      
        InterestRate, Periods, Notes)  
    VALUES(N'08/02/2006', 3, 4, N'2483047', 1, 3500.00, 12.74, 36,          
           'This is personal/cash loan allocated to a customer who walked in the store and requested it.');  
    GO  
    INSERT INTO LoanAllocations(DatePrepared, EmployeeID,  
            CustomerID, AccountNumber, LoanTypeID, LoanAmount,      
        InterestRate, Periods, Notes)  
    VALUES(N'10/08/2006', 2, 5, N'1311804', 4, 22748.36, 12.28, 60,          
           'This is a regular car financing loan');  
    GO  
    -- =========================================  
    -- Table: Payments 
    -- =========================================  
    USE WattsALoan  
    GO    
     
    IF OBJECT_ID(N'dbo.Payments', N'U') IS NOT NULL    
     DROP TABLE dbo.Payments  
    GO    
     
    CREATE TABLE dbo.Payments  
    (      
         PaymentID int identity(1, 1) NOT NULL,      
         PaymentDate datetime2 NOT NULL,       
         EmployeeID int NULL  	
                    CONSTRAINT FK_Employees  	
                    FOREIGN KEY REFERENCES Employees(EmployeeID),      
         LoanAllocationID int NOT NULL  	
                    CONSTRAINT FK_LoanAllocations  	
                    FOREIGN KEY REFERENCES LoanAllocations(LoanAllocationID),      
         PaymentAmount money NOT NULL,      
         Balance money,      
         Notes Text,      
         CONSTRAINT PK_Payments PRIMARY KEY(PaymentID) 
     )  
    GO
  4. Pour exécuter le code, appuyez sur la touche F5
  5. Dans l'Explorateur d'objets, développez le nœud de bases de données si nécessaire et développez WattsALoan
  6. Cliquez sur le schéma de base de données
  7. Lorsque le message s'affiche, lisez-le et cliquez sur Oui
  8. Cliquez avec le bouton droit de la souris sur schéma de base de données, puis cliquez sur Nouveau schéma de base de données...
  9. Dans la boîte de dialogue, double-cliquez sur chaque table et, lorsque toutes les tables ont été ajoutées, cliquez sur Fermer

  10. Enregistrez le schéma comme dgmWattsALoan et fermez-le

Création d'une procédure stockée

Pour créer une procédure :

  • Dans l'Explorateur d'objets, développez la base de données pour laquelle vous souhaitez créer la procédure, développez le nœud de programmabilité, cliquez avec le bouton droit sur Stored Procedure et cliquez sur New Stored Procedure... Une fenêtre de requête avec une syntaxe squelette s'affiche. Vous pouvez alors modifier ce code à l'aide des techniques que nous apprendrons dans cette leçon.
  • Ouvrez une fenêtre de requête vide associée à la base de données pour laquelle vous souhaitez créer la procédure stockée et affichez l'Explorateur de modèles. Dans l'Explorateur de modèles, développez le nœud de procédure Store. La Procédure stockée permet de créer, de glisser et de déposer dans la fenêtre de requête
  • Ouvrez une fenêtre de requête vide associée à la base de données pour laquelle vous souhaitez créer la procédure stockée et entrez le code nécessaire

Dans SQL, pour créer une procédure, vous démarrez avec l'expression CREATE PROCEDURE. Vous pouvez également utiliser CREATE PROC. Les deux expressions produisent le même résultat. Tout comme dans votre base de données, vous devez nommer votre procédure :

  • Le nom de la procédure peut être toute chaîne qui suit les règles que nous avons examinées pour nommer les fonctions
  • S'abstenir en commençant le nom d'une procédure sp_ car il pourrait entrer en conflit avec certaines des procédures stockées qui sont déjà livrées avec Microsoft SQL Server.

Après le nom de la procédure, tapez le mot-clé AS. La section, le groupe de mots, ou un groupe de lignes après le mot-clé AS est appelé le corps de la procédure. Il énonce ce que vous voulez que la procédure fasse ou ce que vous souhaitez produire.

Sur cette base, la syntaxe la plus simple de la création d'une procédure est la suivante :

CREATE PROCEDURE ProcedureName 
AS
Body of the Procedure  

Il est important de garder à l'esprit qu'il existe de nombreuses autres questions liées à la création d'une procédure, mais pour l'instant, nous allons examiner cette syntaxe.

Après avoir créé la procédure, vous devez la stocker sous la forme d'un objet dans votre base de données. Pour le faire, dans la barre d'outils de l'Editeur SQL, vous pouvez cliquer sur le bouton Execute . Si le code de la procédure est bon, il serait créé et un nouveau nœud pour son nom pourrait être ajouté à la section des procédures stockées de la base de données.

Gestion des procédures

 

Modification d'une procédure

Sous la forme d'un objet de base de données SQL Server régulière, vous pouvez modifier une procédure stockée sans recréer. Pour le faire :

  • Dans l'Explorateur d'objets, vous pouvez cliquez avec le bouton droit de la souris sur la procédure et cliquez sur Modifier
  • Dans l'Explorateur d'objets, vous pouvez cliquez avec le bouton droit de la souris sur la procédure, positionnez la souris sur le Script Stored Procedure As-> ALTER To-> New Query Editor Window
  • Ouvrez une fenêtre de requête vide associée à la base de données contenant la procédure stockée. Dans l'Explorateur de modèles, développez la procédure stockée. Faites glisser le nœud de procédures stockées et déposez-les dans la fenêtre de requête vide

Dans chaque cas, un code squelette serait produit pour vous.Vous pouvez ensuite modifier pour créer une nouvelle version de votre procédure stockée. Après avoir modifié le code, vous pouvez exécuter l'instruction SQL pour mettre à jour la procédure stockée.

Dans SQL, la formule de base pour modifier une procédure stockée est :

ALTER PROCEDURE ProcedureName 
AS
Body of Procedure

Une procédure de suppression

L'une des plus grandes caractéristiques d'une procédure stockée est qu'elle est considérée comme un objet dans son propre droit. Par conséquent, après l'avoir créée, si vous n'en avez plus besoin, vous pouvez vous en débarrasser.

Il existe différents types de procédures stockées, dont certaines sont considérées comme temporaires. Ces types de procédures se suppriment eux-mêmes lorsque ce n'est plus nécessaire, comme lorsque la personne qui a créé la procédure stockée se déconnecte de la base de données ou arrête l'ordinateur. Sinon, pour supprimer une procédure, vous pouvez utiliser l'Explorateur d'objets ou SQL. Comme nous l'avons vu avec des tables, même si vous créez une procédure à l'aide de l'Explorateur d'objets, vous pouvez la supprimer en utilisant SQL et vice versa.

Pour supprimer une procédure dans l'Explorateur d'objets, après avoir élargi sa base de données, ses fonctionnalités de programmation et sa procédure stockée de nœuds, vous pouvez cliquez avec le bouton droit de la souris sur la procédure stockée, puis cliquez sur Supprimer. Vous pouvez également cliquer dessus dans l'Explorateur d'objets pour la sélectionner et appuyez sur la touche Suppr. La boîte de dialogue Supprimer un objet vous permettra de prendre une décision.

Pour supprimer une procédure dans SQL, la syntaxe à utiliser est la suivante :

DROP PROCEDURE ProcedureName

Bien entendu, vous devez vous assurer que vous êtes dans la base de données de droite ainsi que la ProcedureName existe.

Exploration des procédures

Introduction

La procédure la plus simple, que vous pouvez écrire comprendrait probablement de la sélection des colonnes d'une table. Cela se fait avec l'opérateur SELECT et en appliquant les techniques que nous avons examinées pour l'analyse des données. Par exemple, pour créer une procédure stockée qui tiendra une liste des étudiants d'une table nommée Students, vous créez la procédure comme suit :

CREATE PROCEDURE GetStudentIdentification 
AS 
BEGIN 
    SELECT FirstName, LastName, DateOfBirth, Gender
    FROM Students 
END 
GO

Outre les opérations SELECT, dans le cadre d'une procédure stockée, vous pouvez effectuer des opérations de base de données que nous avons appliquées jusqu'à présent. Celles-ci incluent la création et la gestion des enregistrements, etc..

Practical LearningFormation pratique : Création d'une procédure stockée

  1. Assurez-vous que vous disposez de la base de données BNS Yugo créée dans le Lesson13. Si vous ne l'avez pas créé, faites-le maintenant.
    Dans l'Explorateur d'objets, cliquez avec le bouton droit sur YugoNationalBank, puis cliquez sur Nouvelle requête
  2. Pour lancer une procédure stockée, tapez ce qui suit :
    USE YugoNationalBank ; 
    GO
    --=============================================
    --Author :      FunctionX
    --Create date : Friday, May 25, 2007
    --Description : This stored procedure assigns a
    --              default password to each employee.
    --============================================= 
     
     
    CREATE PROCEDURE AssignDefaultPassword 
    AS 
    BEGIN 
            UPDATE dbo.Employees 
            SET PASSWORD = N 'Password1' FROM dbo.Employees ; 
    END 
    GO
  3. Pour créer la procédure stockée, appuyez sur la touche F5

L'exécution d'une procédure

Pour obtenir les résultats de la création d'une procédure stockée, vous devez exécuter (en d'autres termes, pour utiliser une procédure stockée, vous devez l'appeler). Pour exécuter une procédure, vous utilisez leMot-clé EXECUTE suivi du nom de la procédure. Bien qu'il existe d'autres problèmes liés à l'exécution d'une procédure, pour l'instant, nous allons examiner que la syntaxe la plus simple pour appeler une procédure est :

EXECUTE ProcedureName

Au lieu de EXECUTE, vous pouvez également utiliser le mot-clé EXEC :

EXEC ProcedureName

Par exemple, si vous avez une procédure nommée GetStudentIdentification, pour l'exécuter, vous taperez :

EXECUTE GetStudentIdentification

Vous pouvez également faire précéder le nom de la procédure avec son schéma, tel que dbo. Voici un exemple :

EXECUTE dbo.GetStudentIdentification ;

Vous pouvez également faire précéder le nom du schéma avec le nom de la base de données. Voici un exemple :

EXECUTE ROSH.dbo.GetStudentIdentification ;

Practical LearningFormation pratique : L'exécution d'une procédure stockée

  1. Supprimez le contenu de la fenêtre de requête et remplacez-le par le texte suivant :
    EXECUTE AssignDefaultPassword ; 
    GO
  2. Pour exécuter la procédure stockée, appuyez sur la touche F5

L'utilisation des expressions et des fonctions

L'un des avantages de l'utilisation d'une procédure stockée est que non seulement elle peut produire la même expression comme nous l'avons vu au cours de l'analyse, mais aussi elle peut stocker une telle expression pour être rappelée à tout moment sans avoir à la réécrire (l'expression). Sur cette base, vous pouvez créer une expression qui combine un premier et un nom pour produire et stocker un nom complet. Voici un exemple :

CREATE PROCEDURE GetStudentIdentification 
AS 
BEGIN 
    SELECT FullName = FirstName + ' ' + LastName, 
           DateOfBirth, Gender 
    FROM Students 
END 
GO

Une procédure stockée peut également appeler une fonction dans son corps. Pour le faire, suivez les mêmes règles que nous avons examinées pour appeler des fonctions au cours de l'analyse de données. Voici un exemple d'une procédure stockée qui appelle une fonction :

USE ROSH ; 
GO 
CREATE PROCEDURE GetStudentsAges 
AS 
BEGIN 
    SELECT FullName = FirstName + N ' ' + LastName, 
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age, 
           Gender 
    FROM Students 
END 
GO

Voici un exemple de l'exécution de la procédure :

Procedure

Practical LearningFormation pratique : Appeler une fonction dans une procédure stockée

  1. Supprimez le contenu de la fenêtre de requête
  2. Pour démarrer une nouvelle procédure stockée, tapez ce qui suit :
    USE YugoNationalBank ; 
    GO
     
     
    --=============================================
    --Author : FunctionX
    --Create date : Friday, May 25, 2007
    --Description : This stored procedure creates a
    --              username for each employee.
    --              It also assigns an email to the employee.
    --============================================= 
    CREATE PROCEDURE CreateUsername 
    AS 
    BEGIN 
            UPDATE dbo.Employees 
            SET Username = LOWER (LEFT (FirstName, 1) + LEFT (LastName, 5)) 
                    FROM dbo.Employees ; 
            UPDATE dbo.Employees 
            SET EmailAddress = LOWER (LEFT(FirstName, 1) + LEFT(LastName, 5)) + 
                                                N'@yugonationalbank.com ' 
                                                FROM dbo.Employees ; 
    END 
    GO
  3. Pour créer la procédure stockée, appuyez sur la touche F5
  4. Supprimez le contenu de la fenêtre de requête et remplacez-la par le texte suivant :
    EXECUTE CreateUsername ; 
    GO
  5. Pour exécuter la procédure stockée, appuyez sur la touche F5

Paramètres et arguments

Introduction

Toutes les procédures stockées que nous avons créées et utilisées jusqu'à présent supposées que les valeurs dont ils avaient besoin étaient déjà dans une table de la base de données. Dans certains cas, il se peut que vous deviez créer une procédure stockée qui implique des valeurs qui ne font pas partie de la base de données. Sur un tel scénario, pour la procédure de transporter son affectation, vous fournirez une ou plusieurs valeurs.

Une valeur externe qui est fournie pour une procédure stockée est appelée un paramètre. Lorsque vous créez une procédure stockée, vous devez également créer le paramètre si vous le jugez nécessaire. Lorsque la création d'une procédure est équipée d'un paramètre, il est dit que la procédure stockée accepte un argument. Une procédure stockée peut également prendre plusieurs arguments.

Lorsque vous exécutez une procédure stockée qui prend un ou plusieurs arguments, vous devez fournir une valeur pour chaque argument. Dans ce cas, on dit que vous passez une valeur de l'argument. Il y a des cas lorsque vous n'avez pas à fournir un argument.

Arguments de passage

Pour créer une procédure stockée qui prend un argument, tapez la formule CREATE PROCEDURE ou CREATE PROC suivie du nom de la procédure, puis tapez le nom de l'argument commençant par @. Le paramètre est créé comme une colonne d'une table. Autrement dit, un paramètre doit avoir un nom, un type de données et une longueur facultative. Voici la syntaxe que vous utiliserez :

CREATE PROCEDURE ProcedureName 
@ParameterName DataType 
AS
Body of the Procedure

Lors de la mise en oeuvre de la procédure stockée, vous pouvez définir ce que vous voulez faire avec les paramètres, dans le corps de la procédure. Une façon dont vous pouvez utiliser un paramètre consiste à exécuter une requête dont l'utilisateur fournirait le coefficient. Par exemple, imaginez que vous souhaitez créer une procédure qui, dès qu'elle est exécutée, pourrait être fournie avec un sexe, puis elle affichera la liste des élèves de ce genre. Étant donné que vous souhaitez que l'utilisateur spécifie le sexe des étudiants à afficher, vous pouvez créer une procédure stockée qui reçoit le sexe. Voici un exemple :

CREATE PROC GetListOfStudentsByGender 
       @Gdr NVARCHAR(12) 
AS 
SELECT FirstName, LastName, 
       DateOfBirth, HomePhone, Gender 
FROM   Students 
WHERE  Gender = @ Gdr

Practical LearningFormation pratique : Création d'une procédure stockée

  1. Supprimez le contenu de la fenêtre de requête et, pour transmettre des arguments à une procédure stockée, tapez la commande suivante dans la fenêtre :
    USE WattsALoan;  
    GO    
     
     
    CREATE PROCEDURE SpecifyCurrentBalance      
        @PmtDate datetime2,      
        @EmplID int,      
        @LaID int,      
        @PmtAmt money  
    AS  
    BEGIN      
        -- Get the amount that was lent to the customer      
        DECLARE @AmountOfLoan money;      
        SET	@AmountOfLoan = (SELECT las.FutureValue                 
                              FROM LoanAllocations las                           
                              WHERE (las.LoanAllocationID = @LaID));        
        -- If the customer had already made at least one payment,      
        -- get the current balance of the customer's account      
        DECLARE @CurrentBalance money;      
        SET     @CurrentBalance = (SELECT MIN(pay.Balance)                                 
                                   FROM Payments pay                                 
                                   WHERE (pay.LoanAllocationID = @LaID));        
     
     
        -- If the customer has never made a payment (yet),      
        -- to specify the balance, subtract the current payment      
        -- from the original amount of the loan      
        IF      @CurrentBalance IS NULL  
           BEGIN  	
               INSERT INTO Payments(PaymentDate, EmployeeID,  	 
                                   LoanAllocationID, PaymentAmount, Balance)  
               VALUES(@PmtDate, @EmplID, @LaID, @PmtAmt,  	
                          @AmountOfLoan - @PmtAmt);  
           END      
        -- If the customer had already at least one payment,      
        -- subtract the current payment from the previous balance      
        ELSE 
           BEGIN  	
               INSERT INTO Payments(PaymentDate, EmployeeID,  	
                                   LoanAllocationID, PaymentAmount, Balance)  
               VALUES(@PmtDate, @EmplID, @LaID,  		 
                          @PmtAmt, @CurrentBalance - @PmtAmt); 
           END  
    END  
    GO
  2. Pour créer la procédure stockée, appuyez sur la touche F5

L'exécution d'une procédure stockée argumentative

Comme je l'ai mentionné déjà, lors de l'exécution d'une procédure stockée qui prend un paramètre, assurez-vous que vous indiquez une valeur pour le paramètre. La syntaxe utilisée est la suivante :

EXEC ProcedureName ParameterValue

Si le paramètre est Boolean ou numérique, veillez à ce que vous fournissez la valeur 0 ou pour une valeur booléenne ou un autre numéro pour le type numérique. Si le paramètre est un caractère ou une chaîne, saisissez sa valeur unique entre-guillemets. Voici un exemple :

EXEC ROSH.dbo.GetListOfStudentsByGender N'Male ' ;

Voici un exemple d'exécution :

Procedure

Notez que nous pourrions avoir omis d' inclure la colonne de genre dans l'instruction puisqu'elle pourrait être implicite à l'utilisateur.

Un autre type de procédure stockée peut être fait à prendre plus d'un paramètre. Dans ce cas, créez les paramètres dans la section avant le mot clé AS, séparé par une virgule. La syntaxe que vous utiliseriez est la suivante :

CREATE PROCEDURE ProcedureName 
@ParameterName1 DataType, @ParameterName2 DataType, @ParameterName_n DataType 
AS
Body of the Procedure  

Voici un exemple :

USE ROSH ; 
GO 
CREATE PROCEDURE IdentifyStudentsByState 
        @Gdr type nvarchar (20), 
        @StateOrProvince char(2) 
AS 
BEGIN 
    SELECT FullName = LastName + ', N' + FirstName, 
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age, 
           Gender 
    FROM Students 
    WHERE (Gender = @Gdr) AND (State = @StateOrProvince) 
END 
GO

Lorsque vous appelez une procédure stockée qui prend plus d'un paramètre, vous devez toujours fournir une valeur pour chaque paramètre, mais vous avez deux possibilités. La technique la plus simple consiste à fournir une valeur pour chaque paramètre dans l'ordre exact qu'ils apparaissent dans la procédure stockée. Voici un exemple :

USE ROSH ; 
GO 
EXEC ROSH.dbo.IdentifyStudentsByState 'Fémale', N'MD ' ; 
GO

Cela donnerait :

Procedure

Alternativement, vous pouvez fournir la valeur de chaque paramètre dans l'ordre de votre choix. Examinez la procédure suivante qui prend 3 arguments :

USE ROSH ; 
GO 
CREATE PROCEDURE IdentifySomeStudents 
        @Gdr nvarchar (20), 
        @StateOrProvince nchar (2), 
        @HomeStatus bit 
AS 
BEGIN 
    SELECT FullName = LastName + N ',' + FirstName, 
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age, 
           Gender 
    FROM Students 
    WHERE (Gender = @Gdr) AND 
          (@StateOrProvince) AND 
          (SPHome = @HomeStatus) 
END 
GO

En appelant ce type de procédure, vous pouvez taper le nom de chaque paramètre et lui attribuer la valeur correspondante. Voici un exemple :

USE ROSH ; 
GO 
EXEC IdentifySomeStudents @ HomeStatus = 1, @StateOrProvince = N 'MD', @Gdr = N 'Female' ; 
GO

Voici un exemple de l'exécution de la procédure :

Procedure

Practical LearningFormation pratique : L'exécution d'une procédure argumentative

  1. Supprimez le contenu de la fenêtre de code et, pour créer une procédure stockée, tapez ce qui suit dans la fenêtre :
    USE WattsALoan ; 
    GO 
    EXECUTE SpecifyCurrentBalance 25/03/2004», 2, 1, 249.08 ; 
    GO 
    EXECUTE SpecifyCurrentBalance ' 01/30/2006 ', 2, 5, 611.93 ; 
    GO 
    EXECUTE SpecifyCurrentBalance 20/04/2004 ', 1, 1, 249.08 ; 
    GO 
    EXECUTE SpecifyCurrentBalance 28/10/2006 ', 2, 4, 134.38 ; 
    GO
  2. Pour exécuter, appuyez sur la touche F5

Arguments par défaut

Imaginez que vous créez une base de données pour un magasin et une table qui contient la liste des objets vendus dans le magasin :

Supposez que vous avez rempli la table avec quelques éléments comme suit :

ItemNumber ItemCategoryID ItemName ItemSize UnitPrice
264850 2 Long-Sleeve Jersey Dress Petite 39,95
930405 4 Solid Crewneck Tee Moyen 12,95
293004 1 Cotton Comfort Open Bottom Pant XLarge 17.85
924515 1 Hooded Full-Zip Sweatshirt S forfait
405945 3 Plaid PinPoint Dress Shirt 22 35-36 35.85
294936 2 Cool-Dry Soft Cup BRA 36 D 15.55
294545 2 Ladies Hooded Sweatshirt Medium 45.75
820465 2 Cotton Knit Blazer M 295.95
294694 2 Denim Blazer - Natural Brown Large 75.85
924094 3 Texture-Striped Pleated Khaki Pants 44 x 30 32.85
359405 3 Iron-Free Pleated Khaki Pants 32 x 32 39,95
192004 3 Sunglasses 15.85

Imaginez que vous souhaitez créer un mécanisme de calcul du prix d'un élément après qu'une remise y a été appliquée. Une telle procédure peut être créée comme suit :

CREATE PROC CalculateNetPrice 
@discount DECIMAL 
AS 
SELECT ItemName, UnitPrice-(UnitPrice * @discount/100) 
FROM StoreItems

Cela peut être exécuté comme suit :

Si vous envisagez de créer une procédure stockée qui prend un argument et savoir que l'argument aura probablement la même valeur la plupart du temps, vous pouvez fournir cette valeur comme paramètre mais laisser un espace pour d'autres valeurs de cet argument. Une valeur donnée à un argument est appelée par défaut. Cela implique que, lorsque l'utilisateur appelle cette procédure stockée, si l'utilisateur ne fournit pas une valeur pour l'argument, la valeur par défaut serait utilisée.

Pour créer une procédure stockée qui prend un argument qui comporte une valeur par défaut, après la déclaration de la valeur, sur son côté droit, tapez = suivi par la valeur souhaitée. Voici un exemple appliqué à la base de données ci-dessus :

CREATE PROC CalculateDiscountedPrice 
@discount décimal = 10,00 
AS 
    SELECT ItemName, UnitPrice-(UnitPrice * @discount/100) 
    FROM StoreItems ; 
GO

Lors de l'exécution d'une procédure stockée qui prend un argument par défaut, vous n'avez pas à fournir une valeur pour l'argument, si la valeur par défaut vous convient. Sur cette base, la procédure stockée ci-dessus peut être appelée comme suit :

Si la valeur par défaut ne s'applique pas à votre calcul actuel, vous pouvez fournir une valeur pour l'argument. Voici un exemple :

En utilisant cette même approche, vous pouvez créer une procédure stockée qui prend plusieurs arguments avec les valeurs par défaut. Pour fournir une valeur par défaut pour chaque argument, après sa déclaration, entrez la valeur souhaitée pour son côté droit. Voici un exemple d'une procédure stockée qui prend deux arguments, chacun avec une valeur par défaut :

CREATE PROC CalculateSalePrice 
@Discount decimal = 20,00, 
@TaxRate decimal = 7,75 
AS 
SELECT ItemName AS [Item Description], 
       UnitPrice As [Marked Price], 
       UnitPrice * @Discount/100 As [Discount Amt], 
       UnitPrice-(UnitPrice * @Discount/100) As [After Discount], 
       UnitPrice* @TaxRate/100 As [Tax Amount], 
       (UnitPrice * @TaxRate/100) + UnitPrice-
       (UnitPrice * @Discount/100) + (@TaxRate/100) As [Net Price] 
FROM StoreItems ; 
GO

Voici un exemple de l'exécution de la procédure :

Lors de l'appel d'une procédure stockée qui prend plusieurs arguments et tous les arguments ayant des valeurs par défaut, vous n'avez pas besoin de fournir une valeur pour chaque argument, vous pouvez fournir une valeur pour seulement un ou certains de ces arguments. La procédure ci-dessus peut être appelée avec un argument comme suit :

EXEC CalculateSalePrice2 55,00

Dans ce cas, l' (les) autre (s) argument (s) utiliserait e(nt) leur valeur par défaut.

Nous avons vu que, lors de l'appel d'une procédure stockée qui prend plusieurs arguments, vous n'aviez pas à fournir les valeurs des arguments dans l'ordre exact qu'ils apparaissaient dans la procédure, vous deviez simplement taper le nom de chaque argument et lui attribuer la valeur souhaitée. De la même façon, si une procédure stockée accepte plusieurs arguments et certains de ces arguments ont une valeur par défaut, lors de l'appel, vous pouvez fournir les valeurs dans l'ordre de votre choix, en tapant le nom de chaque argument et affecter la valeur souhaitée. Sur cette base, la procédure stockée ci-dessus peut être appelée avec uniquement la valeur du deuxième argument comme suit :

EXEC CalculateSalePrice2 @ TaxRate = 8.55

Dans ce cas, le premier argument pourrait utiliser sa valeur par défaut.

Practical LearningFormation pratique : Utilisation des arguments par défaut

  1. Supprimez le contenu de la fenêtre de requête
  2. Pour créer une nouvelle version pour une procédure stockée que nous avons utilisée précédemment, tapez ce qui suit dans la fenêtre :
    USE WattsALoan;  
    GO  
    DROP PROCEDURE SpecifyCurrentBalance; 
     GO  
    CREATE PROCEDURE SpecifyCurrentBalance  
            @PmtDate datetime2,  
            @EmplID int,  	
            @LaID int,      
            @PmtAmt money,  
            @Comments Text = N''  
    AS  
    BEGIN  
            -- Get the amount that was lent to the customer  
            DECLARE @AmountOfLoan money;  
            SET	@AmountOfLoan = (SELECT las.FutureValue                           
                                  FROM LoanAllocations las                                
                                  WHERE (las.LoanAllocationID = @LaID));    
            -- If the customer had already made at least one payment,  
            -- get the current balance of the customer's account  
            DECLARE @CurrentBalance money; 
            SET     @CurrentBalance = (SELECT MIN(pay.Balance)                             
                                       FROM Payments pay                                   
                                       WHERE (pay.LoanAllocationID = @LaID));   
            -- If the customer has never made a payment (yet),  
            -- to specify the balance, subtract the current payment  
            -- from the original amount of the loan  
            IF      @CurrentBalance IS NULL  
            BEGIN  		
                    INSERT INTO Payments(PaymentDate, EmployeeID,  				
                                     LoanAllocationID, PaymentAmount,                       	
                                     Balance, Notes)  		
                    VALUES(@PmtDate, @EmplID, @LaID, @PmtAmt,  		       
                           @AmountOfLoan - @PmtAmt, @Comments);  
            END  	
            -- If the customer had already at least one payment,  
            -- subtract the current payment from the previous balance  	
            ELSE  	
            BEGIN  
                     INSERT INTO Payments(PaymentDate, EmployeeID,  			
                                     LoanAllocationID, PaymentAmount,  				 
                                      Balance, Notes)  		
                     VALUES(@PmtDate, @EmplID, @LaID,  		 
                            @PmtAmt, @CurrentBalance - @PmtAmt, @Comments);  	
            END 
    END 
    GO
  3. Pour créer la procédure stockée, appuyez sur la touche F5
  4. Supprimez le contenu de la fenêtre de requête
  5. Pour utiliser la procédure stockée, tapez ce qui suit :
    USE WattsALoan;  
    GO    
     
     
    EXECUTE SpecifyCurrentBalance '07/15/2004', 3, 1, 498.16,    
      'The customer sent a double-payment to cover this and last month';  
    GO  
    EXECUTE SpecifyCurrentBalance '01/26/2007', 1, 3, 50;  
    GO  
    EXECUTE SpecifyCurrentBalance '08/26/2004', 2, 1, 249.08;  
    GO  
    EXECUTE SpecifyCurrentBalance '02/28/2006', 3, 5, 611.93;  
    GO  
    EXECUTE SpecifyCurrentBalance '10/24/2007', 2, 2, 415.25;  
    GO  
    EXECUTE SpecifyCurrentBalance '05/30/2004', 1, 1, 249.08;  
    GO  
    EXECUTE SpecifyCurrentBalance '02/22/2007', 2, 3, 20;  
    GO  
    EXECUTE SpecifyCurrentBalance '03/23/2006', 2, 5, 611.93;  
    GO  
    EXECUTE SpecifyCurrentBalance '07/22/2007', 2, 2, 415.25,         
           'First regular payment';  
    GO  
    EXECUTE SpecifyCurrentBalance '12/24/2006', 1, 5, 611.93;  
    GO  
    EXECUTE SpecifyCurrentBalance '04/25/2006', 3, 5, 611.93;  
    GO  
    EXECUTE SpecifyCurrentBalance '09/26/2007', 2, 2, 415.25;  
    GO  
    EXECUTE SpecifyCurrentBalance '09/24/2006', 3, 4, 134.38;  
    GO  
    EXECUTE SpecifyCurrentBalance '03/25/2007', 2, 3, 25;  
    GO  
    EXECUTE SpecifyCurrentBalance '11/28/2006', 2, 5, 611.93,   
     'First Car Payment';  
    GO  
    EXECUTE SpecifyCurrentBalance '08/28/2007', 1, 2, 415.25,   
     'Second payment';  
    GO

Paramètres de sortie

Transact-SQL utilise la notion de transmettre un argument par référence. Ce type d'argument est transmis à une procédure mais il est censé retourner une valeur. En d'autres termes, vous pouvez créer une procédure stockée qui prend un paramètre, mais le but du paramètre est d'exécuter une nouvelle valeur lorsque la procédure se termine, afin que vous puissiez utiliser cette valeur comme bon vous semble.

Pour créer un paramètre qui renvoie une valeur de la procédure stockée, après le nom de la procédure, si vous souhaitez que la procédure stockée prenne des arguments, tapez-les. Dans le cas contraire, omettez-les. D'autre part, vous devez transmettre au moins un argument, nommez-le en commençant par le symbole @, spécifiez son type de données, puis entrez le mot-clé OUTPUT sur sa droite. Sur cette base, vous pouvez utiliser la syntaxe de base suivante :

CREATE PROCEDURE ProcedureName 
@ParameterName DataType OUTPUT 
AS 
Body of the procédure

Dans le corps de la procédure, vous pouvez effectuer l'affectation comme bon vous semble. La règle principale que vous devez suivre est que, avant la fin de la procédure, vous devez avoir indiqué une valeur pour l'argument OUTPUT. C'est la valeur que l'argument tiendra lorsque la procédure stockée s'arrête. Voici un exemple :

CREATE PROCEDURE dbo.CreateFullName 
    @FName nvarchar (20), 
    @LName nvarchar (20), 
    @FullName nvarchar (42)OUTPUT 
AS 
    SELECT @ FullName = @ LName + ', N' + @ FName 
GO

Lorsque vous appelez la procédure stockée, vous devez transmettre un argument pour le paramètre OUTPUT et, une fois de plus, vous devez taper OUTPUT sur le côté droit de l'argument. N'oubliez pas que la procédure stockée reviendrait à l'argument. Cela signifie que, après l'appel de la procédure, vous pouvez reprendre l'argument OUTPUT et l'utiliser comme bon vous semble. Voici un exemple :

DECLARE @FirstName nvarchar(20), 
        @LastName nvarchar (20), 
        @Full nvarchar(42)
SET @FirstName = N'Melanie '; 
SET @LatName = N'Johanssen ' ;
 
 
EXECUTE dbo.CreateFullName @FirstName, @LastName, @Full OUTPUT 
 
 
SELECT @Full ; 
GO

L'un des avantages de l'utilisation d'une fonction ou une procédure stockée est qu'elle a accès aux tables et aux enregistrements de la base de données. Cela signifie que vous pouvez accéder aux colonnes et aux enregistrements tant que vous spécifiez la table ou la vue, ce qui est fait avec une clause FROM associée à une instruction SELECT. Examinez la procédure stockée suivante créée dans une base de données qui contienne un tableau nommé Students :

USE ROSH ; 
GO 
 
 
CREATE PROCEDURE ShowStudentsFullNames 
    @FullName nvarchar(42) OUTPUT 
AS 
    SELECT @FullName = LastName + ', N' + FirstName FROM Students ; 
GO

Lorsque vous exécutez cette procédure stockée, elle fonctionnerait sur les enregistrements de la table. Une des particularités d'une procédure stockée qui prend un argument OUTPUT est qu'elle ne peut renvoyer qu'une seule valeur. Prenons l'exemple suivant de l'exécution de la procédure ci-dessus :

Lors de l'appel d'une telle procédure, si vous ne spécifiez pas une condition pour produire un résultat particulier, l'interprète SQL dans ce cas sélectionnerait le dernier enregistrement. Cela signifie que vous devez toujours vous assurer que votre procédure stockée qui prend un paramètre OUTPUT aurait un moyen d'isoler un résultat. Si la procédure stockée traite une instruction SELECT, vous pouvez utiliser une condition WHERE. Voici un exemple d'une telle procédure :

USE ROSH ; 
GO 
 
 
CREATE PROCEDURE ShowStudentsFullNames 
    @FullName nvarchar(42) OUTPUT 
AS 
    SELECT @FullName = LastName + ', N' + FirstName FROM Students 
    WHERE StudentID = 8; 
GO

Lorsque cette procédure est exécutée, elle produirait uniquement l'enregistrement stocké dans le 8e rang de la table.

Résumé de la leçon

 

Exercices

  1. Créez une procédure stockée nommée ProcessPayroll qui prend 11 arguments :
    1. Le nombre d'heures travaillées pour la première semaine (passé par valeur)
    2. Le nombre d'heures travaillées pour la deuxième semaine (passé par valeur)
    3. Un nombre qui représente le nombre d'heures régulières travaillées pendant les deux semaines (transmis par référence)
    4. Un numéro pour le salaire versé pour les heures régulières de deux semaines (transmis par référence)
    5. Un nombre qui représente le nombre d'heures travaillées pendant les deux semaines (transmis par référence)
    6. Un numéro pour le salaire payé pour les heures supplémentaires de deux semaines (transmis par référence)
    7. Un nombre qui représente le salaire net payé pour cette paie (transmis par référence)
 
 
    

Précédent Copyright © 2009 Yevol Suivant