Exercice 2 : Procédures Stockées Sql + Correction

Voici un  autre exercice pour SQL SERVER , qui concerne les Procédures Stockées ( Procédures , Fonctions et Triggers : Déclencheurs ) + Corréction .

Exercice :

Correction :

create database Coupe_du_monde

go

use Coupe_du_monde

go

create table Equipe

(

CodeEquipe int primary key,

Nationalite varchar(25)

)

create table Stade

(

CodeStade int primary key,

NomStade varchar(25),

NbPlaces int

)

create table Individu

(

NoIndividu int primary key,

Nom varchar(25),

Prenom varchar(25),

CodeEquipe int foreign key references Equipe(CodeEquipe)

)

create table Match

(

IdMatch int primary key,

NbSpectateur int,

Date date,

NoIndividu int foreign key references Individu(NoIndividu),

CodeStade int foreign key references Stade(CodeStade)

)

create table Jouer

(

IdMatch int foreign key references Match(IdMatch),

CodeEquipe int foreign key references Equipe(CodeEquipe),

primary key(IdMatch,CodeEquipe)

)

create table But

(

IdBut int primary key identity(0,1),

Minute int,

Type varchar(25),

IdMatch int foreign key references Match(IdMatch),

NoIndividu int foreign key references Individu(NoIndividu)

)

insert into Equipe values(0,'Maroc')

insert into Equipe values(1,'Tunisie')

insert into Equipe values(2,'Camerone')

insert into Equipe values(3,'Manga')

insert into Stade values(0,'Amerigo',1000)

insert into Stade values(1,'Orange',1000)

insert into Stade values(2,'Pricipaux',1000)

insert into Individu values(0,'N1','P1',0)

insert into Individu values(1,'N2','P2',0)

insert into Individu values(2,'N3','P3',0)

insert into Individu values(3,'N4','P4',0)

insert into Individu values(4,'N5','P5',0)

insert into Individu values(5,'N6','P6',0)

insert into Individu values(6,'N7','P7',0)

insert into Individu values(7,'N8','P8',1)

insert into Individu values(8,'N9','P9',1)

insert into Individu values(9,'N10','P10',1)

insert into Individu values(10,'N11','P11',1)

insert into Individu values(11,'N12','P12',1)

insert into Individu values(12,'N13','P13',1)

insert into Individu values(13,'N14','P14',1)

insert into Individu values(14,'NA','PA',null)

insert into Individu values(15,'NA2','PA2',null)

insert into Match values(0,200,'23/07/2003',14,0)

insert into Match values(1,200,'23/07/2003',14,1)

insert into Match values(2,200,'23/07/2003',15,1)

insert into Jouer values(0,0)

insert into Jouer values(0,1)

insert into Jouer values(2,1)

insert into Jouer values(2,2)

insert into But values(15,'Tir',0,1)

insert into But values(23,'Diving header',0,2)

insert into But values(44,'Freestyle',0,3)

insert into But values(79,'Long rage',0,7)

insert into But values(90,'Cross',0,9)

insert into But values(17,'Tir',0,1)

1) Les Procédures :

/*1*/

create proc p1

as

select Nationalite from Equipe

/*2*/

create proc p2

as

select i.* from Match m,Individu i

where i.NoIndividu=m.NoIndividu

/*3*/

create proc p3(@N int)

as

select s.* from Match m,Stade s

where m.CodeStade=s.CodeStade and NbSpectateur>@N

/*4*/

create proc p4

as

select m.IdMatch,NbPlaces-NbSpectateur as 'Places vacantes' from Match m,Stade s

where m.CodeStade=s.CodeStade

group by m.IdMatch,NbPlaces,NbSpectateur

/*5*/

create proc p5

as

select i.* from Individu i,Equipe e

where i.CodeEquipe=e.CodeEquipe and e.Nationalite='Maroc'

/*6*/

create proc p6(@CodeEquipe int)

as

select distinct i.* from Match m,Individu i,Jouer j

where m.NoIndividu=i.NoIndividu and m.IdMatch=j.IdMatch and j.CodeEquipe=@CodeEquipe

order by i.Nom asc

/*7*/

create proc p7(@N int,@type varchar(25))

as

select i.*,COUNT(*) as [NbrButs] from Individu i,But b

where i.NoIndividu=b.NoIndividu and Type=@type

group by i.NoIndividu,i.Nom,i.Prenom,i.CodeEquipe

having COUNT(*)>=@N

/*8*/

create view v8

as

select i.NoIndividu,COUNT(*) as [NbrButs] from Individu i,But b

where i.NoIndividu=b.NoIndividu

group by i.NoIndividu

create proc p8(@CodeEquipe int)

as

select i.CodeEquipe,SUM(NbrButs) as[NbrButs] from v8,Individu i

where i.NoIndividu=v8.NoIndividu and i.CodeEquipe=@CodeEquipe

group by i.CodeEquipe

/*9*/

create view v9

as

select i.NoIndividu,COUNT(*) as [ButsMarque] from Individu i,But b,Equipe e

where i.NoIndividu=b.NoIndividu and e.CodeEquipe=i.CodeEquipe and Nationalite='Maroc'

group by i.NoIndividu 

create proc p9

as

select i.*,ButsMarque from Individu i,v9

where i.NoIndividu=v9.NoIndividu and ButsMarque=(select MAX(ButsMarque) from v9)

/*10*/

create proc p10

as

declare @NoIndividu int

declare curseur CURSOR

for select i.NoIndividu from Individu i,Equipe e

where i.CodeEquipe=e.CodeEquipe and e.Nationalite='Maroc'

open curseur

fetch curseur into @NoIndividu

while(@@FETCH_STATUS=0)

begin

insert into But(NoIndividu) values(@NoIndividu)

insert into But(NoIndividu) values(@NoIndividu)

fetch curseur into @NoIndividu

end

close curseur

deallocate curseur

2)Les Fonctions :

/*1*/

create function f1(@CodeS int)
returns varchar(200)
as
begin
declare @chaine varchar(200)
select @chaine= NomStade +' '+convert(varchar,count(*))  from Stade s,Match m
where s.CodeStade=m.CodeStade and m.CodeStade=@CodeS
group by NomStade
return @chaine
end

/*2*/
create function f2(@Nom varchar(25))
returns varchar(8000)
as
begin
declare @chaine varchar(8000)
declare @idMatch int
set @chaine='Matches Arbitré : '
declare curseur cursor
for select idMatch from Match m,Individu i where m.NoIndividu=i.NoIndividu and i.Nom=@Nom
open curseur
fetch curseur into @idMatch
while(@@Fetch_status=0)
begin
set @chaine+=convert(varchar,@idMatch)+' '
fetch curseur into @idMatch
end
close curseur
deallocate curseur
return @chaine
end


3) Les Triggers :

/*1*/
create trigger t1
on Individu
instead of insert
as
insert into Individu select NoIndividu,Upper(Nom),UPPER(SUBSTRING(Prenom,1,1))+LOWER(SUBSTRING(Prenom,2,len(Prenom)-1)),CodeEquipe from Inserted


/*2*/
create trigger t2
on Match
instead of insert
as
declare @spect int,@places int
select @spect=NbSpectateur from Inserted
select @places=NbPlaces from Stade s,Inserted i where i.CodeStade=s.CodeStade
if(@spect<=@places)
insert into Match select * from Inserted
else
Raiserror('Nombre de spectateurs superieur',0,1)


/*3*/
create trigger t3
on But
instead of insert
as
declare @minute int
select @minute=Minute from Inserted
if(@minute>=0)
insert into But select * from Inserted
else
raiserror('Valeur de minute negative',0,1)


/*4*/
create trigger t4
on Individu
instead of delete
as
delete from But where NoIndividu in(select NoIndividu from deleted)
delete from Individu where NoIndividu in(select NoIndividu from deleted)


/*5*/
create trigger t5
on Match
instead of update
as
if(Update(Date))
raiserror('La modifiaction de date est interdite',0,1)
else
update Match set NbSpectateur=Inserted.NbSpectateur,NoIndividu=Inserted.NoIndividu from Inserted where Match.IdMatch=Inserted.IdMatch



/*6*/
create trigger t6
on Equipe
instead of delete
as
declare @CE int
select @CE=CodeEquipe from deleted
delete from Individu where CodeEquipe=@CE
delete from Equipe where CodeEquipe=@CE


/*7*/

/*8*/
create trigger t8
on Equipe
for insert
as
declare @nat varchar(25)
select @nat=Nationalite from inserted
if(@nat like 'fr')
set @nat='France'
else if(@nat like 'ma')
set @nat='Maroc'
else if(@nat like 'br')
set @nat='Brésil'
else if(@nat like 'Esp')
set @nat='Espagne'
update Equipe set Nationalite=@nat where CodeEquipe in(select CodeEquipe from inserted)



/*OR*/

create function Nationalite(@CH varchar(25))
Returns varchar(25)
as
begin
declare @n varchar(25)
set @n=case @CH
when 'fr' then 'France'
when 'br' then 'Brésil'
when 'ma' then 'Maroc'
when 'esp' then 'Espagne'
end
return @n
end

create trigger t8
on Equipe
for insert
as
declare @r varchar(25)
declare @n varchar(25)
select @n=Nationalite from Inserted
set @r= dbo.Nationalite(@n)
update Equipe set Nationalite=@r where CodeEquipe in(select CodeEquipe from inserted)