Les procédures stockées SQL-Server - Oracle - exercices-corrigés
1. Définition et avantages des
procédures stockées
Une 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 [ OF column_name [ ,...n ] ] } ]
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
Avantages des procédures stocké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.
Ensemble 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'administration.
Nombre 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 :
- elle existe dans la base de
données master ;
- ensuite, en fonction des
éventuels identificateurs fournis (nom de base de données ou propriétaire)
;
- enfin, avec dbo
comme propriétaire si aucun propriétaire n'est spécifié.
Informations sur les procédures stockées
Pour afficher le texte utilisé pour créer la procédure, exécutez sp_helptext
dans 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.
Les 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.
Pour créer et exécuter les procédures stockées temporaires :
Create procedure # #procedure_name
As
sql_statement
Exec sp_executesql
# #procedure_name
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.
Les 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_
- 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 ]
{ @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.
;number
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 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.
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.
sql_statement
Tout numéro et type d'instructions Transact-SQL à inclure dans
la procédure. Certaines limitations s'appliquent.
Espace réservé qui indique que plusieurs instructions
Transact-SQL peuvent être incluses dans cette procédure.
Pour modifier une procédure stockée :
ALTER PROC [ EDURE ] procedure_name [ ; number
]
{ @parameter data_type }
AS
sql_statement [ ...n ]
{ @parameter data_type }
AS
sql_statement [ ...n ]
Pour supprimer une procédure stockée :
Drop
proc[edure] procedure_name
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" }
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 optionnels
Vous pouvez spécifier
pour chaque paramètre une valeur par défaut.
3.4.2 Direction
des paramètres
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éation d'une procédure stockée simple
Create procedure PS;1
as
select * from Client
where IdClient>1650
Exec PS;1
---------------------------------
----Création d'une procédure avec un
seul paramètre
create proc myprocedure
@d datetime
as
select * from Facture where DateFacture=@d
exec myprocedure '08/09/2002'
create proc PS;2
(@Id int)
as
select IdClient,Nom,Prénom from
Client where IdClient=@Id
Exec PS;2 @Id=1668
---------------------------------
----Création d'une procédure avec
deux ou plusieurs paramètres
create proc PS;3
(@n varchar(20),
@p varchar(20))
as
select * from Client where Nom=@n
and Prénom=@p
Exec PS;3 'Alaoui','Ahmed'
---------------------------------
----Procédure qui a un paramètre
optionnel
Create proc PS;4
(@Id int =null)
As
If @Id is null
Begin
Select
* from Client
End
Else Begin
Select
* from Client where IdClient=@Id
End
Go
exec PS;4 @Id=1925
exec PS;4 1668
exec PS;4
exec 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 int
as
select * from Commande where
NumCommande=@n
exec sp_depends 'Client'
---------------------------------
----Procédure Stockée temporaire
globale
create procedure ##ProcGlobale
as
select * from Client where
IdClient=1668
exec sp_executesql ##ProcGlobale
----------------------------------
----Procédure Stockée temporaire
locale
create procedure #ProcLocale
as
select distinct * from Commande
exec 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 SQL
create proc procédureM
as
begin
select * from Client
end
begin
insert into Client
values(3334,'Tourabi','Amina','Casa')
end
-----appel de la procédure
{call procédureM}
--------------------------------------
------Modification d'une Procédure
stockée
alter proc procédureM
as
select * from Commande
--------------------------------------
-------Suppression d'une Procédure
stockée
drop proc procédureM
--------------------------------------
--------Gestion
des erreurs-----------
Create
procedure pp
@aa int
as
if @aa>0
select * from client where
IdClient=@aa
else
print'Attention
le IdClient que vous avez entrer n''est pas correct!'
Exec pp -55
--------------------------------------
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;1
as
select * from Client where
exists
(select * from Commande
where
IdClient=Client.IdClient and Quntité>75 and exists
(select * from Facture
where 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 output
as
select @somme = sum(PrixTotal)
from
Commande
if @somme < 1000
print 'La société va fermer ses portes.'
else
---SELECT 'La société a réalisé ' + convert (varchar(20),
@somme)+' F' as PrixTotal
select @somme as SommeRéalisé
go
declare @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 ligne
Corrigé :
create proc ProcCursor
as
DECLARE Cur CURSOR FOR
SELECT Nom, Prénom FROM Client where Nom like
'Al%'
OPEN Cur
FETCH NEXT FROM Cur
WHILE @@FETCH_STATUS = 0
BEGIN
insert into client
values(2056,'toto','titi','Mars')
FETCH
NEXT FROM Cur
END
CLOSE Cur
drop proc ProcCursor
exec ProcCursor
Exercice
4 :
Créer une procédure qui
exécute la procédure précédente
Corrigé :
create proc ProcAppelante
@P1 varchar(10)
as
exec @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=@id
group by IdClient
drop proc prc
declare @P int
Exec prc 1578,@P output
Select @p as NbreCommandes
merci beaucoup
RépondreSupprimer