Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

NFA011 – Développement d’applications avec les bases de données ED PL/SQL (Corr

NFA011 – Développement d’applications avec les bases de données ED PL/SQL (Corrigé) Par la suite on considère que les tables utilisées par les exercices ont été déjà crées et remplies avec les données nécessaires. Déclarations, itérations, boucles, instructions conditionnelles Exercice 1. Soit la table suivante : VOL(Numvol, Heure_départ, Heure_arrivée, Ville_départ, Ville_arrivée) Écrivez un programme PL/SQL qui insère le vol AF110 partant de Paris à 21h40 et arrivant à Dublin à 23h10 (hypothèse : le vol n’est pas déjà présent dans la table). Solution : DECLARE v vol%ROWTYPE; BEGIN v.numvol := 'AF110'; v.heure_départ := to_date('21/11/2013 21:40', 'DD/MM/YYYY hh24:mi'); v.heure_arrivée := to_date('21/11/2013 23:10', 'DD/MM/YYYY hh24:mi'); v.ville_départ := 'Paris'; v.ville_arrivée := 'Dublin'; INSERT INTO vol VALUES v; END; Exercice 2. Soit la table RES(NO). Écrivez un bloc PL/SQL qui inséré les chiffres de 1 à 100 dans cette table. Solution : DECLARE nb NUMBER := 1 ; BEGIN LOOP INSERT INTO RES VALUES(nb) ; nb = nb + 1 ; EXIT WHEN nb > 100 ; END LOOP END Exercice 3. Écrivez un bloc PL/SQL qui affiche la somme des nombres entre 1000 et 10000. Solution : DECLARE somme NUMBER := 0 ; BEGIN FOR i IN 1000..10000 LOOP somme = somme + i ; END LOOP DBMS_OUTPUT.PUT_LINE('Somme = ' || somme) ; END NFA011 NFA011 – Développement d’applications avec les bases de données Exercice 4. Écrivez un programme PL/SQL qui affiche le reste de la division de 17664 par 171. Ne pas utilisez la fonction MOD. Solution : DECLARE reste NUMBER = 17664 ; BEGIN WHILE reste > 171 LOOP reste .= reste – 171 ; END LOOP DBMS_OUTPUT.PUT_LINE('Le reste de 17664 par 171 est ' || reste) END Exercice 5. Créez une type tableau pouvant contenir jusqu'à 50 entiers. 1. Créez une variable de ce type, faites une allocation dynamique et dimensionnez ce tableau à 20 emplacements. 2. Placez dans ce tableau la liste des 20 premiers carrés parfaits : 1, 4, 9, 16, 25, … 3. Affichez ce tableau. Solution : DECLARE TYPE MTAB IS VARRAY (50) OF INTEGER ; t MONTAB ; BEGIN t := MONTAB() ; t.extend(20) ; -- initialisation FOR i IN 1..20 LOOP t(i) := i*i ; END LOOP ; -- affichage FOR i IN 1..20 LOOP DBMS_OUTPUT.PUT_LINE('t(' || i || ') = ' || t(i)) ; END LOOP ; END Exercice 6. Écrire une fonction PL/SQL qui prends en entrée un nombre entier n et retourne le factoriel de ce nombre n!. Implémenter deux versions : itérative et récursive. La version récursive est basée sur la relation de récurrence : n! = n · [(n – 1)!] Solution : CREATE OR REPLACE FUNCTION FACT_ITER (N INTEGER) RETURN INTEGER IS result INTEGER := 1; BEGIN for i in 2..N loop result := result*i; end loop; return result; END FACT_ITER; CREATE OR REPLACE FUNCTION FACT_REC (N INTEGER) RETURN INTEGER IS BEGIN IF (N < 0) THEN NFA011 NFA011 – Développement d’applications avec les bases de données RETURN -1; ELSIF(N = 0) THEN RETURN 1; ELSE RETURN N*FACT1(N - 1); END IF; END FACT_REC; Curseurs, déclencheurs, relations Exercice 7. On considère la table suivante: PILOTE(Matricule, Nom, Ville, Age, Salaire). Écrivez un programme PL/SQL qui calcule la moyenne des salaires des pilotes dont l’âge est entre 30 et 40 ans. Solution : DECLARE CURSOR curseur1 IS SELECT salaire FROM pilote WHERE (Age >= 30 AND Age <=40); salairePilote Pilote.Salaire%TYPE; sommeSalaires NUMBER(11,2) := 0; moyenneSalaires NUMBER(11,2); BEGIN OPEN curseur1; LOOP FETCH curseur1 INTO salairePilote; EXIT WHEN (curseur1%NOTFOUND OR curseur1%NOTFOUND IS NULL); sommeSalaires := sommeSalaires + salairePilote; END LOOP; moyenneSalaires := sommeSalaires / curseur1%ROWCOUNT; CLOSE curseur1; DBMS_OUTPUT.PUT_LINE('Moyenne salaires (pilotes de 30 <E0> 40 ans) : ' || moyenneSalaires); END; Exercice 8. Soit la base de données suivante (simplifiée) de gestion de la mémoire d'un ordinateur : DISQUE(nom, capacité, vitesse, fabricant); PARTITION(nomDisque, nomPartition, taille); Écrivez en PL/SQL le déclencheur (trigger) qui lors de l’insertion d’une nouvelle ligne dans la table PARTITION vérifie que la taille totale des partitions sur le disque concerné (y compris la partition qui est en cours d'être ajoutée) ne dépasse pas la capacité du disque. Si tel n’est pas le cas, l’enregistrement de la nouvelle cage ne doit pas être fait et un message doit être affiché pour indiquer cette anomalie. Solution : CREATE OR REPLACE TRIGGER VérificationDisque BEFORE INSERT ON PARTITION FOR EACH ROW /* nécessaire pour avoir accès à :NEW */ DECLARE tailleT otale PARTITION.taille%TYPE = 0; capacitéDisque DISQUE.capacité%TYPE = 0; BEGIN SELECT SUM(taille) INTO tailleT otale FROM PARTITION WHERE nomDisque = :NEW.nomDisque; NFA011 NFA011 – Développement d’applications avec les bases de données SELECT capacité INTO capacitéDisque FROM DISQUE WHERE nom = :NEW.nomDisque; IF tailleT otale + :NEW.taille > capacitéDisque THEN RAISE_APPLICATION_ERROR(-20100, ‘Pas assez d'espace disque pour créer la partition ’ || :NEW.nomPartition); ENDIF END Exercice 9. Soit la relation : EMPLOYE(ID, NOM, DEPARTEMENT, AGE, SALAIRE). Écrivez un bloc PLSQL qui effectue une augmentation de 200 euros du salaire des employés du département 'Commercial' et qui utilise le dernier curseur implicite pour afficher le nombre d’employés affectés par ce changement. Solution : Les curseurs implicites sont créés par PLSQL lors de l’exécution des commandes SQL qui itèrent sur plusieurs items (I N S E R T , U P D A T E , D E L E T E , S E L E C T , e t c ). Les attributs du dernier curseur implicite utilisé sont accessibles par le préfixe SQL : SQL%FOUND, SQL %NOTFOUND, SQL%ROWCOUNT DECLARE total NUMBER(2); BEGIN UPDATE EMPLOYE SET salaire = salaire + 200 WHERE DEPARTEMENT = 'Commercial'; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Aucun salaire augmenté'); ELSIF SQL%FOUND THEN total := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE( total || ' salaires ont été augmentés '); END IF; END; Exercice 10. Soit la relation EMPLOYE de l'exercice précédent. É c r i v e z u n b l o c P L / S Q L q u i a ffi c h e l e s n o m s d e s e m p l o y é s d u d é p a r t e m e n t ' C o m m e r c i a l ' q u i s o n t â g é s d e p l u s d e 4 0 a n s . U t i l i s e z u n c u r s e u r i m p l i c i t e d a n s u n e b o u c l e F O R . Solution : BEGIN FOR emp IN (SELECT * FROM EMPLOYE WHERE AGE >= 40 AND DEPARTEMENT = 'Commercial') LOOP DBMS_OUTPUT.PUT_LINE(emp.NOM) ; END LOOP END Exercice 11. Soit la relation EMPLOYE de l'exercice précédent. Écrivez une procédure PLSQL qui prends en paramètre un NUMBER (age limite) et qui affiche pour chaque département le nombre des employés qui dépassent l'age limite. Utilisez un curseur avec paramètre l'age limite. Solution : CREATE OR REPLACE PROCEDURE moyenneAge(AgeLim IN NUMBER) IS CURSOR CS(Age_Limite NUMBER) IS SELECT DEPARTEMENT AS DNOM, COUNT(*) AS NB FROM EMPLOYE WHERE AGE > Age_Limite GROUP BY DEPARTEMENT ; NFA011 NFA011 – Développement d’applications avec les bases de données BEGIN FOR DEPT IN CS(AgeLim) LOOP DBMS__OUTPUT.PUT_LINE(DEPT.DNOM || ' ' || DEPT.NB) END FOR END Exercice 12. Soit la table suivante : ME T E O ( N O M_ V I L L E , T e m p é r a t u r e , H u m i d i t é ) É c r i r e u n e f o n c t i o n P L / S Q L q u i p r e n d s e n e n t r é e l e n o m d ' u n e v i l l e e t r e t o u r n e l a t e m p é r a t u r e e t l ' h u m i d i t é d e c e t t e v i l l e . G é r e r a u s s i p a r u n e e x c e p t i o n l e c a s o u l a v i l l e n ' e x i s t e p a s . Solution : TYPE HumT emp IS RECORD( HUM METEO.HUMIDITY%TYPE, TEMP METEO.TEMPERATURE%TYPE ) FUNCTION GET_TEMPERATURE (PVILLE IN METEO.NOM_VILLE%TYPE) RETURN HumT emp IS VAL HUMTEMP; BEGIN VAL.HUM = -10000; VAL.TEMP = -10000; SELECT HUMIDITE, TEMPERATURE INTO VAL FROM METEO WHERE VILLE_NOM = PVILLE; RETURN VAL; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Ville n\'existe pas'); RETURN VAL; END; Exercice 13. Soit la table ME T E O d e l ' e x e r c i c e p r é c é d e n t . É c r i r e u uploads/Geographie/ ed-corrige-plsql 1 .pdf