BD et programmation TD n 21/5 Universit Lumire Lyon 2, Facult de
Sciences conomiques et de Gestion Master dInformatique M2 spcialit
IUP IDS Anne 2005-2006 Bases de donnes et programmation TD n 2 J .
Darmont (http://eric.univ-lyon2.fr/~jdarmont/), 19/10/05 Exercice 1
1. Sous SQL, crer une table nomme TEST_DBL contenant un seul
attribut numrique. Ne pas spcifier de cl primaire. Peupler cette
table en incluant des doublons et des triplets. 2. crire un bloc
PL/SQL anonyme permettant dafficher les valeurs prsentes au moins
en double dans la table TEST_DBL. 3. Intgrer dans le programme
PL/SQL la suppression les doublons. 4. Est-il possible dajouter une
cl primaire la table TEST_DBL depuis un bloc PL/SQL ? Exercice 2 On
souhaite appliquer une rgle dchantillonnage la table DARMONT.EMP
(Empno, Ename).
crireunprogrammePL/SQL(blocPL/SQLanonyme)permettantdelirelatableEMPet
dafficher les noms des employs dont les rangs dapparition dans la
table sont : 1, 3, 6, 10, 15 Rgle de calcul des rangs successifs :
1= 0+ 1 3= 1+ 2 6= 3+ 3 10= 6+ 4 15= 10+ 5 Rsultat attendu : 1: SMI
TH 3: WARD 6: BLAKE 10: TURNER Indication
:Utiliserunparcourspersonnalisdecurseurdanslequel n-1 n-uplets sont
lus avant dafficher un nomdemploy (n =1, 2, 3 ; cest le nombre en
italiques dans la rgle de calcul des rangs). Exercice 3 Pour tenter
dtablir une corrlation, on souhaite connatre la diffrence de
quantit moyenne entre les commandes successivement enregistres dans
la table COMMANDE dune base de donnes
CLIENT-COMMANDE-PRODUIT.LatableCOMMANDEestrempliedecommandesvalues
(cest--dire, pour lesquelles lattribut QUANTITE nest pas NULL) ou
non. Les commandes non values ne sont pas prendre en compte. crire
un programme PL/SQL (bloc PL/SQL anonyme) permettant de calculer la
diffrence de quantit moyenne entre les commandes. Pour simplifier,
on BD et programmation TD n 22/5 pourra la rduire la table COMMANDE
lunique attribut QUANTITE. Exemple : QUANTI TE 5 NULL 10 8 9 13 Cas
particuliers traiter : La table COMMANDE contient moins de deux
commandes values. Indications : Crer la table COMMANDE et la
remplir laide de SQL. Crer un curseur contenant les quantits des
commandes values. laide dun parcours de curseur personnalis, lire
la premire quantit puis, pour toutes les quantits suivantes,
cumuler la valeur absolue de quantitcourante quantitprcdente
(fonction ABS). Exercice 4 : Application On souhaite grer les
rsultats dexamens de la Facult de Sciences conomiques et de
Gestion. Il sagit de dfinir un programme PL/SQL permettant
linsertion automatique dinformations dans les relations rsultats
RESULTATetCLASSEMENT,partirdesdonnesdesrelations sources
ETUDIANT,NOTATIONetMATIERE,quicontiennentrespectivementdes
renseignements sur les tudiants, les notes obtenues par les
tudiants et les coefficients affects aux matires. Pour dfinir ce
programme, suivre les tapes suivantes. 1) laide du langage SQL,
recopier les relations ETUDIANT, NOTATION et MATIERE de
lutilisateurDARMONTsurvotreproprecompte.UtiliserlacommandeDESCpouren
consulter le schma. 2)Dfinir en SQL la structure des relations
RESULTAT et CLASSEMENT : la relation RESULTAT a pour attributs un
numro dtudiant, un nomdtudiant, un code matire, ainsi quun attribut
note globale pour cet tudiant ; la relation CLASSEMENT a pour
attributs un numro dtudiant, un nomdtudiant, une moyenne gnrale et
un rang (place au classement). Ne pas inclure de contrainte
dintgrit dans la dfinition de ces deux relations, qui sont
temporaires et ne servent quau stockage des rsultats. 3)Dfinir un
bloc PL/SQL anonyme permettant dinsrer dans RESULTAT tous les
n-uplets constitus du numro dun tudiant, de son nom, du code dune
matire et de la note obtenue par cet tudiant dans cette matire. Le
calcul de cette note doit tenir compte des coefficients
decontrlecontinuetdexamendfinispourlamatireenquestion,ainsiquedela
possibilit davoir des valeurs nulles pour les notes des tudiants,
qui sont alors assimiles Rsultat attendu =( |10 5| +|8 10| +|9 8|
+|13 9| ) / 4 =3 BD et programmation TD n 23/5
0(utiliserlafonctionNVL).Lesn-upletsconsidrsdoiventtreextraitsdestables
ETUDIANT, NOTATION et MATIERE de manire itrative, grce un curseur
adapt. 4)Terminer le traitement en ralisant linsertion dans la
relation CLASSEMENT des n-uplets constitus du numro dun tudiant, de
son nom, de son prnom, de la moyenne gnrale obtenue dans toutes les
matires par cet tudiant (ces informations doivent tre extraites de
latableRESULTAT)etdesonrang(place),quidoittrecalcul.Poursimplifier,on
considre que toutes les matires sont quivalentes en termes de
notes. Utiliser un curseur dans lequel les enregistrements sont
tris. Questions complmentaires Modifier le programme afin de
prendre en compte : le cas o plusieurs tudiants ont le mme rang (ex
quo) ; le cas dtudiants nayant aucune note (par dfaut, leur moyenne
gnrale est 0) ; la possibilit de navoir aucun n-uplet dans la
relation ETUDIANT. Dans ce cas, un n-uplet de valeur (0, Aucun
tudiant, NULL, NULL) doit tre insr dans la relation RESULTAT et le
traitement doit sarrter. BD et programmation TD n 24/5 Correction
-- Ex. 1 DECLARE CURSORdoubl onsI SSELECTdbl FROM t est
_dblGROUPBYdblHAVI NGCOUNT( *) >1;nupl et doubl ons%ROWTYPE;
BEGI N DBMS_OUTPUT. PUT_LI NE( ' Val eur sendoubl edansTEST_DBL: '
) ;FORnupl et I Ndoubl onsLOOP DBMS_OUTPUT. PUT_LI NE( nupl et .
dbl ) ;DELETEFROM t est _dbl WHEREdbl =nupl et . dbl ;I NSERTI NTO
t est _dbl VALUES( nupl et . dbl ) ;ENDLOOP;DBMS_OUTPUT. PUT_LI NE(
' Doubl onssuppr i ms! ' ) ;- - ALTERTABLEt est _dbl ADDCONSTRAI
NTt est _dbl _pr i PRI MARYKEY( dbl ) ;- - Nef onct i onnepas END;/
- - Ex. 2 DECLARE CURSORempl oyesI Ssel ect enamef r omdar mont .
emp;eempl oyes%ROWTYPE;i I NTEGER;nI NTEGER: = 1; BEGI N OPENempl
oyes;FETCHempl oyesI NTO e; - - 1er n- upl etWHI LEempl
oyes%FOUNDLOOP - - Lect ur eden- 1n- upl et ssuppl ment ai r es(
onsaut edesl i gnes)i : = 1;WHI LE( i < n) AND( empl oyes%FOUND)
LOOP FETCHempl oyesI NTO e;i : = i + 1;ENDLOOP;- - Af f i chage I
Fempl oyes%FOUNDTHEN- - Si onn est pasenf i ndecur seurDBMS_OUTPUT.
PUT_LI NE( empl oyes%ROWCOUNT| | ' : ' | | e. ename) ;n: = n+
1;FETCHempl oyesI NTO e; - - N- upl et sui vantENDI
F;ENDLOOP;CLOSEempl oyes;END;/ BD et programmation TD n 25/5 -- Ex.
3 DECLARE CURSORval ueesI S SELECTquant i t eFROM
commandeWHEREquant i t eI SNOTNULL;cdeval uees%ROWTYPE;pr ecREAL; -
- quant i t epr ecedent e cour REAL; - - quant i t ecour ant e
cumul REAL;moyenneREAL;ncvI NTEGER;pas_assezEXCEPTI ON; BEGI N - -
Test nombr edecommandesval uees SELECTCOUNT( *) I NTO ncvFROM
commandeWHEREquant i t eI SNOTNULL;I Fncv< 2THEN RAI
SEpas_assez;ENDI F; - - Acces1er n- upl etOPENval uees;FETCHval
ueesI NTO cde; - - 1er n- upl etpr ec: = cde. quant i t e; - - I ni
t i al i sat i ondupr cdent - - Accesauxsui vant set cumulcumul :
=0;WHI LEval uees%FOUNDLOOP cour : = cde. quant i t e;cumul : =
cumul + ABS( cour - pr ec) ;pr ec: = cour ;FETCHval ueesI NTO cde;
- - N- upl et ui vantENDLOOP;CLOSEval uees; - - Cal cul et af f i
chagedel amoyenne moyenne: = cumul / ( ncv- 1) ;DBMS_OUTPUT. PUT_LI
NE( ' Moyenne= ' | | TO_CHAR( moyenne) ) ; EXCEPTI ON
WHENpas_assezTHEN RAI SE_APPLI CATI ON_ERROR( - 20501, '
Pasassezdecommandes' ) ; END;/ -- Ex. 4 - - RESULTAT cr eat et abl
er esul t at (numenumber ( 2) ,nomchar ( 20) ,codemat number ( 2)
,not enumber ( 5, 2) ) ; - - CLASSEMENT cr eat et abl ecl assement
(numenumber ( 2) ,nomchar ( 20) ,moyennenumber ( 5, 2) ,r angnumber
( 2) ) ;BD et programmation TD n 26/5 - - Edi t i ondesr esul t at
set cl assement deset udi ant s( bl ocPL/ SQL) DECLARE - - Cal cul
del anot egl obal epar mat i er e CURSORet udi ant _not eI S
SELECTETUDI ANT. nume, nom, MATI ERE. codemat ,( ( NVL( not ecc, 0)
*coef cc) + ( NVL( not exam, 0) *coef exam) ) /( coef cc+ coef
exam) ASnot e FROM ETUDI ANT, NOTATI ON, MATI ERE WHEREETUDI ANT.
nume= NOTATI ON. nume ANDNOTATI ON. codemat = MATI ERE. codemat
;enet udi ant _not e%ROWTYPE;nbeI NTEGER; - - Nombr ed' et udi ant
sacl asserr gI NTEGER; - - Pl acedansl ecl assement gener
almoyNUMBER( 5, 2) ; - - Moyennegener al ed' unet udi antaucun_et
udi ant EXCEPTI ON; - - Cal cul del amoyennegener al e - - Let r i
per met def aci l i t er l et r ai t ement ul t er i eur ducl
assement .CURSORet udi ant _cl asseI S SELECTnume, nom, AVG( not e)
moyenne FROM RESULTAT GROUPBYnume, nomORDERBYmoyenneDESC;ecet udi
ant _cl asse%ROWTYPE; - - Gest i ondeset udi ant ssansaucunenot e
CURSORet udi ant _non_not eI S SELECTnume, nomFROM ETUDI ANT
WHEREnumeNOTI N( SELECTDI STI NCTnumeFROM NOTATI ON) ;ennet udi ant
_non_not e%ROWTYPE; BEGI N - - Dest r uct i ondesdonneesevent uel l
esdesanneespr ecedent es DELETEFROM RESULTAT;DELETEFROM CLASSEMENT;
- - Test del ' exi st anceden- upl et sdansl ar el at i onETUDI ANT
SELECTCOUNT( *) I NTO nbeFROM ETUDI ANT;I Fnbe=0THEN RAI SEaucun_et
udi ant ;ENDI F; - - I nser t i ondeset udi ant snot escommen- upl
et sdansRESULTAT FORenI Net udi ant _not eLOOP I NSERTI NTO
RESULTAT VALUES( en. nume, en. nom, en. codemat , en. not e)
;ENDLOOP; - - I nser t i ondeset udi ant ssansnot ecommen- upl et
sdansRESULTAT FORennI Net udi ant _non_not eLOOP I NSERTI NTO
RESULTAT VALUES( enn. nume, enn. nom, NULL, 0) ;ENDLOOP; - - I ni t
i al i sat i ondesvar i abl esl ocal es moy: =21;r g: =- 1; - - I
nser t i ondesn- upl et sdansl ar el at i onCLASSEMENT FORecI Net
udi ant _cl asseLOOP I Fmoy>ec. moyenneTHEN r g: =et udi ant _cl
asse%ROWCOUNT;ENDI F;BD et programmation TD n 27/5 I NSERTI NTO
CLASSEMENT VALUES( ec. nume, ec. nom, ec. moyenne, r g) ;moy: =ec.
moyenne;ENDLOOP; - - Val i dat i ondel at r ansact i on COMMI T;
EXCEPTI ON WHENaucun_et udi ant THEN I NSERTI NTO RESULTAT VALUES(
0, ' Aucunet udi ant ' , NULL, NULL) ; END;/
