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