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) ; 2013 / 2014 NFA011 – Développement d’applications avec les bases de données END 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; 2013 / 2014 NFA011 – Développement d’applications avec les bases de données CREATE OR REPLACE FUNCTION FACT_REC (N INTEGER) RETURN INTEGER IS BEGIN IF (N < 0) THEN 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. 2013 / 2014 NFA011 – Développement d’applications avec les bases de données Solution : CREATE OR REPLACE TRIGGER VérificationDisque BEFORE INSERT ON PARTITION FOR EACH ROW /* nécessaire pour avoir accès à :NEW */ DECLARE tailleTotale PARTITION.taille%TYPE = 0; capacitéDisque DISQUE.capacité%TYPE = 0; BEGIN SELECT SUM(taille) INTO tailleTotale FROM PARTITION WHERE nomDisque = :NEW.nomDisque; SELECT capacité INTO capacitéDisque FROM DISQUE WHERE nom = :NEW.nomDisque; IF tailleTotale + :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 (INSERT, UPDATE, DELETE, SELECT, etc). 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. Écrivez un bloc PL/SQL qui affiche les noms des employés du département 'Commercial' qui sont âgés de plus de 40 ans. Utilisez un curseur implicite dans une boucle FOR. Solution : BEGIN FOR emp IN (SELECT * FROM EMPLOYE WHERE AGE >= 40 AND DEPARTEMENT = 'Commercial') 2013 / 2014 NFA011 – Développement d’applications avec les bases de données 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 ; BEGIN FOR DEPT IN CS(AgeLim) LOOP DBMS__OUTPUT.PUT_LINE(DEPT.DNOM || ' ' || DEPT.NB) END FOR END Exercice 12. Soit la table suivante : METEO(NOM_VILLE, Température, Humidité) Écrire une fonction PL/SQL qui prends en entrée le nom d'une ville et retourne la température et l'humidité de cette ville. Gérer aussi par une exception le cas ou la ville n'existe pas. Solution : TYPE HumTemp IS RECORD( HUM METEO.HUMIDITY%TYPE, TEMP METEO.TEMPERATURE%TYPE ) FUNCTION GET_TEMPERATURE (PVILLE IN METEO.NOM_VILLE%TYPE) RETURN HumTemp 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 METEO de l'exercice précédent. Écrire un déclencheur qui avant l'insertion d'une nouvelle ville dans la table vérifie : 2013 / 2014 NFA011 – Développement d’applications avec les bases de données a. Si la température est la plus grande de toutes les villes, afficher un message d'avertissement. b. Si la ville existe déjà dans la table, ne pas l’insérer une nouvelle fois mais faire la mis a jour seulement. Solution : CREATE OR REPLACE TRIGGER MTRIGGER BEFORE UPDATE ON METEO FOR EACH ROW DECLARE TMAX NUMBER; NB NUMBER := 0; BEGIN SELECT MAX(Temperature) INTO TMAX FROM METEO ; SELECT COUNT(*) INTO NB FROM METEO M WHERE M.VILLE_NOM = :NEW.VILLE_NOM; IF (:NEW.TEMPERATURE > TMAX) THEN DBMS_OUTPUT.PUT_LINE(20001,'Temperature MAX'); ELSIF NB = 0 THEN UPDATE METEO SET TEMPERATURE =:NEW.TEMPERATURE WHERE VILLE_NOM = :NEW.VILLE_NOM ; RAISE_APPLICATION_ERROR(20001, 'La ville existe déjà'); END IF END; Exercice 14. On considère la base de données suivante : COMPETITION(CODE_COMP, NOM_COMPETITION) PARTICIPANT(NO_PART, NOM_PART, DATENAISSANCE, ADRESSE, EMAIL) SCORE(NO_PAR, CODE_COMP, NO_JUGE, NOTE) Écrire un bloc PLSQL qui lit à la console le nom d'une compétition et qui affiche les participants avec leur score total (la somme de tous les scores par tous les juges). Utilisez un curseur avec paramètre. Solution : ACCEPT cnom 'Nom de la compétition : '; DECLARE NOMC VARCHAR2(10) := &cnom; CURSOR C(PNOM COMPETITION.NOM_COMP%TYPE)IS SELECT NOM_PART, SUM(NOTE) AS TOTAL FROM COMPETITION C, PARTICIPANT P, SCORE S WHERE C.CODE_COMP = S.CODE_COMP AND S.NO_PART = P.NO_PART AND C.NOM_COMP = PNOM GROUP BY NOM_PART; BEGIN FOR I IN C(NOMC) LOOP DBMS_OUTPUT.PUT_LINE(I.NOM_PART || ' ' || I.TOTAL) END LOOP END Exercice 15. On considère la table COMPETITION donné dans l'exercice précédent. Écrire un déclencheur qui vérifie que le code d'une compétition commence par les lettres 'CMP' avant son l'insertion dans la table COMPETITION. Solution : CREATE OR REPLACE TRIGGER VERIFIE_CODE_COMP 2013 / 2014 NFA011 – Développement d’applications avec les bases de données BEFORE INSERT OR UPDATE ON COMPETITION FOR EACH ROW WHEN (:NEW.CODE_COMP NOT uploads/Geographie/ ed-corrige-plsql 2 .pdf