Les proc�dures stock�es

Document Sample
Les proc�dures stock�es Powered By Docstoc
					SGBD II - SQL Server 2005 -                                                  M. Mohamed ZAZZA




                                                                             CHAPITRE

                                 Procédures stockées



1. Définition
Une procédure stockée (Stored Procedure en anglais), appelée aussi une procédure
cataloguée ou encore une procédure mémorisée (Remote procedure en anglais) est une suite
d’instructions SQL stockées dans la base de données et pouvant être exécutée par appel de
son nom.

Les procédures stockées sous SQL Server peuvent prendre en paramètre et/ou retourner des
entiers, des chaînes de caractères, des dates, des curseurs, des tables, des tables virtuelles et
tout autre type défini dans SQL Server par défaut ou par les utilisateurs.

Les procédures stockées diffèrent des instructions SQL standards par les particularités
suivantes :
   Elles sont compilées dont le code est vérifié et compilé pour être utilisé lors des appels.
   Elles ne sont pas appelées automatiquement, mais suite à un appel explicite de la part de
   l’utilisateur.
   Elles peuvent être appelées par plusieurs applications frontales.
   Elles gèrent les dépendances entre le code SQL et les objets du moteur.

L’avantage majeur d’utilisation des procédures stockées réside dans le fait qu’elles
améliorent les performances de la programmation par utilisation du code compilé. En outre,
elles renforcent l’intégrité de la base de données en centralisant les traitements en un endroit
unique (c’est l’unicité du code).


2. Types de procédures stockées
Il existe trois types de procédures stockées :




OFPPT/ ISTA- NADOR                               Confidentiel                         Page 1 sur 9
SGBD II - SQL Server 2005 -                                                M. Mohamed ZAZZA




2.1 Procédures stockées de type systèmes
Les procédures stockées de type systèmes résident dans la base de données Master et sont
utilisées pour des tâches d’administration. Leur nom est précédé de préfixe SP_

2.2 Procédures stockées de type systèmes étendues
Les procédures de type système étendues résident aussi dans la base de données Master et
sont conçues pour des taches diverses mais hors services SQL. Elles sont implémentées
comme des DLL. Leur nom est précédé d’un préfixe XP_

2.3 Procédures stockées locales
Les procédures stockées locales sont définies par l’utilisateur et stockées dans les bases de
données utilisateur.

3. Création de procédure stockée
Une procédure stockée peut être créée dans l’analyseur de requêtes avec le mot clé CREATE.
La syntaxe simplifiée est la suivante :


CREATE PROCEDURE nom_de_procédure
        Paramètres
AS
      Corps_de_la_pocédure


Exemple :




                              Figure : Exemple de création de procédure



4. Définition des paramètres
Les procédures stockées acceptent des paramètres dont il sera nécessaire de définir les
caractéristiques :
     Le nom du paramètre, préfixé d’un @ pour l’utiliser comme variable dans le programme.
     Le type de données choisi parmi les types SQL ou les types définis par l’utilisateur.
     Une valeur par défaut optionnelle.


OFPPT/ ISTA- NADOR                             Confidentiel                           Page 2 sur 9
SGBD II - SQL Server 2005 -                                                 M. Mohamed ZAZZA




   La direction, par défaut en entrée. Pour définir un paramètre en sortie, il sera nécessaire
   de lui associer le mot clé OUTPUT.
   Par convention, lorsque le paramètre s’apparente à la valeur d’une colonne, il portera un
   nom identique à celle-ci. Dans l’exemple précédent, la procédure reçoit en entrée la
   valeur de l’identifiant de la table Customers qui est CustomerID. Le paramètre sera
   donc nommé @CustomerID.
   Il existe un paramètre en sortie défini par défaut pour toute procédure stockée
   @RETURNVALUE qui reçoit la valeur de l’opération RETURN et la transmet au
   programme appelant.
   Les paramètres doivent être définis en entête de la procédure avant la clause AS qui
   délimite le début du code implémenté dans la procédure.




                              Figure : Paramètres d'une procédure stockée



Vous noterez ici le caractère optionnel du paramètre @Descriptif qui, par défaut, prendra
la valeur NULL.
Le paramètre @IdCCP est défini en sortie car nous souhaitons récupérer dans ce dernier une
valeur attribuée par le système à la colonne identifiant de la table CCP. La colonne a comme
attribut la propriété IDENTITY (compteur).




Exemple 1: L’exemple suivant représente une opération d’insertion sur une table dont la
valeur de la clé primaire est affectée par le système :




OFPPT/ ISTA- NADOR                              Confidentiel                        Page 3 sur 9
SGBD II - SQL Server 2005 -                                                M. Mohamed ZAZZA




Remarques !!!
   Le positionnement de NOCOUNT à ON permet d’éviter que l’instruction SELECT ne
   comptabilise les lignes affectées par la sélection. En fait, ce qui intéresse le programme
   appelant est de connaître le nombre de lignes affectées par l’opération INSERT (ici 1).
   La fonction SCOPE_IDENTITY() permet de récupérer la dernière valeur affectée à une
   colonne identité lors de la dernière instruction INSERT exécutée.
   Par convention, lorsqu’une procédure s’exécute correctement, la valeur retournée est 0.




Exemple 2: Le deuxième exemple est une procédure d’insertion dans une table dont les
valeurs des clés primaires ne sont pas allouées par le système. On doit alors vérifier qu’il
n’existe pas de ligne avec une valeur de clé identique à celle que l’on souhaite insérer.




OFPPT/ ISTA- NADOR                           Confidentiel                             Page 4 sur 9
SGBD II - SQL Server 2005 -                                           M. Mohamed ZAZZA




Remarques !!!
L’utilisation de l’instruction EXISTS permet de s’assurer qu’aucune ligne ne figure dans la
table avec une valeur de clé identique.




OFPPT/ ISTA- NADOR                        Confidentiel                           Page 5 sur 9
SGBD II - SQL Server 2005 -                                                          M. Mohamed ZAZZA




Exemple 2: Le troisième exemple est une procédure réalisée pour exécuter des tâches
d’administration.




                              Figure : Procédure de copie de fichier via le réseau




Remarques !!!
La construction de la commande du système d’exploitation par concaténation de constantes
de type chaîne et de variables.
Le recours à la procédure stockée étendue xp_cmdshell qui permet d’exécuter des
commandes de l’OS.



5. Appel de procédure stockée
Les procédures stockées sont exécutées (appelées) à l’aide de l’instruction EXECUTE ou
EXEC. Il est nécessaire de respecter quelques principes de base lors du passage ou la
réception de valeurs en arguments de procédure.

Les paramètres peuvent être passés selon deux façons :
   Par position : ce procédé est conforme aux standards SQL.
   Par référence nommée : ce procédé est réservé aux technologies Microsoft.




OFPPT/ ISTA- NADOR                                  Confidentiel                             Page 6 sur 9
SGBD II - SQL Server 2005 -                                                              M. Mohamed ZAZZA




4.1 Passage de paramètre par position




                         Figure : Récupération de la valeur retournée par la procédure



La valeur de retour est récupérée dans une variable @RetValue qui doit être insérée entre
l’instruction EXECUTE et le nom de la procédure stockée.
Au sein de la procédure stockée, un message est imprimé si la demande de suppression
concerne un client qui a passé des commandes.




                               Figure : Exemple avec des paramètres en sortie


Dans cet exemple, nous avons 4 paramètres : 2 en entrée, 2 en sortie dont celui correspondant
à la valeur retournée.

Les différents paramètres doivent être séparés par des virgules et le mot clé OUTPUT associé
à la variable qui recevra la valeur du paramètre en sortie.



OFPPT/ ISTA- NADOR                                  Confidentiel                                 Page 7 sur 9
SGBD II - SQL Server 2005 -                                                       M. Mohamed ZAZZA




L’image suivante illustre ces propos :




                              Figure : Utilisation de paramètres en sortie



4.2 Passage de paramètre par référence nommée
Cette approche, qui n’est pas conforme à la norme SQL mais présente dans l’ensemble de
l’architecture des produits Microsoft, permet de ne pas se soucier de l’ordre de déclaration
des paramètres.
Elle offre aussi l’avantage de ne pas avoir à se soucier des paramètres optionnels qui, sans
valeur mentionnée, prendront la valeur par défaut définie dans la procédure.




                      Figure : Procédure avec de nombreux paramètres optionnels




OFPPT/ ISTA- NADOR                              Confidentiel                              Page 8 sur 9
SGBD II - SQL Server 2005 -                                                     M. Mohamed ZAZZA




                         Figure : Passage par référence nommée des paramètres




4.3 Modification de procédure stockée
Les procédures stockées sont des objets compilés. Ceci implique qu’elles ne peuvent pas être
modifiées. Cependant, l’interface SQL Object Manager masque cette contrainte.

Une procédure stockée doit être supprimée pour être à nouveau créée.


4.4 Suppression de procédure stockée
La suppression d’une procédure mémorisée se fait par :
                        DROP PROCEDURE nom_de_procédure




                                      FIN DU CHAPITRE




OFPPT/ ISTA- NADOR                             Confidentiel                             Page 9 sur 9

				
DOCUMENT INFO
Shared By:
Tags: CINOS
Stats:
views:14
posted:2/1/2013
language:
pages:9
Description: ce ficher.c'est le cour de proc�dures stock��s