Téléchargez comme PDF, TXT ou lisez en ligne sur Scribd
Télécharger au format pdf ou txt
Vous êtes sur la page 1sur 4
BD et programmation TD n 1 1/4
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 1 J . Darmont (http://eric.univ-lyon2.fr/~jdarmont/), 11/10/05
Aide-mmoire PL/SQL
Bloc PL/SQL DECLARE - - Dcl ar at i on const ant es/ var i abl es BEGI N - - Commandes/ i nst r uct i ons EXCEPTI ON - - Tr ai t ement des er r eur s l excut i on END; / - - Excut i on aut omat i que l or s du st ar t Dclaration de variable Nom_Var i abl e TYPE_VARI ABLE; Affectation Nom_Var i abl e : = val eur ; SELECT at t r i but I NTO Nom_Var i abl e FROM t abl e; Tests I F condi t i on1 THEN - - I nst r uct i ons ELSEI F condi t i on2 THEN - - ( Opt i onnel ) - - I nst r uct i ons ELSE - - ( Opt i onnel ) - - I nst r uct i ons END I F; Boucles FOR compt eur I N [ REVERSE] mi n. . max LOOP - - I nst r uct i ons END LOOP; WHI LE condi t i on LOOP - - I nst r uct i ons END LOOP; Curseurs - Dclaration - Utilisation CURSOR Nom_Cur seur I S Requt e_SQL; FOR nupl et I N Nom_Cur seur LOOP - - I nst r uct i ons - - Ex. Nom_Var i abl e : = nupl et . at t r i but ; END LOOP; - - NB : nupl et est de t ype Nom_Cur seur %ROWTYPE Exceptions - Dclarer - Lever - Traiter Nom_Except i on EXCEPTI ON; RAI SE Nom_Except i on; WHEN Nom_Except i on THEN - - I nst r uct i on; Documentation PL/SQL http://dis.univ-lyon2.fr/~darmont/oradoc/appdev.101/b10807/toc.htm
Exercice 1
1. Dans un fichier . sql (commande edi t monf i chi er . sql ), crire un programme PL/SQL (bloc PL/SQL anonyme) permettant dafficher un message lcran. Le message devra tre contenu dans une variable chane de caractres qui aura pralablement initialise Coucou ! , par exemple. Tester (commande st ar t monf i chi er . sql ). Passer directement la question suivante.
2. Tester nouveau en affectant la variable denvironnement SQL*Plus SERVEROUTPUT la valeur ON (commande SET var i abl e val eur _var i abl e ou menu Options/Environnement).
3. Dans le mme bloc PL/SQL, dfinir une variable entire, puis lui affecter le nombre total de pices enregistres dans la table PIECE de lutilisateur DARMONT (DARMONT.PIECE). Afficher le contenu de cette variable dans un message de la forme Nombre de pices =XX . Tester. BD et programmation TD n 1 2/4 4. Dans le cas o le nombre de pices est suprieur 6, dclencher une exception et ne pas afficher le nombre de pices. Le traitement de cette exception devra donc interrompre lexcution du programme (erreur fatale, trop de pices pour tenir dans lentrept). Pour cela, utiliser la procdure prdfinie RAI SE_APPLI CATI ON_ERROR. Tester.
5. la suite de ce programme, ajouter le code PL/SQL permettant dafficher lcran la dsignation dune pice dont le numro est saisi au clavier (utiliser une condition du type WHERE nop = ' &var i abl e_sai si e' ). Modifier la condition dexception pour permettre lexcution de cette partie du code (par exemple, nombre de pices suprieur 60). Que se passe-t-il lorsque le numro de pice saisi nexiste pas ?
Exercice 2
1. Recopier la table SERVICE de lutilisateur DARMONT dans la table SERVICE de votre compte (en SQL : CREATE TABLE SERVI CE AS SELECT * FROM DARMONT. SERVI CE).
2. crire un bloc PL/SQL anonyme permettant dafficher les services de la table SERVICE au format numro_service : intitul (localisation) . NB : Lexploitation dune requte qui retourne plusieurs n-uplets passe forcment par lutilisation dun curseur.
3. Au cas o il ny ait aucun n-uplet dans la table SERVICE, afficher un message derreur (exception, mais pas erreur fatale). Tester le bon fonctionnement du test en suivant la procdure suivante : 1. valider les mises jour prcdentes laide de la commande COMMI T ; 2. effacer le contenu de la table SERVICE ; 3. excuter le bloc PL/SQL ; 4. annuler leffacement de la table SERVICE laide de la commande ROLLBACK.
Exercice 3
Une entreprise de type faillitaire rcupre un stock ancien de produits. La base de donnes qui dcrit ce stock indique des prix en Francs. crire un bloc PL/SQL anonyme permettant de construire, partir dune table PRODUIT, une table PRODUIT2 telle que : la dsignation des produits soit crite en majuscules ; le prix unitaire en francs des produits soit converti en euros (1 =6,55957 F). Le prix en euros devra tre entier (arrondir au prix le plus proche).
Cas particuliers traiter : Si la table PRODUIT est vide, la table PRODUIT2 devra contenir uniquement le n-uplet (0,Pas de produit, NULL). Si un prix de la table produit est NULL, son prix en euros doit tre 0.
Indications : Tester si la table PRODUIT est vide. Si ce nest pas le cas, y accder squentiellement laide dun curseur, effectuer les transformations sur les champs et stocker le rsultat dans la table PRODUIT2. Utiliser les fonctions SQL UPPER, ROUND et NVL. Documentation sur les fonctions : http://dis.univ-lyon2.fr/~darmont/oradoc/server.101/b10759/functions001.htm (TSVP) BD et programmation TD n 1 3/4 Marche suivre : 1. En SQL, crer la structure de la table PRODUIT (NumProd, Designation, Prix). Laisser la table vide. 2. En SQL, crer la table PRODUIT2 en recopiant la structure de la table PRODUIT. 3. Saisir le bloc PL/SQL adquat dans un fichier. 4. Excuter ce bloc PL/SQL. 5. En SQL, peupler la table PRODUIT avec quelques n-uplets. 6. R-excuter le bloc PL/SQL. 7. Afficher le contenu de la table PRODUIT2.
Exercice 4
Soit la table PERSONNEL (Nom, Role) qui rassemble les membres du personnel dun cirque. On souhaite dterminer la proportion de jongleurs parmi eux.
1. Recopier la table DARMONT.PERSONNEL sur votre compte, sous le nomPERSONNEL.
2. crire un bloc PL/SQL anonyme permettant de : compter le nombre de n-uplets dans la table PERSONNEL et stocker le rsultat dans une variable ; compter le nombre demploys dont le rle est J ongleur dans la table PERSONNEL et stocker le rsultat dans une deuxime variable ; calculer la proportion (en pourcentage), stocker le rsultat dans une troisime variable et afficher le rsultat lcran.
3. Inclure dans le programme prcdent un traitement dexception permettant de dtecter si la table PERSONNEL est vide, cest--dire, que le nombre total de n-uplets dans PERSONNEL est gal zro. Dans ce cas, dclencher une erreur fatale. Tester en effaant tout le contenu de la table PERSONNEL. Il est ensuite possible dannuler cette transaction grce la commande ROLLBACK (cf. Exercice 2).
Exercice 5
1. crire un programme PL/SQL (bloc PL/SQL anonyme) permettant dafficher votre catalogue systme (liste des tables et des vues de votre compte disponible grce la vue systme TAB sous la forme : Lobjet UNE_TABLE est de type TABLE. Lobjet UNE_AUTRE_TABLE est de type TABLE. Lobjet UNE_VUE est de type VIEW.
NB : Tester au pralable sous SQL lexcution de la requte SELECT * FROM TAB; pour avoir une ide du contenu de la vue systme TAB.
2. Crer une vue quelconque (ex. CREATE VI EWNomEmp AS SELECT ename FROM dar mont . emp; ) et rexcuter le programme. BD et programmation TD n 1 4/4 Exercice 6
Les performances des avions de marque Airbus voluent, aussi souhaite t-on faire des mises jour de la table VOL. Les temps de vol des avions de type A300 (avions n 1 et 4) doivent tre rduits de 10 % et ceux des avions de type A310 (avions n 2 et 8) de 15 %. Il sagit de dfinir un programme PL/SQL permettant ces modifications.
1. Recopier les tables PILOTE, AVION et VOL du compte DARMONT sur le vtre.
2. Dans un bloc PL/SQL anonyme, dclarer un curseur permettant de lire les donnes suivantes : numro de vol, numro davion, heure de dpart et heure darrive des vols pour lesquels lavion utilis est le n 1, 2, 4 ou 8. Pour chaque vol lu par le curseur, calculer le temps de vol, le rduire dans la proportion voulue selon lavion utilis, puis mettre jour lattribut HEUREARR de ce vol dans la table VOL.
3. Tester !
BD et programmation TD n 1 5/4 Correction
-- Ex 1
DECLARE message VARCHAR( 10) : = ' Coucou ! ' ; nbp I NTEGER; f ul l EXCEPTI ON; desi pi ece. desi gnat i on%TYPE;
BEGI N DBMS_OUTPUT. PUT_LI NE( message) ;
SELECT COUNT( nop) I NTO nbp FROM dar mont . pi ece;
I F nbp > 60 THEN RAI SE f ul l ; END I F;
DBMS_OUTPUT. PUT_LI NE( ' Nombr e de pi ces = ' | | nbp) ;
SELECT desi gnat i on I NTO desi FROM dar mont . pi ece WHERE nop = ' &numer o_pi ece' ; DBMS_OUTPUT. PUT_LI NE( ' Desi gnat i on : ' | | desi ) ;
EXCEPTI ON WHEN f ul l THEN RAI SE_APPLI CATI ON_ERROR( - 20500, ' Ent r epot pl ei n ! ' ) ;
END; /
-- Ex 2
DECLARE CURSOR ser vi ces I S SELECT * FROM ser vi ce ORDER BY nos; un_ser vi ce ser vi ces%ROWTYPE; nbs I NTEGER; no_ser vi ce EXCEPTI ON;
BEGI N SELECT COUNT( *) I NTO nbs FROM ser vi ce; I F nbs=0 THEN RAI SE no_ser vi ce; END I F;
FOR un_ser vi ce I N ser vi ces LOOP DBMS_OUTPUT. PUT_LI NE( un_ser vi ce. nos | | ' : ' | | un_ser vi ce. i nt i t ul e | | ' ( ' | | un_ser vi ce. l ocal i sat i on | | ' ) ' ) ; END LOOP;
EXCEPTI ON WHEN no_ser vi ce THEN DBMS_OUTPUT. PUT_LI NE( ' La t abl e SERVI CE est vi de. ' ) ;
END; /
BD et programmation TD n 1 6/4 -- Ex 3
DECLARE eur o CONSTANT REAL : = 6. 55957; nbpr od NUMBER( 3) ; aucun_pr odui t EXCEPTI ON; CURSOR acces I S SELECT numpr od, desi , pr i xuni , numf our FROM pr odui t ; pr od acces%ROWTYPE;
BEGI N - - Compt e des pr odui t s SELECT COUNT( *) I NTO nbpr od FROM PRODUI T;
- - Si pas de pr odui t s, except i on I F nbpr od = 0 THEN RAI SE aucun_pr odui t ; END I F;
- - Acces sequent i el a l a t abl e PRODUI T - - Rempl i ssage de l a t abl e PRODUI T2 FOR pr od I N acces LOOP I NSERT I NTO pr odui t 2 VALUES( pr od. numpr od, UPPER( pr od. desi ) , ROUND( NVL( pr od. pr i xuni , 0) / eur o) , pr od. numf our ) ; END LOOP;
- - Val i dat i on de l a t r ansact i on COMMI T;
EXCEPTI ON WHEN aucun_pr odui t THEN I NSERT I NTO pr odui t 2 VALUES( 0, ' Pas de pr odui t ' , NULL) ;
END; /
-- Ex 4
DECLARE nt ot I NTEGER; - - Nombr e t ot al d empl oys nj on I NTEGER; - - Nombr e de j ongl eur s pj on NUMBER( 6, 2) ; - - Pr opor t i on de j ongl eur s per sonne EXCEPTI ON; - - Except i on : pas d empl oyes
BEGI N SELECT COUNT( *) I NTO nt ot FROM per sonnel ; I F ( nt ot =0) THEN RAI SE per sonne; END I F;
SELECT COUNT( *) I NTO nj on FROM per sonnel WHERE r ol e=' J ongl eur ' ; pj on : = 100 * nj on / nt ot ; DBMS_OUTPUT. PUT_LI NE( ' Pr opor t i on de j ongl eur s = ' | | pj on | | ' %' ) ;
EXCEPTI ON WHEN per sonne THEN RAI SE_APPLI CATI ON_ERROR( - 20500, ' La t abl e PERSONNEL est vi de ! ' ) ;
END; /
BD et programmation TD n 1 7/4 -- Ex 5
DECLARE CURSOR cat al ogue I S SELECT t name, t abt ype FROM t ab; l i gne cat al ogue%ROWTYPE;
BEGI N FOR l i gne I N cat al ogue LOOP DBMS_OUTPUT. PUT_LI NE( ' L' ' obj et ' | | l i gne. t name | | ' est de t ype ' | | l i gne. t abt ype | | ' . ' ) ; END LOOP; END; /
-- Ex 6
DECLARE CURSOR avamodi f I S SELECT vol num, avnum, heur edep, heur ear r FROM vol WHERE avnumI N ( 1, 2, 4, 8) ; vol mod avamodi f %ROWTYPE; t vol REAL;
BEGI N FOR vol mod I N avamodi f LOOP t vol : = vol mod. heur ear r - vol mod. heur edep; I F vol mod. avnumI N ( 1, 4) THEN t vol : = t vol *0. 9; ELSE t vol : = t vol *0. 85; END I F; UPDATE vol SET heur ear r = vol mod. heur edep + t vol WHERE vol num= vol mod. vol num; END LOOP;