les procédures stockées

Les procédures stockées TS Base de données PROF : Mme Aissat

Implémentation des procédures stockées

Objectif pédagogique :

A la fin de la séance, le stagiaire sera capable de créer, exécuter, modifier et supprimer des procédures stockées. Utiliser des paramètres dans les procédures stockées, Traiter les messages d’erreur

Plan :

1. Présentation des procédures stockées :

Page | 1


1.1. Définition :Une procédure stockées est un ensemble d’instructions du LMD stockées dans la base des données et parfaitement identifiée par son nom.

SQL SERVER prend en charge les procédures stockées :a- Procédure stockées système(sp_) :

Résident dans la base de données master, Sont utilisées pour extraire les informations des tables systèmeExemples :

Nous avons déjà exécuté des procédures stockées telles que sp_help, sp_whob- Procédure stockées locales

Sont crées dans les bases de données individuelles des utilisateursc- Procédures stockées temporaires :

Sont crées dans la base de données tempBD Temporaires locale (son nom est précédée par #) sont disponibles au sein d’une seule

session utilisateur Temporaires globale (son nom est précédée par ##) sont disponibles sur l’ensemble des

sessions utilisateurs.d- Procédures stockées distantes :

Les procédures stockées sont exécutées dans le serveur et le résultat renvoyé aux clients.

1.2. Avantage des procédures stockées : Evitent aux utilisateurs d’avoir accès aux détails des tables de la base de données Fournissent un mécanisme de sécurité : les utilisateurs peuvent recevoir l’autorisation

d’exécuter une procédure stockée même s’ils ne disposent pas de l’autorisation d’accéder aux tables et aux vues référencées dans la PS.

Contribuent à la diminution du trafic sur le réseau : au lieu d’envoyer des centaines d’instructions Transact-SQL sur le réseau, les utilisateurs peuvent efféctuer une opération complexe à l’aide d’une seule instruction, ce qui qui a pour effet de diminuer le nombre de requêtes échangées entre le client et le serveur.

2. Gestion des procédures stockées: 2.1. Création de procédures stockées :

Syntaxe :

Syntaxe

CREATE PROC [ EDURE ] sp_nom [ ; numéro ] [ @param1 type_de_donnée [OUTPUT],

@param2 tpe_de_donnée,..] AS

instruction_sql -- Code de la procédure

Arguments

Sp_nom

Page | 2


Nom de la nouvelle procédure stockée. Les noms des procédures doivent respecter les règles applicables aux identificateurs et doivent être uniques dans la base de données et pour son propriétaire (pour une meilleur organisation je vous conseille de précéder les procédure stockées par sp_)

;numéro

Nombre entier facultatif utilisé pour regrouper les procédures de même nom afin qu'elles puissent être supprimées ensemble à l'aide d'une seule instruction DROP PROCEDURE. Par exemple, les procédures utilisées avec une application appelée order peuvent être nommées orderproc;1, orderproc;2, etc. L'instruction DROP PROCEDURE orderproc supprime le groupe tout entier.

@param

Un paramètre de la procédure. Vous pouvez déclarer un ou plusieurs paramètres dans une instruction CREATE PROCEDURE. La valeur de chaque paramètre déclaré doit être fournie par l'utilisateur lors de l'exécution de la procédure (sauf si vous définissez une valeur par défaut pour le paramètre). Une procédure stockée peut comprendre au maximum 2100 paramètres.

Spécifiez un nom de paramètre en plaçant le signe @ comme premier caractère. Ce nom doit respecter les règles gouvernant les identificateurs. Un paramètre est local à une procédure, vous pouvez donc utiliser le même nom dans d'autres procédures.

[OUTPUT] permet de spécifier un paramètre retourné par la procédure.

Type_de_donnée

Type de données du paramètre. Tous les types de données y compris les types text, ntext et image, peuvent être utilisés comme paramètre dans une procédure stockée.

AS

Spécifie l’ensemble des instructions SQL de la procédure (code de la procédure)

2.2. Modification des procédures stockées

Pour modifier une procédure stockée :

ALTER PROC [ EDURE ] sp_nom [ ; numéro ] { @parameter data_type } AS Instructions_sql

2.3. Suppression des procédures stockées2.4. Pour supprimer une procédure stockée :

Drop proc[edure] sp_nom

2.5. Exécuter une une procédure stockée

Page | 3


Utilisez l'instruction EXECUTE de Transact-SQL pour exécuter une procédure stockée. L'utilisation du mot clé EXECUTE n'est pas nécessaire à cette exécution si la procédure est la première instruction du lot.

Exec [ute] sp_nom [@parameter 1= value1,@parameter2 = value2,@parameter3 = value3…]

3. Exemples

a- Procédure simple sans paramètres : la procédure stockée suivante permet d’afficher les informations des tables produit et fournisseur

CREATE PROCEDURE sp_voirInformationAS SELECT * FROM Produit; SELECT * FROM Fournisseur; GOExécuter une procédure stockée :

EXEC dbo.sp_voirInformation – ou EXEC voiInformation

b. Procédure avec un seul paramètre :Cette procédure stockée donne le contenu d’une commande donnée.Le numéro de la commande change donc il est passé comme paramètre.

CREATE PROC sp_commande @NC char(6) -- @NC est une variable qui représente le paramètre

-- Numéro de la commande AS Begin SELECT P.Codeart, P.LibArt,L.Qtecde,L.PriUniV FROM Produit P INNER JOIN LigCom L ON P.CodeArt = L.CodeArt WHERE L.NumCom = @NC EndPour exécuter la procédure stocké : Si on tape : EXEC Sp_commandeune erreur est générée :

Msg 201, Niveau 16, État 4, Procédure sp_commande, Ligne 0La procédure ou fonction 'sp_commande' attend le paramètre '@NC', qui n'a pas été fourni.

Car la procédure stockée contient un paramètre mais dans l’appel aucune valeur n a été donnée à ce dernier.

EXEC sp_commande @nc='090010'c. Procédure avec plusieurs paramètres :

Page | 4


Cette procédure stockée affiche les numéros de commande dans lesquelles existe une quantité supérieur à une quantité X d’un produit donné.Les paramètres nécessaires sont donc : le code du produit et la quantité fixée.

CREATE PROC SP1 @P_codeart char(4), @P_qtecde int AS begin SELECT Numcom from LigCom where (CodeArt = @p_codeart) and (QteCde >= @P_qtecde) end Exemple d’Exécution EXEC sp1 'I105',50 ou encore EXEC sp1 @p_qtecde = 50, @P_codeart ='I105' - - L’ordre ici n’est pas

--important puisque le nom du paramètre est sfécifié.

d- Procédure qui a un paramètre optionnelDans cette procédure stockée la liste des commandes est afichée si aucun paramètre n’est passé sinon affiche les information d’une commande donnée.

Create proc PS;1(@Id char(6) =null)As If @Id is null

BeginSelect * from commande

EndElse Begin

Select * from Commande where NumCom=@IdEnd

Exemple d’exécution :

EXEC ps;1 -- Affiche toutes les commandes

EXEC ps;1 '090010' -- affiche la commande 090010

EXEC ps;1 @Id = '090010'

EXEC ps;1 @Id = default – -NULL est la valeur par defaut

e- Procédure avec plusieurs instructions

CREATE PROC PS3AS

Page | 5


begin select * from Produit end begin UPDATE Produit set Stkale = Stkale - 5 INSERT INTO Produit values ('L002','Souris',20,25,'Unité') End

f- Procédure stockée qui renvoie un résultat Cette procédure renvoie le montant total d’une facture donnée.

CREATE PROC PS4 @Num char(6), @total int OUTPUTAS Select @total = SUM (priuniv * qtecde) from LigCom where NumCom = @Numselect @total AS [Montant de la facture]

Exécution :declare @Montant money EXEC PS4 @num = '090010',@total=@Montant output

COmplément:SET NOCOUNTPour ne pes renvoyer le nombre de lignes afféctées après une exécution d’une procédure stockées, on ajoute SET NOCOUNT au début de la procédure :

SET NOCOUNT {ON|OFF}

Si la valeur de SET NOCOUNT est définie à ON, ce comptage n'est pas renvoyé. Si la valeur de SET NOCOUNT est définie à OFF, ce comptage est renvoyé.

4- Gestion des erreurs:4-1- Introduction:Il existe deux types d’erreur générés par SQL SERVER :

- Des erreurs syntaxiques qui sont gén- érées lors de la compilation, dans ce cas aucune instruction SQL ne sera exécutée,

Page | 6


- Des erreurs lors de l’exécution des instructions (Run-Time Error) qui peuvent provoquées une incohérence dans la base de données.

En règle générale, tous les messages possèdent la même structure : un numéro d’erreur, un message d’explication de l’erreur, un indicateur de sévérité, un état, le nom de la procédure associée à l’erreur et le numéro de la ligne ayant provoquée l’erreur

Attribut Description

Numéro d'erreur

Chaque message d'erreur possède un numéro d'erreur unique.

Chaîne de message d'erreur

Le message d'erreur contient des informations sur la cause probable de l'erreur. De nombreux messages d'erreur ont des variables de substitution contenant des informations telles que le nom de l'objet à l'origine de l'erreur.Les messages d'erreur sont stockés dans la base Master. On peut lister le contenu de cette table avec la commande :

SELECT * FROM sys.messages;

Gravité La gravité indique l'importance de l'erreur

État Un état est associé à une erreur

Nom de la procédure

Nom de la procédure stockée ou du déclencheur dans lequel l'erreur s'est produite.

Numéro de ligne

Indique l'instruction qui a provoqué l'erreur dans un traitement, une procédure stockée, un déclencheur ou une fonction.

En résumé : la "sévérité"ou gravité qui est notée par une valeur (Voir les tableau complet en annex)

Severity 0 - Information (T-SQL PRINT) Severity 1-9 - Information (not used by SQL Server) Severity 10 - Converted to severity 0 Severity 11-16 - Programming Errors Severity 17-25 - Resource Error Severity 19 and above - must be sysadmin Severity 20-25 - Terminate the connection

Exemple d’erreurs:

Exemple avec erreur Erreur généré

select * from tableinexistante Msg 208, Niveau 16 , État 1, Ligne 1

Page | 7


Nom d'objet 'tableinexistante' non valide.

seect * from tableinexistante Msg 102, Niveau 15 , État 1, Ligne 1Syntaxe incorrecte vers '*'.

USE BdconsommableGoSELECT * FROM Produit

Msg 229, Niveau 14 , État 5, Ligne 1L'autorisation SELECT a été refusée sur l'objet 'Produit', base de données 'Bdconsommable', schéma 'dbo'.

Il existe deux manières de gérer les erreurs. La première consiste à tester la valeur de la variable système @@ERROR, la seconde consiste à positionner dans un gestionnaire d’exception TRY le bloc d’instructions à tester, et dans le CATCH, l’erreur à lever.

4-2- l’instruction TRY… CATCH

Syntaxe :

BEGIN TRY { instructions_SQL | Block_instructions }

END TRYBEGIN CATCH { instructions_SQL | Block_instructions }END CATCH

instructions_SQLToute instruction Transact-SQL.

Block_instructions Tout groupe d'instructions Transact-SQL dans un lot ou contenues dans un bloc BEGIN…END.

Fonctionnement: à l’exécution si une erreur ou excéption se produit dans le block TRY, immédiatement SQL SERVER exécute le block CATCH.Si aucune erreur ne s’est produite dans le block TRY , le BLOCK CATCH est ignoré.

Remarques : - Le block TRY est obligatoirement suivie du BLOCK CATH même si ce dernier sera vide,- Aucune instruction ne doit sépérée END TRY de BEGIN CATCH.

Exemple : Exemple d’un block sans erreur :

BEGIN TRY DECLARE @larg decimal(6, 3),

@Perimetre decimal(6, 3);

SET @larg = 124.36; SET @Perimetre = @larg * 4;

END TRYBEGIN CATCH

PRINT 'Il Y a erreur 'END CATCH

Page | 8


PRINT 'Le perimetre est'PRINT @perimetre

Dans cet exemple, aucune erreur ne s’est produite dans le block TRY, le BLOCK CATCH est ignoré, les instructions suivantes à END CATCH sont excécutées.

Exemple avec erreur :Exécuter l’exemple suivant et constatez l’erreur : DECLARE @Nbr tinyint, @Result tinyint;

SET @Nbr = 252;SET @Result = @Nbr + 20;

SELECT @Nbr AS Nombre, @Result AS Resultat;L’erreur est :Erreur de dépassement de capacité arithmétique pour le type de données tinyint, valeur = 272.

En utilisant TRY … CATCH BEGIN TRYDECLARE @Nbr tinyint, @Result tinyint;


SELECT @Nbr AS Nombre, @Result AS Resultat;END TRYBEGIN CATCH PRINT N'il y a erreur dans le programme';END CATCH

A l’exécution aucune exéption ne s’est déclenchée.

4-3- Identification de l’erreur :

Lorsqu’une erreur est signalée, certainement le première chose à laquelle on pense est de localisée l’erreur pour pouvoir la corrigé.Dans le Block CATCH il est possible d’utiliser les fonctions suivantes :

Function Description

ERROR_NUMBER() Retourne le numéro de l’erreur qui est un entier. ERROR_SEVERITY() Retourne le numéro de gravité de l’erreurERROR_STATE() Returns the error state number.ERROR_PROCEDURE() Retourne le nom de la procedure stockée où l’erreur s’est produite.ERROR_LINE() Retourne le numéro de la ligne où l’erreur s’est produite.ERROR_MESSAGE() Retourne le message correspondant à l’erreur.

Exemple 1 :

Page | 9


Soit le code suivant qui contient une division par 0BEGIN TRY -- Création d'une erreur de division par 0 SELECT 1/0;END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS NumeroErreur, ERROR_SEVERITY() AS SeveriteErreur, ERROR_STATE() AS EtatErreur, ERROR_PROCEDURE() AS ProcedureErreur, ERROR_LINE() AS LigneErreur, ERROR_MESSAGE() AS MessageErreur;END CATCH;GOL’exécution donne :

Exemple 2:BEGIN TRYDECLARE @Nbr tinyint, @Result tinyint;


SELECT @Nbr AS Nombre, @Result AS Resultat;END TRYBEGIN CATCH PRINT N'il y a erreur dans le programme'; pRINT 'erreur '+ cast(error_number() as nvarchar(100))END CATCH

L’exécution donne : il y a erreur dans le programmeerreur 220

Cependant, Les constructions TRY…CATCH n'interceptent pas les conditions suivantes :

Les avertissements ou messages d'information dont la gravité est inférieure ou égale à 10. Les erreurs dont le niveau de gravité est supérieur ou égal à 20 interrompent le traitement des

tâches du moteur de base de données SQL Server pour la session. Si une erreur dont le niveau de gravité est supérieur ou égal à 20 survient et que la connexion à la base de données n'est pas interrompue, TRY…CATCH gère l'erreur.

Page | 10

CAST et CONVERTConvertit une expression d'un type de données en un autre dans SQL Server 2008 R2.

Syntax for CAST:

CAST (expression AS data_type [ (length ) ] )

Syntax for CONVERT:

CONVERT (data_type [ (length ) ] ,expression [ ,style ] )


Un événement d'avertissement, tel qu'une requête d'interruption par le client ou une rupture de connexion avec le client, se produit.Cela survient lorsque la session est terminée par un administrateur système utilisant l'instruction KILL.

4-4- Utilisation de TRY … CATCH dans la gestion des transactions :

Pour controler la validité des transactions, en plus des instruction BEGIN TRANSACTION et COMMIT TRANSACTION, on peut utiliser une fonction XACT_STAT.

Celle-ci peut avoir 3 valeurs :

-1 : La transaction est considérée comme valide et peut être "Commitée" 0 : Etat de base, il n'y a aucune transaction à valider 1 : La transaction est invalide et doit être "Roll Backée"

Afin d'utiliser XACT_STATE, il nous faut activer :

SET XACT_ABORT ON;

Indique si SQL Server annule automatiquement la transaction en cours lorsqu'une instruction Transact-SQL déclenche une erreur d'exécution.

Exemple : Nous allons reprendre l’exemple utilisé dans la gestion des transactions :

use testgo

SET XACT_ABORT ON;

BEGIN TRY BEGIN TRANSACTION AjoutEmployes INSERT INTO Employes VALUES (N'928375', N'Ben Farouk', N'20080628');-- erreur: une valeur d'une collone manque INSERT INTO Employes VALUES (N'792764', N'Fourali Fatiha', N'20000616', 1500.00); COMMIT TRANSACTION AjoutEmployes;END TRYBEGIN CATCH PRINT 'erreur n°' + Cast(error_number() as varchar(100)); IF (XACT_STATE()) = -1 BEGIN PRINT 'La transaction est invalide.' ROLLBACK TRANSACTION AjoutEmployes; END; -- Tester si la transaction est valide pour la commiter IF (XACT_STATE()) = 1 BEGIN PRINT 'La transaction est valide'; COMMIT TRANSACTION AjoutEmployes; END;END CATCH;

Page | 11


GO

L’exécution donne un message d’erreur, c-à-d que le block CATCH n’a pas été excéuté car l’erreur colonne manquante (erreur de syntaxe) n’est pas prise en charge dans CATCH.Voiyons une erreur de type Run-Time (d’exécution) telle qu’une violation de caontrainte de clé primaire : use testgo

SET XACT_ABORT ON;

BEGIN TRY BEGIN TRANSACTION AjoutEmployes INSERT INTO Employes VALUES (N'792764', N'Fourali Fatiha', N'20000616', 1500.00); INSERT INTO Employes VALUES (N'792764', N'Fourali Fatiha', N'20000616', 1500.00);

COMMIT TRANSACTION AjoutEmployes;END TRYBEGIN CATCH PRINT 'erreur n°' + Cast(error_number() as varchar(100)); IF (XACT_STATE()) = -1 BEGIN PRINT 'La transaction est invalide.' ROLLBACK TRANSACTION AjoutEmployes; END; -- Tester si la transaction est valide pour la commiter IF (XACT_STATE()) = 1 BEGIN PRINT 'La transaction est valide'; COMMIT TRANSACTION AjoutEmployes; END;END CATCH;GO

L’erreur est contournée par CATCH le résultat est :

(1 ligne(s) affectée(s))erreur n°2627La transaction est invalide.

Page | 12


ANNEX (http://msdn.microsoft.com/fr-fr/library/ms164086.aspx

Niveaux de gravité des erreurs du moteur de base de données

Lorsqu'une erreur est déclenchée par le Moteur de base de données SQL Server, la gravité de l'erreur indique le type de problème rencontré par SQL Server.

Niveaux de gravité

Le tableau suivant répertorie et décrit les niveaux de gravité des erreurs déclenchées par le Moteur de base de données SQL Server.

Niveau de gravité

Description

0-9 Messages qui retournent des informations d'état ou qui signalent des erreurs sans gravité. Le Moteur de base de données ne déclenche pas d'erreurs système dont les niveaux de gravité sont compris entre 0 et 9.

10 Messages qui retournent des informations d'état ou qui signalent des erreurs sans gravité. Pour des raisons de compatibilité, le Moteur de base de données convertit le niveau de gravité 10 en 0 avant de retourner les informations d'erreur à l'application appelante.

11-16 Indique les erreurs pouvant être corrigées par l'utilisateur.

11 Indique que l'objet ou l'entité spécifique n'existe pas.

12 Niveau de gravité particulier pour les requêtes qui n'utilisent pas de verrouillage en raison d'indicateurs de requête spéciaux. Dans certains cas, les opérations de lecture effectuées par ces instructions peuvent produire des données incohérentes, car les verrouillages ne sont pas effectués pour garantir la cohérence.

13 Indique des erreurs de blocage de transaction.

14 Indique des erreurs liées à la sécurité, par exemple le refus d'une autorisation.

15 Indique des erreurs de syntaxe dans la commande Transact-SQL.

16 Indique des erreurs générales qui peuvent être corrigées par l'utilisateur.

17-19 Indique des erreurs logicielles qui ne peuvent pas être corrigées par l'utilisateur. Informez votre administrateur système de l'existence du problème.

17 Indique que l'instruction a obligé SQL Server à s'exécuter avec des ressources insuffisantes (mémoire, verrous ou espace disque de la base de données) ou à dépasser une limite fixée par l'administrateur système.

18 Indique l'existence d'un problème logiciel avec le Moteur de base de données ; toutefois, l'instruction s'exécute intégralement et la connexion à l'instance du Moteur de base de données est conservée. L'administrateur système doit être informé chaque fois qu'un message s'affiche avec le niveau de gravité 18.

19 Indique qu'une limite non configurable du Moteur de base de données est dépassée et que le traitement est terminé. Les messages d'erreur dont le niveau de gravité est supérieur ou égal à 19 arrêtent l'exécution du traitement actuel. Les erreurs dont le niveau de gravité est 19 se produisent rarement ; elles doivent être résolues par l'administrateur système ou votre support technique. Contactez votre administrateur système en cas de déclenchement d'un message dont le niveau de gravité est 19. Les messages d'erreur dont le niveau de gravité est compris entre 19 et 25 sont inscrits dans le journal des erreurs.

20-25 Indique l'existence de problèmes système et d'erreurs irrécupérables, ce qui signifie que la tâche du Moteur de base de données exécutant une instruction ou un traitement n'est plus en cours d'exécution. La tâche enregistre des informations sur ce qui s'est produit, puis se termine. Dans la plupart des cas, la connexion de l'application à l'instance du Moteur de base de données se termine également. Si cela se produit, selon la nature du problème, l'application risque de ne pas pouvoir se reconnecter.

Les messages d'erreur de ce niveau peuvent affecter tous les processus qui accèdent aux données de la même base de données et indiquer la détérioration d'une base de données ou d'un objet. Les messages d'erreur dont le niveau de gravité est compris entre 19 et 25 sont inscrits dans le journal des erreurs.

20 Indique qu'une instruction a rencontré un problème. Dans la mesure où le problème affecte uniquement la tâche actuelle, il est improbable que la base de données elle-même soit

Page | 13


endommagée.

21 Indique qu'un problème a été rencontré et qu'il affecte toutes les tâches de la base de données actuelle ; toutefois, il est improbable que la base de données elle-même soit endommagée.

22 Indique que la table ou l'index spécifié dans le message a été endommagé à la suite d'un problème logiciel ou matériel.

Les erreurs dont le niveau de gravité est 22 se produisent rarement. Si ce type d'erreur se produit, exécutez DBCC CHECKDB pour déterminer si d'autres objets de la base de données sont également endommagés. Il est possible que le problème provienne uniquement du cache des tampons et non du disque lui-même. Dans ce cas, le redémarrage de l'instance du Moteur de base de données permet de corriger le problème. Pour pouvoir continuer à travailler, vous devez vous reconnecter à l'instance du Moteur de base de données ; dans le cas contraire, utilisez DBCC pour corriger le problème. Dans certains cas, vous pouvez être amené à restaurer la base de données.

Si le redémarrage de l'instance du Moteur de base de données ne permet pas de corriger le problème, cela signifie que ce dernier provient du disque. Dans certains cas, il peut être résolu en détruisant l'objet spécifié dans le message d'erreur. Par exemple, si le message indique que l'instance du Moteur de base de données a trouvé une ligne de longueur 0 dans un index non-cluster, supprimez l'index et reconstruisez-le.

23 Indique que l'intégrité de la totalité de la base de données est douteuse en raison d'un problème matériel ou logiciel.

Les erreurs dont le niveau de gravité est 23 se produisent rarement. Si ce type d'erreur se produit, exécutez DBCC CHECKDB pour déterminer l'étendue des dommages. Il est possible que le problème provienne uniquement du cache et non du disque lui-même. Dans ce cas, le redémarrage de l'instance du Moteur de base de données permet de corriger le problème. Pour pouvoir continuer à travailler, vous devez vous reconnecter à l'instance du Moteur de base de données ; dans le cas contraire, utilisez DBCC pour corriger le problème. Dans certains cas, vous pouvez être amené à restaurer la base de données.

24 Indique une défaillance du support. L'administrateur système peut être obligé de restaurer la base de données. Vous pouvez également être amené à contacter le fournisseur de votre matériel.

Page | 14

Documents

les procédures stockées