-
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
-- Dclaration constantes/variables BEGIN --
Commandes/instructions EXCEPTION -- Traitement des erreurs
lexcution END; / -- Excution automatique lors du start
Dclaration de variable Nom_Variable TYPE_VARIABLE; Affectation
Nom_Variable := valeur;
SELECT attribut INTO Nom_Variable FROM table; Tests IF
condition1 THEN
-- Instructions ELSEIF condition2 THEN -- (Optionnel) --
Instructions ELSE -- (Optionnel) -- Instructions END IF;
Boucles FOR compteur IN [REVERSE] min..max LOOP -- Instructions
END LOOP; WHILE condition LOOP -- Instructions END LOOP;
Curseurs - Dclaration - Utilisation
CURSOR Nom_Curseur IS Requte_SQL; FOR nuplet IN Nom_Curseur LOOP
-- Instructions -- Ex. Nom_Variable := nuplet.attribut; END LOOP;--
NB : nuplet est de type Nom_Curseur%ROWTYPE
Exceptions - Dclarer - Lever - Traiter
Nom_Exception EXCEPTION; RAISE Nom_Exception; WHEN Nom_Exception
THEN -- Instruction;
Documentation PL/SQL
http://dis.univ-lyon2.fr/~darmont/oradoc/appdev.101/b10807/toc.htm
Exercice 1 1. Dans un fichier .sql (commande edit monfichier.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 start monfichier.sql). Passer
directement la question suivante. 2. Tester nouveau en affectant la
variable denvironnement SQL*Plus SERVEROUTPUT la valeur ON
(commande SET variable valeur_variable 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 RAISE_APPLICATION_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 =
'&variable_saisie'). 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 SERVICE AS SELECT * FROM DARMONT.SERVICE). 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 COMMIT
; 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 nom PERSONNEL. 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 Jongleur 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 VIEW NomEmp AS SELECT ename FROM
darmont.emp;) et rexcuter le programme.
BD et programmation TD n 1 4/4
Exercice 6 Soit le schma suivant. AVION (AvNum, AvNom, Capacite,
Localisation) PILOTE (PlNum, PlNom, PlPrenom, Ville, Salaire) VOL
(VolNum, PlNum#, AvNum#, VilleDep, VilleArr, HeureDep, HeureArr)
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 INTEGER; full EXCEPTION; desi piece.designation%TYPE; BEGIN
DBMS_OUTPUT.PUT_LINE(message); SELECT COUNT(nop) INTO nbp FROM
darmont.piece; IF nbp > 60 THEN RAISE full; END IF;
DBMS_OUTPUT.PUT_LINE('Nombre de pices = ' || nbp); SELECT
designation INTO desi FROM darmont.piece WHERE nop =
'&numero_piece'; DBMS_OUTPUT.PUT_LINE('Designation : ' ||
desi); EXCEPTION WHEN full THEN RAISE_APPLICATION_ERROR(-20500,
'Entrepot plein !'); END; / -- Ex 2 DECLARE CURSOR services IS
SELECT * FROM service ORDER BY nos; un_service services%ROWTYPE;
nbs INTEGER; no_service EXCEPTION; BEGIN SELECT COUNT(*) INTO nbs
FROM service; IF nbs=0 THEN RAISE no_service; END IF; FOR
un_service IN services LOOP DBMS_OUTPUT.PUT_LINE(un_service.nos ||
' : ' || un_service.intitule || ' (' || un_service.localisation ||
')'); END LOOP; EXCEPTION WHEN no_service THEN
DBMS_OUTPUT.PUT_LINE('La table SERVICE est vide.'); END; /
BD et programmation TD n 1 6/4
-- Ex 3 DECLARE euro CONSTANT REAL := 6.55957; nbprod NUMBER(3);
aucun_produit EXCEPTION; CURSOR acces IS SELECT numprod, desi,
prixuni, numfour FROM produit; prod acces%ROWTYPE; BEGIN -- Compte
des produits SELECT COUNT(*) INTO nbprod FROM PRODUIT; -- Si pas de
produits, exception IF nbprod = 0 THEN RAISE aucun_produit; END IF;
-- Acces sequentiel a la table PRODUIT -- Remplissage de la table
PRODUIT2 FOR prod IN acces LOOP INSERT INTO produit2
VALUES(prod.numprod, UPPER(prod.desi),
ROUND(NVL(prod.prixuni,0)/euro), prod.numfour); END LOOP; --
Validation de la transaction COMMIT; EXCEPTION WHEN aucun_produit
THEN INSERT INTO produit2 VALUES(0,'Pas de produit', NULL); END; /
-- Ex 4 DECLARE ntot INTEGER; -- Nombre total demploys njon
INTEGER; -- Nombre de jongleurs pjon NUMBER(6,2); -- Proportion de
jongleurs personne EXCEPTION; -- Exception : pas demployes BEGIN
SELECT COUNT(*) INTO ntot FROM personnel; IF (ntot=0) THEN RAISE
personne; END IF; SELECT COUNT(*) INTO njon FROM personnel WHERE
role='Jongleur'; pjon := 100 * njon / ntot;
DBMS_OUTPUT.PUT_LINE('Proportion de jongleurs = ' || pjon || ' %');
EXCEPTION WHEN personne THEN RAISE_APPLICATION_ERROR(-20500, 'La
table PERSONNEL est vide !'); END; /
-
BD et programmation TD n 1 7/4
-- Ex 5 DECLARE CURSOR catalogue IS SELECT tname, tabtype FROM
tab; ligne catalogue%ROWTYPE; BEGIN FOR ligne IN catalogue LOOP
DBMS_OUTPUT.PUT_LINE('L''objet ' || ligne.tname || ' est de type '
|| ligne.tabtype || '.'); END LOOP; END; / -- Ex 6 DECLARE CURSOR
avamodif IS SELECT volnum, avnum, heuredep, heurearr FROM vol WHERE
avnum IN (1, 2, 4, 8); volmod avamodif%ROWTYPE; tvol REAL; BEGIN
FOR volmod IN avamodif LOOP tvol := volmod.heurearr -
volmod.heuredep; IF volmod.avnum IN (1, 4) THEN tvol := tvol*0.9;
ELSE tvol := tvol*0.85; END IF; UPDATE vol SET heurearr =
volmod.heuredep + tvol WHERE volnum = volmod.volnum; END LOOP; END;
/
