Exercice 3 : Procédures Fonctions Triggers Sql + Correction

Voici une autre série d'Exercices Corrigés .

Allons travailler un peu d'exercices de Procédures Stockées , de Fonctions et de Triggers .












Ecercice :

Correction :

/*La création*/

create database Agence
go
use Agence
go
create table Station
(
nomStation varchar(25) primary key,
capacité int,
lieu varchar(25),
region varchar(25),
tarif float
)
create table Client
(
ID int primary key,
nom varchar(25),
prenom varchar(25),
ville varchar(25),
region varchar(25),
solde float
)

create table Activite
(
nomStation varchar(25) foreign key references Station(nomStation),
libelle varchar(25),
primary key(nomStation,libelle),
prix float
)

create table Sejour
(
idClient int foreign key references Client(ID),
station varchar(25) foreign key references Station(nomStation),
debut date,
primary key(idClient,station,debut),
nbPlaces int
)

insert into Station values('Venus',350,'Guadeloupe','Antilles',1200)

insert into Client values(10,'Gogg','Philipes','Londres','Europe',1246.5)
insert into Client values(20,'Pascal','Blais','Paris','Europe',6763)
insert into Client values(30,'Kerouac','Jack','New York','Amérique',9812)

insert into Activite values('Venus','Voiles',150)
insert into Activite values('Venus','Plongee',120)

insert into Sejour values(20,'Venus','03/08/2003',4)


select * from Station
select * from Client
select * from Activite
select * from Sejour


/*1*/
create proc p1(@idClient int)
as
declare @nomC varchar(100)
select @nomC=nom+' '+prenom from Client
print @nomC


Ou

/*1*/
create proc NomClient(@id int)
as
declare @nom varchar(25),@prenom varchar(25)
declare curseur CURSOR for select nom,prenom from Client where ID=@id
open curseur
fetch curseur into @nom,@prenom
while(@@FETCH_STATUS=0)
begin
print @nom+' '+@prenom
fetch curseur into @nom,@prenom
end
close curseur
deallocate curseur

/*2*/
Create function Activities(@nomStation varchar(25))
Returns varchar(8000)
as
begin
declare @i int
set @i=0
declare @chaine varchar(8000)
set @chaine=''
declare @libelle varchar(25)
declare curseur CURSOR for select libelle from Activite where nomStation=@nomStation
open curseur
fetch curseur into @libelle
while(@@FETCH_STATUS=0)
begin
if(@i!=0)
set @chaine+=','
set @chaine+=@libelle
set @i+=1
fetch curseur into @chaine
end
close curseur
deallocate curseur
return @chaine
end

/*3*/
create view v1
as
select *,dbo.Activities(nomStation)as 'Activies' from Station

/*4*/
Create proc Actualiser(@pourcentage int,@nomStation varchar(25))
as
if(@pourcentage<0 or @pourcentage>100)
    print 'Pourcentage invalide!'
else
    begin
    update Station set tarif+=(tarif*(@pourcentage*0.01)) where nomStation=@nomStation
    update Activite set prix+=(prix*(@pourcentage*0.01)) where nomStation=@nomStation
    print 'Modifié'
    end


/*5*/
create trigger t2
on activite
after update
as
declare @dif float
select @dif=d.prix-i.prix from inserted i,deleted d
update Station set tarif+=@dif


/*6*/
/*(a)*/
create table Station
add nbrActivities int not null default 0

/*(b)*/
create trigger t2
on Station
for insert,update,delete
as
declare @nbrActivites int
declare @nomStation varchar(25)
declare curseur    CURSOR for select nomStation from Station
open curseur
fetch curseur into @nomStation
while(@@FETCH_STATUS=0)
begin
    update Station set nbrActivities=(select count(*) from Activite where nomStation=@nomStation) where nomStation=@nomStation
    fetch curseur into @nomStation
end
close curseur
deallocate curseur

/*7*/
create trigger t3
on Sejour
after insert
as
declare @Solde float
declare @NbPlaces int
declare @Tarif float

select @Solde=solde from Client c,Inserted i where i.idClient=c.id
select @NbPlaces=Nbplaces from inserted
select @Tarif=tarif from Station where NomStation in(select NomStation from Inserted)
print @Solde
print @Nbplaces
print @tarif
if((@Tarif*@NbPlaces)>@Solde)
begin
    print 'Opération échoué : Solde inférieur à (tarif*NbPlaces)'
    rollback transaction
end