Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

SGBD – Programmer des procédures stockées

Système de gestion de base de données



Introduction:

Les procédures stockées permettent de disposer de méthodes encapsulées au niveau du serveur qui prendront en charge les transactions sur les données.

Différents avantages peuvent être tirés de cette approche :

Le code transactionnel n’est pas disséminé dans les différents composants logiciels manipulant les données mais centralisé sur le serveur.

Cette approche permet la réutilisation de code. Par exemple, quel que soit le composant qui demande l’ajout d’un client, il devra nécessairement le faire via la procédure stockée.

Elle respecte les normes de développement par couche : la couche transactionnelle est isolée des autres couches (métier, interface). Il est ainsi envisageable de confier le développement de cette couche à des spécialistes sous le contrôle du DBA (Data Base Administrator).

La mise au point est facilitée : une fois les tests unitaires des procédures stockées réalisés et leur conformité aux règles de gestion énoncées vérifiées, ces dernières peuvent être utilisées en toute confiance.

Cette approche apporte un niveau de sécurité supplémentaire:

Permet d’éviter que les utilisateurs connaissement le détail du schéma des objets de la base. Les utilisateurs n’accèdent pas directement aux tables mais échangent des valeurs  à travers la paramètres.

Dès lors que les opérations de mise à jour se font via des procédures stockées, il n’est plus nécessaire d’accorder des droits de modification des données sur les tables aux utilisateurs. Cette approche restreint l’usage d’interfaces non prévues pour la mise à jour des données (Access CS, Excel, …)

Une procédure stockée peut inclure n’importe quelle instruction SQL en dehors des instructions permettant la création de règles, de vues ou de programmes déclencheurs. Elle est de ce point de vue beaucoup plus souple que les fonctions intégrées.

Les procédures stockées admettent des paramètres en entrée et sortie. Elles peuvent aussi retourner une valeur scalaire (on parle alors de code retour) et/ou un jeu d’enregistrements dans le cadre d’une procédure utilisée pour sélectionner des données.

La généralisation du codage des transactions sous forme de procédures stockées peut toutefois rendre la portabilité de vos applicatifs plus difficile.

En effet, bien que la notion de procédure stockée existe sur tout SGBDR réseau (par opposition à SGBD fichier tel que Access), les conditions de mise en œuvre et la codification peuvent être quelque peu différentes.

Mais il est tout à fait envisageable de s’appuyer sur des générateurs de procédures stockées afin de réduire le temps à consacrer à leur réalisation.

Types de procédures stockées :

Il existe 3 types de procédures stockées : ü Les procédures de type système qui résident dans la base de données Master et sont utilisées pour des tâches d’administration : préfixe SP_.

  • Les procédures de type système, mais étendues, qui résident dans la base de données Master et sont utilisées pour des tâches diverses mais hors services SQL. Elles sont implémentées comme des DLL. Leur nom est précédé d’un préfixe XP_. Par exemple, nous pouvons citer la procédure xp_cmdshell qui permet l’exécution de commandes du système d’exploitation.
  • Les procédures stockées locales. Elles sont définies par l’utilisateur et sont stockées dans les bases de données utilisateur.

Codification des 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.

Création, modification et suppression d’une procédure stockée

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.

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
  • 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 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.

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).

Exemples :

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.

A noter :

Utilisation de NOCOUNT :

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).

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.

Valeur retourné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.

Utilisation de l’instruction EXISTS :

Elle permet de s’assurer qu’aucune ligne ne figure dans la table avec une valeur de clé identique.

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

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.

Appel des procédures stockées :

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 position : ce procédé est conforme aux standards SQL.
  • Par référence nommée : ce procédé est réservé aux technologies Microsoft.

Passage par position

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.

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.

L’image suivante illustre ces propos.

Passage 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.


Comments are closed, but trackbacks and pingbacks are open.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More