Correction exercice procedures stockées

Corrigé :

Exercice 1 :

CREATE PROCEDURE PS1 AS

Select NumCostume, DesignationCostume, NomStyliste, AdrStyliste from Styliste, Costume where Styliste.NumStyliste=Costume.NumStyliste

CREATE PROCEDURE PS2 @NumCos int AS

Select  DesignationCostume, NomStyliste, AdrStyliste from Styliste, Costume where Styliste.NumStyliste = Costume.NumStyliste and NumCostume=@NumCos

CREATE PROCEDURE PS3 @NumCos int AS

Select MembreJury.NumMembreJury, NomMembreJury, FonctionMembreJury, NoteAttribuée from MembreJury, Notesjury where MembreJury.numMembreJury= Notesjury.NumMembreJury and NumCostume =@NumCos

CREATE PROCEDURE PS4 @NbrCos int output AS

Set @NbrCos=(select count(NumCostume) from Costume)

CREATE PROCEDURE PS5 @NumCos int, @MJ int , @note decimal output AS

Set @note=(select Noteattribuée from Notesjury where NumCostume =@NumCos and NumMembreJury=@MJ)

CREATE PROCEDURE PS6 @NumCos int , @M decimal output AS

Set @M=(select Avg(NoteAttribuée) from  Notesjury where NumCostume =@NumCos)

Exercice 2 :

CREATE PROCEDURE PS1 AS

Create table ProduitBrut(CodProBrut int primary key,NomProBrut varchar(50), PrixAchat decimal, NumFour int Foreign Key references Fournisseur)

Create table Composition (CodProFini int Foreign key references ProduitFini, CodProBrut int Foreign Key references ProduitBrut, QteUtilisee decimal, Constraint PK_Composition Primary Key(CodProFini, CodProBrut))

CREATE PROCEDURE PS2 AS

Select codProFini, count(CodProBrut) from composition Group by CodProFini

CREATE PROCEDURE PS3 @MaxPrix decimal output AS

Set @MaxPrix =(Select max(PrixAchat) from ProduitBrut)

CREATE PROCEDURE PS4 AS

Select CodProFini from Composition Group by CodProFini

Having Count(CodProBrut)>=2

CREATE PROCEDURE PS5 @ProBrut varchar(50), @RS varchar(50) output AS

Set @RS=(Select RSFour From Fournisseur, ProduitBrut

 Where Fournisseur.NumFour= ProduitBrut.NumFour and NomProBrut=@ProBrut)

CREATE PROCEDURE PS6 @CodProFini int AS

Select * from Mouvement Where TypeMvt='S' and CodProFini=@CodProFini

CREATE PROCEDURE PS7 @CodProFini int, @TypeMvt char(1) AS

Select * from Mouvement Where TypeMvt=@TypeMvt  and CodProFini=@CodProFini

CREATE PROCEDURE PS8 AS

Declare @CPF int, @QteStock decimal

Declare C1 Cursor for select CodProFini, QteEnStock from ProduitFini

open C1

Fetch next from C1 into @CPF, @QteStock

while @@fetch_status=0

      Begin

            Print 'La quantité en stock est :' + convert(varchar, @QteStock)

            Select * from Mouvement where CodProFini=@CPF

            Declare @SommeE decimal, @SommeS decimal

Set @SommeE=(Select Sum(Quantite) from Mouvement where TypeMvt="E" and CodProFini=@CPF)

Set @SommeS=(Select Sum(Quantite) from Mouvement where TypeMvt="S" and CodProFini=@CPF)

            if @SommeE-@SommeE <>@QteStock

                  Print 'Stock OK'

            Else

                  Print 'Problème de Stock'

      Fetch Next from C1 into @CPF, @QteStock

      end

Close C1

Deallocate C1

CREATE PROCEDURE PS9 @CPF int, @PrixReviens decimal output AS

set @PrixReviens=(select Sum(PrixAchat*Qteutilisee) from ProduitBrut PB, Composition C where C.CodProBrut=PB.CodProBrut and CodProFini=@CPF)

CREATE PROCEDURE SP10 AS

Declare @CPF int, @NbrProduitsFinis int

Declare C1 Cursor for select CodProFini, count(CodproBrut) from Composition group by CodProFini

Open C1

Fetch Next from C1 into @CPF, @NbrProduitsFinis

While @@fetch_status=0

Begin

Declare @PR decimal

Exec SP9 @CPF, @PR output

Print 'Le prix de reviens est : ' + convert(varchar, @PR)

Select NomProFini, PrixAchat*Qteutilisee, RSFour From ProduitBrut PB, Composition C Where PB.CodProBrut=C.CodProBrut and CodProFini=@CPF

Print 'Le nombre de produits bruts est : ' + convert(varchar,@NbrProduitsFinis)

Fetch Next from C1 into @CPF, @NbrProduitsFinis

End

Close C1

Deallocate C1

Exercice 3 :

CREATE PROCEDURE PS1 AS

Select NumStagiaire, NomStagiaire From Stagiaire where NumStagiaire not in (Select NumStagiaire from Notation, )

CREATE PROCEDURE PS2 AS

Select Filiere.NumFiliere, NomFiliere from filiere, programme

Where Filiere.numFiliere=Programme.Numfiliere

Group by Filiere.NumFiliere, NomFiliere

Having Count(NumModule)>=10

CREATE PROCEDURE SP3 @CodSecteur varchar(10) AS

Select NomModule from Module M,Programme P, Filiere F

Where M.Nummodule=P.numModule and P.NumFiliere=F.NumFiliere and codSecteur=@CodSecteur

group by NomModule

Having count(F.numfiliere)=(select Count(numfiliere) from filiere where CodSecteur=@CodSecteur)

CREATE PROCEDURE PS4 @NumStagiaire int AS

Select Module.NumModule, NomModule, Note,Coefficient from Module, Notation, programme

Where Notation.NumModule=Module.NumModule and Module.Nummodule=programme.Nummodule and numStagiaire=@NumStagiaire

CREATE PROCEDURE PS5 AS

Declare @NumSta int, @NomPreSta varchar(50), @NomFil varchar(50)

Declare C1 Cursor for Select NumStagiaire, NomStagiaire + ' ' + PrenomStagiaire, NomFiliere from Stagiaire S, Filiere F where S.NumFiliere=F.NumFiliere

Open C1

Fetch Next from C1 into  @NumSta, @NomPreSta, @NomFil

while @@fetch_status=0

Begin

Print 'Nom et Prénom : ' + @NomPreSta + ' Filière : ' + @NomFil

if exists (select NumModule from Programme P, Stagiaire S where P.NumFiliere=S.NumFiliere and NumStagiaire=@NumSta and NumModule not in (select NumModule from notation where NumStagiaire=@NumSta))

Begin

Print 'En cours de traitement'

select NumModule from Programme P, Stagiaire S where P.NumFiliere=S.NumFiliere and NumStagiaire=@NumSta and NumModule not in (select NumModule from notation where NumStagiaire=@NumSta)

                        Fetch Next from C1 into  @NumSta, @NomPreSta,                          @NomFil

                        Continue

end

if (select count(NumModule) from notation where NumStagiaire = @NumSta and note<3) >2

Begin

Print 'Notes Eliminatoires'

Select NomModule from Module M, Notation N where M.NumModule=N.NumModule and numStagiaire=@NumSta and note <3

                        Fetch Next from C1 into  @NumSta, @NomPreSta,                          @NomFil

                        Continue

End

Select NomModule, Coefficient, Note  from Module M, Programme P, Notation N, Stagiaire S  where M.NumModule=P.NumModule and P.numFiliere=S.NumFiliere and M.NumModule=N.NumModule and N.NumStagiaire=S.NumStagiaire and S.numStagiaire=@NumSta

Select Sum(Note*coefficient) / Sum(coefficient)  from Module M, Programme P, Notation N, Stagiaire S  where M.NumModule=P.NumModule and P.numFiliere=S.NumFiliere and M.NumModule=N.NumModule and N.NumStagiaire=S.NumStagiaire and S.numStagiaire=@NumSta

Fetch Next from C1 into  @NumSta, @NomPreSta, @NomFil

End

Close C1

Deallocate C1

Enoncé