Il existe 3 types de procédures stockées :
La conception des procédures stockées doit obéir aux mêmes règles que celles relatives à la conception des fonctions et procédures définies dans d’autres langages : la procédure stockée doit effectuer une et une seule tâche. Ainsi, si nous devons programmer les opérations de maintenance des données de la table client, nous devrons coder 3 procédures relatives à l’ajout, la modification et la suppression d’un client.
Comme pour les autres objets SQL, nous associerons les mots clefs CREATE, ALTER ou DROP, au type de l’objet PROCEDURE et à son nom, afin de créer, modifier ou supprimer une procédure.
Opérations de création et maintenance des Procédures stockées
La procédure stockée PS_Client_Supprimer
Les procédures stockées acceptent des paramètres dont il sera nécessaire de définir les caractéristiques :
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 (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.
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).
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.
Procédure d'insertion d'une ligne
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).
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.
Procédure d'insertion d'une ligne
A noter :
Utilisation de NOCOUNT :
Empêche/oblige l'envoi au client de messages pour chaque instruction d'une procédure stockée affichant la ligne « nn rows affected » à la fin d'une instruction (SELECT, INSERT, UPDATE, DELETE).
Il est recommandé de désactiver le comptage dans les procédures stockées afin d'éviter l'envoi intempestif de lignes non lues qui génère du trafic réseau et rallonge les temps d'exécution.
Utilisation de la fonction SCOPE_IDENTITY() :
Cette fonction 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.
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.
Insertion d'une ligne avec contrôle valeur clé primaire
A noter :
Utilisation de l’instruction EXISTS :
Elle permet de s’assurer qu’aucune ligne ne figure dans la table avec une valeur de clé identique.
Les procédures stockées sont exécuté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 référence nommée : ce procédé est réservé aux technologies Microsoft.
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.
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.
tilisation de paramètres en sortie
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.
Procédure avec de nombreux paramètres optionnels
Passage par référence nommée des paramètre
Soit le schéma de la base de données qui décrit les mouvements des comptes de clients d'une banque :
Client (Num_client, Nom_client, Prenom_Client)
Compte (Num_Compte, Solde, #Num_Client,typeC)
Mouvement (Num_Mouvement, montant, TypeM, #Num_Compte, DateM)
La table Mouvement archive les mouvements effectués sur chaque compte on retiendra le montant et la date du mouvement, le type peut être un retrait signalisé par (R) ou un dépôt signalisé par (D).
La procédure doit effectuer le traitement suivant :
(Ne pas utiliser la propriété Identity).
La procédure doit effectuer le traitement suivant :
Ajouter un commentaire
Veuillez vous connecter pour ajouter un commentaire.
Pas encore de commentaires.