PROCEDURES STOCKEES - Exercices Corrigés procedure stocké - CallableStatement SQL SERVER


I- DEFINITON
Une procédure stockée (stored procédure) est un ensemble d'instructions T_Sql précompilées, stockées sur le serveur, directement avec la base de données.

Une procédure stockée peut être exécutée sur demande : lancée par un utilisateur, un administrateur (DBA) ou encore de façon automatisée par un événement déclencheur (Trigger).

Les procédures stockées peuvent:
·        Recevoir et envoyer des paramètres à une procédure appelante.
·        Appeler d'autres procédures.
·        Renvoyer une valeur de sortie à la procédure appelante.




I- intérêt
·        simplicité     : code plus simple à comprendre.
·        Rapidité       : moins d'informations, sont chargées entre le serveur et le client.
·        Performance : économise au serveur l'interprétation de la requête car elle est précompilée.
·         Sécurité      : les applications et les utilisateurs n'ont aucun accès direct aux tables, mais passent par des procédures stockées prédéfinies.

II-       Création d'une procédure
1- En utilisant Entreprise Manager

- Cliquer avec le bouton droit sur "Procédures stockées"
- Nouvelle procédure stockée.

Create Procedure Nom_Procedure
As
Begin
   
      --Ecrire le code T_Sql

End
- OK

Exemple:

Create procedure P1 As
Begin
  Select * from FILM
  Select * from Acteur
end

2- En utilisant "Analyseur de requêtes"

Instruction : create procedure

Create Procedure Nom_Procedure
As
Begin
   
      -- code T_Sql

End

3- Exécution d'une procédure stockée

Une procedure stockée peut être appelée à partir de:

-      Une application externe (cliente)
-      Une autre procedure stockée.
-      Analyseur de requête.

  Execute Nom_Procedure
Ou
  Exec Nom_Procedure
Ou
  Nom_Procedure

4- Exemple
Create proc Test As
Begin
   Declare @m decimal
   Select @m=max(FILMDUR) From Film
   Select FILMTIT, REACOD, FILMDUR
     From Film
       Where FILMDUR=@m
End

III-     Procédures stockées avec paramètres

1.   procédures stockées avec paramètres d'entrée.

a.   Procédures avec paramètres d'entrée Obligatoires

Create Procedure Nom_Procedure @parametre1 type,
                                @parametre2 type,    
                                @parametre3 type,    
                                @parametren type    
As
Begin
   
      -- code T_Sql

End

Exemple 1:

Ecrire une procedure stockée qui permet d'afficher la liste des films d'une catégorie donnée à une année donnée.

create proc ListeFilm @cat char(1), @an int
As
begin
   select * from film
    where catcod=@cat and filman=@an
end

Exécution:

exec ListeFilm            -- erreur paramètre manquant
exec ListeFilm 'A'        -- erreur paramètre manquant
exec ListeFilm 1964,'A'   -- erreur ordre de paramètres
-- non respecté

exec ListeFilm @an=1964,@cat='A' --OK

exec ListeFilm 'A', 1964         --OK

Exemple 2:

Ecrire une procedure stockée qui permet d'afficher le code, le nom, le prénom et le nombre de films des réalisateurs qui ont réalisé un nombre de films supérieur ou égale à un nombre donné.

create proc listerealisa @nb int
as
begin
   select realisa.reacod,reanom,reapre, count(filmcod) [Nombre Films]
   from film , realisa
   where film.reacod=realisa.reacod
   group by realisa.reacod,reanom,reapre
   having count(filmcod)>@nb
end


Exemple 3:

Ecrire une procédure stockée qui permet d'afficher les codes des acteurs qui ont joué avec un acteur dont le code est donné.

create proc ActeurAmis1 @codeActeur int
As
begin
select distinct actcod
  from Joue
  where Filmcod in (select filmcod from joue where actcod=@codeActeur)
  and actcod!=@codeActeur

end

Exécution
ActeurAmis1 627
Actcod
45
398
669

Exemple 4:

Ecrire une procedure stockée qui permet la liste des acteurs qui ont joué avec un acteur dont le nom est donné.

….

b.   Procédures avec paramètres d'entrée facultatifs (optionnels)

Syntaxe:
Create Procedure Nom_Procedure @parametre1 type=valeur,
                                 @parametre2 type=valeur    
                               
As
Begin
   
      -- code T_Sql

End


Exemples

Exemple1:

Ecrire une procédure stockée qui affiche la liste des films qui commencent par des caractères donnés. Si ces caractères ne sont pas donnés, la procédure affiche la liste de tous les films.



Create proc Liste_Film2  @car  varchar(50)='%'
As
Begin
   Select * from Film
   Where FILMTIT like @car + '%'
End

Exec Liste_Film2 'An'       : affiche tous les films qui commencent par 'An'

Exec Liste_Film2              : affiche tous les films

Autre version de l'exemple 1:

Create proc Liste_Film2  @car  varchar(50)=NULL
As
Begin
   If (@car is NULL)
      Select * from FILM
    else
       Select * from Film where FILMTIT like @car + '%'
End

Exec Liste_Film2 'An'       : affiche tous les films qui commencent par 'An'

Exec Liste_Film2              : affiche tous les films



  Exemple2:

Ecrire une procédure stockée qui affiche la liste des films réalisés par un réalisateur dont le code ou le nom sont donnés. Si les deux paramètres sont omis, la procédure affichera le message suivant:

"Manque du nom ou du code de réalisateur"

Si le réalisateur n'existe pas, la procédure affichera : "Réalisateur non existant"

create proc films_realisa @code int=NULL, @nom varchar(50)=NULL
AS
begin
  if((@code is NULL) and (@nom is NULL))
    print 'Manque du nom ou du code de réalisateur'

  else
     if(@code is NULL)
          if exists (select reanom from realisa where reanom=@nom)
             select * from film, realisa where film.reacod=realisa.reacod
                                          and   reanom=@nom
          else
             print 'Nom de realisateur non existant'
     else
          if exists (select reacod from realisa where reacod=@code)
             select * from film, realisa where film.reacod=realisa.reacod
                                          and   realisa.reacod=@code
         else
             print 'code de realisateur non existant'
end

exec films_realisa                            -- Manque du code ou nom realisateur
exec films_realisa 30                      -- Liste des films du réalisateur ayant le code=30
exec films_realisa @nom='Ford'   -- Liste des films du réalisateur ayant le nom=Ford
exec films_realisa @nom='Farid'  -- Nom réalisateur non existant
exec films_realisa 71,'Ford'           -- Liste des films du réalisateur ayant le code=71
exec films_realisa 300                    -- code réalisateur non existant



Remarque:

Dans une procédure stockée, on peut trouver des paramètres d'entrées obligatoires et autres facultatifs. Dans ce cas, il faut déclarer les paramètres obligatoires en premier, suivis des paramètres facultatifs.

Exemple
Create proc Test @code int , @d int=0
As
Begin
   Print @code
   Print @d
End

Execute test                                             --erreur
Execute test   67                                               --juste
Execute test   67         ,90                                --juste
Execute test   @d=90                             --erreur
Execute test   @d=90,@code=67                  --juste

2-Procédures avec paramètres de sortie

L'option output derrière un paramètre indique un paramètre en sortie qui est renvoyé à la procédure appelante.

Exemple:

--procédure appelée

create proc liste_film_5 @nom varchar(50)='%',
                        @total int output  --parm de sortie
As
Begin
    select filmtit,reanom
    from film,realisa
    where film.reacod=realisa.reacod
    and reanom like @nom

    select @total=count(FILMTIT)
    from FILM,realisa
    where film.reacod=realisa.reacod
    and reanom like @nom
End

--procédure appelante

create proc Test_liste_film @name varchar(50)='%'
as
begin
    declare @T int
    exec liste_film_5 @name, @T output
    print 'Nombre de films='  + convert(varchar,@T)
end

--execution
exec Test_liste_film 'borderie'  : affiche la liste des film de 'borderie'  et affiche le message : Nombre de films=3

Remarque:

Dans une procédure stockée, on peut retourner une valeur par l'instruction return.

Exemple:

--procédure appelée

create proc liste_film_6 @nom varchar(50)='%'
as
begin
    declare @total int
    select filmtit,reanom
    from film,realisa
    where film.reacod=realisa.reacod
    and reanom like @nom

    select @total=count(FILMTIT)
    from FILM,realisa
    where film.reacod=realisa.reacod
    and reanom like @nom

    return @total
end

--procédure appelante

create proc Test_liste_film_6 @name varchar(50)='%'
as
begin
    declare @T int
    exec @T=liste_film_6 @name 
    print 'Nombre de films='  + convert(varchar,@T)
end

--execution
exec Test_liste_film_6 'borderie'



IV-    Fonctions définies par l'utilisateur

1-Syntaxe

Create Function Nom_Fonction (@parm1 type, @parm2 type,…)
                 returns typeresultat
  as
  begin
     instructions T_sql
    return resultat
 
 end
 
Appel de la fonction

Dbo.Nom_Fonction(paramètres)

1-Exemple

Function qui calcule 1+2+3+4+…..+N

CREATE FUNCTION sigma(@N int) 
RETURNS int AS 
BEGIN

  declare @i int
  declare @s int
  set @i=0
  set @s=0
  while(@i<@N)
  begin
   set @i=@i+1;
   set @s=@s+@i;
  end
  
  return @s;

END
Go

--Appel de la fonction
select dbo.sigma(3) as Sigma
-------------------------------------------------------------------

Exercices Corrigés - TP avec Solution SQL SERVER PROCEDURES STOCKEES


Article plus récent Article plus ancien

Leave a Reply

Telechargement