SGBD 2 TP Fonctions Avec Correction
Produit (IDP, LibP, IDM#, PU_V, Qté)
Marque (IDM, Désignation, NBProd)
Fournir (IDF#, IDP#, Date, Qté, PU_A)
Fournisseur (IDF, RS, Ville, Tél)
1) Réaliser une fonction qui retourne le prix moyen des produits d’une marque donnée
2) Réaliser une fonction qui renvoie la quantité moyenne fournie d’un produit pendant une période
donnée
3) Réaliser une fonction qui retourne le libellé le plus long des produits (composé de plus de
caractère)
4) Réaliser une fonction qui renvoie le libellé et le pu de tous les produits classés par PU croissant,
sans utiliser le tri
5) Sachant que l’IDP représente, pour chaque produit, son classement selon un PU croissant,
Réaliser une fonction qui permet de modifier le PU d’un produit donné en retournant le nouveau
classement des produits.
6) Réaliser une fonction qui permet d’afficher le libellé et le PU, des produits d’une famille donnée,
augmentés ou diminués d’un pourcentage passé en paramètre
7) Réaliser une fonction qui retourne le nombre des produits dont le libellé est écrit en majuscules.
8) Listez pour chaque produit, le libellé, la famille et un champ calculé qui aura pour alias ‘CodP’
et qui sera obtenu comme suit : NBcar_Fam (Avec NBCar représente le nombre de caractères du
libellé et Fam représente la désignation correspondante à sa famille)
9) Réaliser une fonction qui affiche pour tous les produits, le libellé, l’écart entre le PU_A moyen et
le PU_V.
Correction :
create database TP1_Fonction
use TP1_Fonction
create table produit(
idp int primary key,
libp varchar(50),
idm int foreign key references marque(idm),
pu_v float,
qté varchar(50)
)
create table marque(
idm int primary key,
designation varchar(200),
nbprod int
)
insert into fournir values(NULL,1,'2010/1/1',1,100)
create table fournir(
idf int foreign key references fournisseur(idf),
idp int foreign key references produit(idp),
date_f date,
qte int,
pu_a float
primary key(idf,idp,date_f)
)
create table fournisseur(
idf int primary key,
rs varchar(50),
ville varchar(50),
tel varchar(50)
)
insert into marque values(123,'adidas',1000)
insert into marque values(124,'nike',2000)
insert into marque values(125,'puma',4000)
insert into produit values(1,'tiger',125,500,'40')
insert into produit values(2,'smith',123,400,'50')
insert into produit values(3,'sheekers',124,600,'40')
insert into produit values(4,'cock',125,300,'10')
insert into fournisseur values(10,'ona','casablanca',0524435010)
insert into fournisseur values(20,'pigi','marrakech',0524312345)
insert into fournisseur values(30,'akim','rabat',0524678976)
insert into fournir values(10,1,'2011/01/01',10,450)
insert into fournir values(20,2,'2011/05/09',20,350)
insert into fournir values(30,3,'2011/04/01',15,550)
--1
create function F1(@marque varchar(255)) returns table
As
return (select AVG(p.pu_v * p.qté) as [La moyenne des produit de marque ] from produit p, marque m where p.idm = m.idm and m.designation = @marque)
select * from dbo.F1('puma')
--2
create function F2(@idp int , @d1 date, @d2 date) returns table
As
return (select avg(qte) as 'Moyenne de quantité' from fournir where date_f between @d1 and @d2 and idp = @idp)
select * from dbo.F2(1,'2001/2/10','2012/1/1')
--3
alter function F3() returns @t table (Lib varchar(50))
As
Begin
Declare t cursor for (select libp from produit)
declare @l varchar(50), @max varchar(50), @n int
open t
fetch next from t into @l
select @max=@l, @n = len(@l)
while(@@FETCH_STATUS = 0)
Begin
if len(@l) > @n
begin
set @max = @l
set @n = len (@l)
end
fetch next from t into @l
end
insert into @t values(@max)
close t
deallocate t
return
End
select * from dbo.F3()
create function F4() returns @t table (libp varchar(255),pu float)
As
Begin
Declare C1 cursor for (select libp, pu_v from produit)
Declare @l varchar(55), @p float, @intr float,@l1 varchar(55), @p1 float, @tempP float, @tempLib varchar(50)
open C1
fetch next from C1 into @l,@p
while(@@Fetch_status = 0)
Begin
Declare C2 cursor for (select libp, pu_v from produit)
Open C2
fetch last from C2 into @l1,@p1
declare @pp float
set @pp = p1
while(@@Fetch_status = 0)
Begin
fetch last from C2 into @l1,@p1
if(@p1 > @pp)
Begin
--set @tempP = :x
print 'Erreur'
end
end
fetch next from C1 into @l,@p
end
return
end
----------------------------------------
--M1
create function F4() returns @t table (idp int, lib varchar(255), prix float)
As
Begin
declare @i1 int, @l1 varchar(255), @p1 float
declare c1 cursor for (select idp, libp,pu_v from produit)
open c1
fetch next from c1 into @i1,@l1,@p1
while @@FETCH_STATUS = 0
Begin
insert into @t select idp,libp,pu_v from produit where pu_v = (select MIN(pu_v) from produit where idp not in(select idp from @t))
fetch next from c1 into @i1,@l1,@p1
end
close c1
deallocate c1
return
end
select * from dbo.F4()
--M1
create function F4_2() returns @t table (idp int, lib varchar(255), prix float)
As
Begin
declare @i int
set @i = 0
while @i < (select count(*) from produit)
Begin
insert into @t select idp,libp,pu_v from produit where pu_v = (select MIN(pu_v) from produit where idp not in(select idp from @t))
set @i = @i + 1
end
return
end
select * from dbo.F4_2()
/*
Réaliser une fonction qui permet d’afficher le libellé et le PU, des produits d’une famille donnée,
augmentés ou diminués d’un pourcentage passé en paramètre
*/
alter function F5(@marque varchar(max), @p float) returns @t table (libp varchar(max), prix float)
As
Begin
insert into @t select libp, pu_v from produit p, marque m where p.idm = m.idm and m.designation = @marque
create proc p1
As
Begin
update produit set pu_v = 0
-- Le problème de Update :D
end
exec p1
return
end
select * from dbo.F5('puma', 10)
--------------------------------------------------------------------------
create function F7() returns int
As
Begin
declare t cursor for (select libp from produit)
declare @l varchar(255), @n int,@c int, @i int
set @n = 0
open t
fetch next from t into @l
while(@@fetch_status = 0)
Begin
-------------------
select @c=1, @i=1
while(@i < LEN(@l))
Begin
if(ascii(substring(@l,@i,1)) < 65 or ascii(substring(@l,@i,1)) > 90)
set @c = 0
set @i = @i + 1
end
if @c = 1
set @n = @n + 1
-------------------
fetch next from t into @l
end
close t
deallocate t
return @n
end
select dbo.F7()
create function F8() returns @t table(Libp varchar(255), fmlp varchar(255), CodP varchar(255))
As
Begin
declare c1 cursor for (select p.libp,m.designation from produit p , marque m where p.idm = m.idm)
declare @lb varchar(255), @mr varchar(255)
open c1
fetch next from c1 into @lb, @mr
while(@@fetch_status = 0)
begin
insert into @t values (@lb, @mr,len(@lb))
fetch next from c1 into @lb, @mr
end
return
end
select * from dbo.F8()
create function dbo.F9() returns @t table (libp varchar(255), ecart varchar(255))
As
Begin
insert into @t select p.libp, p.pu_v - f.pu_a as 'l’écart' from produit p , fournir f where p.idp = f.idp
return
end
select * from dbo.F9()