Les procédures stockées SQL-Server - Oracle - exercices-corrigés

1.Définition et avantages des procédures stockéesUne procédure stockée est une collection précompilée d'instructions Transact-SQL stockée sous un nom et traitée comme une unité. Les procédures stockées de SQL Server permettent de gérer celui-ci et d'afficher les informations sur les bases de données et les utilisateurs. Les procédures stockées fournies avec SQL Server sont appelées procédures stockées du système.
Elles renvoient les données de quatre façons différentes :

• des paramètres de sortie, qui renvoient soit des données (entiers ou caractères) ou une variable de curseur, les curseurs étant des ensembles de résultats pouvant être extraits ligne par ligne ;
• des codes de retour, qui sont toujours un entier ;
• un ensemble de résultats pour chaque instruction SELECT contenue dans la procédure stockée ou toute autre procédure stockée appelée par cette dernière ;
• un curseur global qui peut être référencé en dehors de la procédure stockée.

Les curseurs :Les opérations réalisées dans une base de données relationnelle s'exécutent sur un ensemble complet de lignes. L'ensemble de lignes renvoyé par une instruction SELECT contient toutes les lignes satisfaisant aux conditions de la clause WHERE de l'instruction. Cet ensemble complet de lignes renvoyées par l'instruction est appelé jeu de résultats. Les applications, en particulier les applications interactives en ligne, peuvent ne pas toujours fonctionner efficacement si le jeu de résultats est traité comme une unité. Ces applications ont besoin d'un mécanisme leur permettant de travailler avec une seule ligne ou un petit bloc de lignes à la fois. Les curseurs sont une extension des jeux de résultats et contiennent ce mécanisme. DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OFcolumn_name [ ,...n ] ] } ] Avantages des procéduresstockées:Les procédures stockées contribuent à mettre en œuvre une logique cohérente dans les applications. Les instructions SQL et la logique nécessaires à l'exécution d'une tâche fréquente peuvent être créées, codées et testées une seule fois dans une procédure stockée. Il suffit ensuite à chaque application devant effectuer la tâche d'exécuter la procédure stockée. Le codage de la logique de gestion en une seule procédure offre aussi un point de contrôle unique permettant de vérifier que les règles d'entreprise sont bien respectées.Les procédures stockées peuvent également améliorer les performances. De nombreuses tâches sont mises en œuvre sous forme de séquences d'instructions SQL. La logique conditionnelle appliquée aux résultats des premières instructions SQL détermine les instructions suivantes à exécuter. Si ces instructions SQL et la logique conditionnelle sont écrites dans une procédure stockée, elles deviennent partie intégrante d'un plan d'exécution unique sur le serveur. Les résultats n'ont pas besoin d'être renvoyés au client pour que la logique conditionnelle soit appliquée, car tout le travail est réalisé sur le serveur.
Les procédures stockées évitent aussi aux utilisateurs d'avoir à connaître les détails des tables de la base de données. Si un ensemble de procédures stockées prend en charge toutes les fonctions de gestion nécessaires aux utilisateurs, ceux-ci n'ont pas besoin d'accéder directement aux tables ; il leur suffit d'exécuter les procédures stockées qui modélisent les processus avec lesquels ils ont l'habitude de travailler.Les procédures stockées du système SQL Server évitant aux utilisateurs d'accéder aux tables système en sont un exemple. SQL Server comprend un ensemble de procédures stockées système dont les noms commencent en général par sp_. Ces procédures prennent en charge toutes les tâches administratives nécessaires à l'exécution d'un système SQL Server. Vous pouvez administrer un système SQL Server à l'aide des instructions Transact-SQL associées à l'administration (telles que CREATE TABLE) ou des procédures stockées du système, sans jamais avoir à mettre à jour directement les tables système.
1.Les différents types de procédures stockées1.1.Procédure stockée systèmeEnsemble de procédures stockées fournies par SQL Server pour la réalisation d'opérations telles que l'extraction d'informations du catalogue système ou l'exécution de tâches d'administrationNombre d'activités administratives dans Microsoft® SQL Server™ 2000 s'exécutent à l'aide d'un type spécial de procédure connu sous le nom de procédure stockée système. Les procédures stockées système sont créées et enregistrées dans la base de données master et ont le préfixe sp_. Les procédures stockées du système peuvent s'exécuter depuis n'importe quelle base de données, sans avoir à qualifier complètement le nom de la procédure stockée, en utilisant le nom de base de données master.Il est fortement recommandé de ne pas créer de procédures stockées avec le préfixe sp_. SQL Server recherche toujours une procédure stockée en commençant par sp_ dans l'ordre suivant :

1. elle existe dans la base de données master ;
2. ensuite, en fonction des éventuels identificateurs fournis (nom de base de données ou propriétaire) ;
3. enfin, avec dbo comme propriétaire si aucun propriétaire n'est spécifié.

Par conséquent, bien qu'il puisse exister dans la base de données en cours une procédure stockée créée par l'utilisateur ayant le préfixe sp_, la base de données master est toujours analysée la première, même si la procédure stockée est qualifiée avec le nom de la base de données.

Informations sur les procédures stockées

Pour afficher le texte utilisé pour créer la procédure, exécutez sp_helptextdans la base de données dans laquelle la procédure se trouve en passant le nom de la procédure en paramètre.
Pour obtenir une liste des objets référencés par une procédure, utilisez sp_depends.
Pour renommer une procédure, utilisez sp_rename

Important  Si une procédure stockée créée par un utilisateur porte le même nom qu'une procédure stockée système, celle de l'utilisateur ne s'exécutera jamais.1.1.Procédures stockées temporairesLes procédures stockées temporaires privées et globales, comme les tables temporaires, peuvent être créées en ajoutant les préfixes # et # # à leur nom. # désigne une procédure stockée temporaire locale, et # #, une procédure stockée temporaire globale. Ces procédures n'existent plus après l'arrêt de SQL Server.Les procédures stockées temporaires locales sont disponibles au sein d’une seule session d’utilisateur. Tandis que les procédures stockées temporaires globales sur l’ensemble des sessions d’utilisateur.Les procédures stockées temporaires sont utiles lorsque vous vous connectez à des versions antérieures de SQL Server qui ne prennent pas en charge la réutilisation des plans d'exécution des instructions ou lots d'instructions Transact-SQL.
Pour créer et exécuter les procédures stockées temporaires :
Create procedure # #procedure_nameAssql_statementExec sp_executesql# #procedure_name

1.2.Procédures stockées distantes

Les procédures stockées distantes sont une ancienne fonctionnalité de Microsoft® SQL Server™ 2000. Leur fonctionnalité dans Transact-SQL est limitée à l'exécution d'une procédure stockée sur une installation SQL Server distante. Les requêtes distribuées introduites dans la version 7.0 de SQL Server prennent en charge cette possibilité ainsi que l'accès à des tables dans des sources de données OLE DB hétérogènes directement à partir d'instructions Transact-SQL locales. Au lieu d'utiliser un appel de procédure stockée distante sur SQL Server 2000, utilisez des requêtes distribuées et une instruction EXECUTE pour exécuter une procédure stockée sur un serveur distant.Une instance SQL Server 2000 peut envoyer et recevoir des appels de procédures stockées distantes à d'autres instances de SQL Server 2000 et SQL Server version 7.0. Une instance SQL Server 2000 peut également envoyer des appels de procédures stockées distantes vers des instances SQL Server 6.0 ou 6.5 et en recevoir. Un serveur exécutant SQL Server 2000 peut recevoir des appels de procédures stockées distantes d'une instance SQL Server 4.21a, mais l'instance SQL Server 2000 ne peut pas faire des appels de procédures stockées distantes à l'instance SQL Server 4.21a. L'instance SQL Server 4.21a ne peut pas reconnaître la version du flux de données tabulaires (TDS, Tabular Data Stream) utilisée par SQL Server 2000.
1.1.Procédures stockées étenduesLes procédures stockées étendues vous permettent de créer vos propres routines externes dans un langage de programmation comme le langage C. Les procédures stockées étendues apparaissent aux utilisateurs comme des procédures stockées normales et s'exécutent de la même façon. Des paramètres peuvent être passés à une procédure stockée étendue pour renvoyer des résultats et un état. Les procédures stockées étendues permettent d'étendre les fonctionnalités de Microsoft® SQL Server™ 2000.Les procédures stockées étendues sont des bibliothèques de liaison dynamique (DLL, dynamic-link library) que SQL Server peut charger et exécuter dynamiquement. Elles s'exécutent directement dans l'espace d'adresse de SQL Server et sont programmées au moyen de l'API Open Data Services de SQL Server.Une fois que la procédure stockée étendue est écrite, les membres du rôle de serveur fixe sysadmin peuvent l'inscrire dans SQL Server, puis donner l'autorisation de l'exécuter à d'autres utilisateurs. Les procédures stockées étendues ne peuvent être ajoutées qu'à la base de données master. Les procédures stockées étendues sont généralement identifiées par le préfixe xp_  2.Codification des procédures stockée3.1. Création des procédures stockéesVous pouvez créer une procédure stockée en utilisant l'instruction Transact-SQL CREATE PROCEDURE. Lisez les informations ci-dessous avant de créer une procédure stockée.

• L'instruction CREATE PROCEDURE ne peut pas s'utiliser conjointement avec d'autres instructions SQL dans un même lot d'instructions.
• L'autorisation de créer des procédures stockées revient par défaut au propriétaire de la base de données, qui peut la transmettre à d'autres utilisateurs.
• Les procédures stockées sont des objets de base de données et leur nom doit respecter les règles gouvernant les identificateurs.
• Vous ne pouvez créer une procédure stockée que dans la base de données en cours.

Pour créer une procédure stockée, vous devez préciser :

• es paramètres d'entrée et de sortie de la procédure ou du lot appelant ;
• les instructions de programmation qui exécutent les opérations dans la base de données, y compris l'appel à d'autres procédures ;
• la valeur d'état renvoyée à la procédure ou au lot appelant pour indiquer la réussite ou l'échec et, dans ce cas, la raison de l'échec.

Syntaxe

CREATE PROC [ EDURE ] procedure_name [ ; number ]
  { @parameter data_type }
 AS sql_statement [ ...n ]

Arguments

procedure_name
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.;numberNombre 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 abandonne le groupe tout entier. @parameter
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.data_type
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 les actions que peut entreprendre la procédure.
sql_statement
Tout numéro et type d'instructions Transact-SQL à inclure dans la procédure. Certaines limitations s'appliquent.n
Espace réservé qui indique que plusieurs instructions Transact-SQL peuvent être incluses dans cette procédure.3.2. Modification des procédures stockéesPour modifier une procédure stockée : ALTER PROC [ EDURE ] procedure_name [ ; number ]
   { @parameter data_type }
AS
  sql_statement [ ...n ]

3.3. Suppression des procédures stockéesPour supprimer une procédure stockée : Drop proc[edure] procedure_name  3.4. Appel d’une procédure stockéeUtilisez 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] procedure_name @parameter 1= value1,@parameter2 = value2,@parameter3 = value3…Des valeurs de paramètres peuvent être fournies si une procédure stockée a été écrite pour les accepter.Remarque  Si vous entrez des paramètres sous la forme @Parameter = value, leur ordre n'a pas d'importance. Vous pouvez aussi omettre les paramètres pour lesquels une valeur par défaut a été définie. Si vous spécifiez un paramètre donné sous la forme @Parameter = value, vous devez tous les spécifier de cette façon. Sinon, ils doivent apparaître dans l'ordre indiqué par l'instruction CREATE PROCEDURE.Lorsque le serveur exécute une procédure stockée, il refuse tous les paramètres qui n'étaient pas insérés dans la liste des paramètres au moment de la création de la procédure. Tout paramètre qui est passé par référence (en fournissant explicitement son nom) ne sera pas accepté si son nom ne concorde pas.Bien que vous puissiez omettre des paramètres ayant des valeurs par défaut, seule la liste des paramètres peut être tronquée. Par exemple, si une procédure stockée a cinq paramètres, vous pouvez omettre les deux derniers paramètres, mais pas omettre le quatrième et inclure le cinquième, à moins d'utiliser le format @parameter = value.La valeur par défaut d'un paramètre, si elle a été définie dans la procédure stockée, est utilisée dans les cas suivants :

• si aucune valeur n'est spécifiée pour le paramètre au moment de l'exécution de la procédure ;
• si le mot clé DEFAULT est spécifié comme valeur du paramètre.

Pour exécuter une procédure stockée qui est groupée avec d'autres procédures du même nom, indiquez le numéro d'identification de la procédure à l'intérieur du groupe. Par exemple, pour exécuter la seconde procédure stockée du groupe my_proc, spécifiez :

EXECUTE my_proc;2

Pour faire appel à une procédure stockée on utilise le mot call {call procedure_name(@parameter….)}{call  "DataBase"."Owner"."Procedure_name" }3.5. Définition des paramètres, traitement des erreurs   les procédures ne seraient pas intéressantes si on ne pouvait pas spécifier de paramètres. Heureusement, il est très facile d’écrire une procédure stockée paramétrable.Pour déclarer un paramètre, il suffit donc de le spécifier dans l’entête de la procédure en lui indiquant :·   son nom : @Parameter (n’oubliez pas le @), qui sera utilisable comme une variable dans la procédure stockée.·   Un type de donnée, choisi parmi les types SQL ou les types utilisateurs.·   Une valeur par défaut optionnelle.·   Une direction, en mettant OUTPUT derrière le nom d’un paramètre.3.4.1 Paramètres optionnelsVous pouvez  spécifier pour chaque paramètre une valeur par défaut.3.4.2 Direction des paramètresVous pouvez également définir des paramètres de sortie. Ces paramètres sont modifiés dans la procédure stockée puis retournés à l’appelant.
3.6.Utilisation de NOCOUNT, EXISTSNOCOUNT :
Empêche le message indiquant le nombre de lignes affectées par une instruction Transact-SQL d'être renvoyé en tant que résultat.

Syntaxe

SET NOCOUNT {ON | OFF}

Notes

Si SET NOCOUNT est activée (ON), le chiffre indiquant le nombre de lignes affectées par une instruction Transact-SQL n'est pas renvoyé. Si la valeur de SET NOCOUNT est définie sur OFF, ce chiffre est renvoyé.EXISTS :Précise une sous-requête pour déterminer l'existence ou non de lignes.   **************
Dans les exemples et les exercices qui suivent, on travaille sur la base de données«GestionCommande »:

1.Exemples ----Créationd'une procédure stockée simpleCreate procedure PS;1asselect * from Clientwhere IdClient>1650Exec PS;1 --------------------------------- ----Création d'une procédure avec un seul paramètrecreate proc myprocedure@d datetimeasselect * from Facture where DateFacture=@dexec myprocedure '08/09/2002'create proc PS;2(@Id int)asselect IdClient,Nom,Prénom from Client where IdClient=@IdExec PS;2 @Id=1668--------------------------------- ----Création d'une procédure avec deux ou plusieurs paramètrescreate proc PS;3(@n varchar(20),@p varchar(20))asselect * from Client where Nom=@n and Prénom=@pExec PS;3 'Alaoui','Ahmed'--------------------------------- ----Procédure qui a un paramètre optionnelCreate proc PS;4(@Id int =null)As If @Id is null   Begin   Select * from Client   EndElse Begin    Select * from Client where IdClient=@Id   EndGoexec PS;4 @Id=1925exec PS;4 1668exec PS;4exec PS;4 @Id=default--------------------------------- ----On crée une procédure stockée porte le même nom d'une procédure stockée système "sp_depends"create procedure sp_depends@n intasselect * from Commande where NumCommande=@nexec sp_depends 'Client'--------------------------------- ----Procédure Stockée temporaire globalecreate procedure ##ProcGlobaleasselect * from Client where IdClient=1668exec sp_executesql ##ProcGlobale---------------------------------- ----Procédure Stockée temporaire localecreate procedure #ProcLocaleasselect distinct * from Commandeexec sp_executesql #ProcLocale--------------------------------- ----Appel d'une procédure stockée{call "GestionCommande"."dbo"."PS;1"}{call PS;1}{call PS;3 ('alaoui','ahmed')}{call myprocedure ('08/09/2002')}--------------------------------- -------Procédure avec plusieurs commandes SQLcreate proc procédureMasbeginselect * from Clientendbegininsert into Client values(3334,'Tourabi','Amina','Casa')end-----appel de la procédure{call procédureM}-------------------------------------- ------Modification d'une Procédure stockéealter proc procédureMasselect * from Commande-------------------------------------- -------Suppression d'une Procédure stockéedrop proc procédureM-------------------------------------- --------Gestion des erreurs-----------Create procedure pp@aa intasif @aa>0select * from client where IdClient=@aaelse print'Attention le IdClient que vous avez entrer n''est pas correct!'Exec pp -55--------------------------------------1.Exercices Exercice 1 :Créer une procédure stockée qui affiche les clients dont la quantité commande est supérieur à 75 et les factures sont réalisées entre 2003 et 2004 Corrigé :create proc E;1asselect * from Client where exists(select * from Commandewhere IdClient=Client.IdClient and Quntité>75 and exists(select * from Facturewhere datefacture between '01/01/2003'and '31/12/2004'and NumCommande=Commande.NumCommande ))exec E;1 Exercice 2 :Créer une procédure stockée qui retourne la somme des prix à payer par tous les clients en utilisant un paramètre de sortie.Corrigé :create proc E;2@somme money outputas         select @somme = sum(PrixTotal)   from Commandeif @somme < 1000    print 'La société va fermer ses portes.'else   ---SELECT 'La société a réalisé ' + convert (varchar(20), @somme)+' F' as PrixTotalselect @somme as SommeRéaliségodeclare @P money exec E;2  @P output Exercice3 :Créer une procédure qui affiche les noms et les prénoms des clients dont le nom commence par ‘Al’ en utilisant un cursor qui permet d’extraire les lignes ligne par ligneCorrigé :create proc ProcCursorasDECLARE Cur CURSOR FORSELECT Nom, Prénom FROM Client where Nom like 'Al%'OPEN CurFETCH NEXT FROM CurWHILE @@FETCH_STATUS = 0BEGINinsert into client values(2056,'toto','titi','Mars')    FETCH NEXT FROM CurENDCLOSE Curdrop proc ProcCursorexec ProcCursor Exercice 4 :Créer une procédure qui exécute la procédure précédenteCorrigé :create proc ProcAppelante@P1 varchar(10)asexec @P1---------exec ProcAppelante 'ProcCursor'Exercice 5 :Créer la procédure stockée qui compte le nombre de commandes d’un client et affiche le résultat Corrigé :create proc prc(@id int,@var int output)as      select @var = count(NumCommande)   from Commande where IdClient=@idgroup by IdClientdrop proc prcdeclare @P int Exec prc 1578,@P output Select @p as NbreCommandes