M1 Informatique – Année 2017-2018 Bases de données avancées TD n° 1 (PL/SQL) : Requêtes, curseurs et exceptions J. Darmont (http://eric.univ-lyon2.fr/~jdarmont/), 24/08/17 Aide-mémoire PL/SQL Bloc PL/SQL Déclaration de variable Affectation Tests Boucles Curseurs Exceptions - Déclarer - Utiliser - Déclarer - Lever - Traiter Documentation PL/SQL DECLARE -- Déclarations : types, curseurs, constantes, -- variables, sous-programmes BEGIN -- Instructions du programme principal EXCEPTION -- Traitement des erreurs à l’exécution END; nom_variable TYPE_VARIABLE; nom_variable := valeur; SELECT attribut INTO nom_variable FROM table; IF condition1 THEN -- Instructions ELSIF condition2 THEN -- (Optionnel) -- Instructions ELSE -- (Optionnel) -- Instructions END IF; CASE nom_variable WHEN valeur 1 THEN -- Instructions WHEN valeur 2 THEN -- Instructions ELSE -- Instructions END CASE; FOR iterateur IN [REVERSE] min..max LOOP -- Instructions END LOOP; WHILE condition LOOP -- Instructions END LOOP; LOOP -- Répéter jusqu’à -- Instructions EXIT WHEN condition END LOOP; CURSOR nom_curseur IS requete_SQL; FOR nuplet IN nom_curseur LOOP -- Instructions -- Ex. nom_variable := nuplet.attribut; END LOOP;-- NB : nuplet est de type nom_curseur%ROWTYPE nom_exception EXCEPTION; RAISE nom_exception; WHEN nom_exception THEN -- Instructions; http://docs.oracle.com/database/121/LNPLS/toc.htm Logiciel client : SQL Developer • Menu “Démarrer / Tous les programmes / Oracle – OraClientversion / Développement d’applications”. • Exécution d’un bloc PL/SQL depuis la fenêtre SQL : touche F5. • Activation de l’affichage dans la fenêtre de résultat : onglet Sortie SGBD / icône M1 Informatique – Bases de données avancées – TD 1 . 1/3 Exercice 1 : Requêtes simples et exception Soit la table EMP qui stocke des informations sur les employés d’une entreprise. On souhaite déterminer la proportion de managers parmi eux. 1. Recopier la table DARMONT.EMP sur votre compte, sous le nom EMP à l’aide de la requête SQL CREATE TABLE emp AS SELECT * FROM darmont.emp. 2. Écrire un bloc PL/SQL anonyme permettant de : • compter le nombre total de n-uplets dans la table EMP et stocker le résultat dans une variable ; • compter le nombre d’employés dont la fonction (JOB) est MANAGER dans la table EMP et stocker le résultat dans une deuxième variable ; • calculer la proportion (en pourcentage), stocker le résultat dans une troisième variable et afficher le résultat à l’écran. 3. Tester ! 4. Inclure dans le programme précédent un traitement d’exception permettant de détecter si la table EMP est vide, c’est-à-dire, que le nombre total de n-uplets dans EMP est égal à zéro. Dans ce cas, déclencher une erreur fatale (on ne peut pas permettre une division par zéro). Tester le bon fonctionnement de l’exception en suivant la procédure suivante : 1. valider les mises à jour précédentes à l’aide de la commande SQL COMMIT (ou touche F11) ; 2. effacer le contenu de la table EMP (DELETE FROM emp) ; 3. exécuter le bloc PL/SQL ; 4. annuler l’effacement de la table EMP à l’aide de la commande SQL ROLLBACK (ou touche F12). Exercice 2 : Curseur implicite 1. Créer une vue quelconque (ex. CREATE VIEW nom_emp AS SELECT ename FROM emp). 2. Écrire un bloc PL/SQL anonyme permettant d’afficher votre catalogue système (liste des tables et des vues de votre compte disponible grâce à la vue système TAB) sous la forme : L’objet UNE_TABLE est de type TABLE. L’objet UNE_AUTRE_TABLE est de type TABLE. L’objet UNE_VUE est de type VIEW. … Indication : Tester au préalable sous SQL l’exécution de la requête SELECT * FROM TAB pour avoir une idée du contenu de la vue système TAB. Exercice 3 : Curseur explicite 1. Recopier la table DARMONT.DEMO_PRODUCT_INFO sur votre compte (cf. Exercice 1). 2. Écrire un bloc PL/SQL anonyme permettant d’afficher le nom (PRODUCT_NAME) et le prix (LIST_PRICE) des 5 produits les plus chers de la table DEMO_PRODUCT_INFO. Indications : • Définir un curseur qui liste les produits par ordre de prix décroissant. • Effectuer un parcours explicite du curseur qui s’arrête tant que 5 n-uplets n’ont pas été lus. M1 Informatique – Bases de données avancées – TD 1 2/3 Exercice 4 : Curseur explicite et exception Afin d’établir une corrélation, on souhaite connaître la différence de quantité moyenne entre les commandes successivement enregistrées dans une table de commandes (ORD). La table ORD est remplie de commandes valuées (c’est-à-dire, pour lesquelles l’attribut quantité QTY n’est pas NULL) ou non. Les commandes non valuées ne sont pas à prendre en compte. Écrire un bloc PL/SQL anonyme permettant de calculer la différence de quantité moyenne entre les commandes. Pour simplifier, on pourra la réduire la table ORD à l’unique attribut QTY. Exemple : QTY 5 NULL 10 8 9 13 Résultat attendu = ( |10 – 5| + |8 – 10| + |9 – 8| + |13 – 9| ) / 4 = 3 Indications : • • • • Recopier la table DARMONT.ORD sur votre compte. Créer un curseur contenant les quantités des commandes valuées (…WHERE QTY IS NOT NULL). À l’aide d’un parcours explicite du curseur, lire la première quantité puis, pour toutes les quantités suivantes, cumuler la valeur absolue de quantité courante – quantité précédente (fonction ABS). Exception : Interrompre le programme immédiatement si la table ORD contient moins de deux contraintes values. Pour ce type de traitements, on utilise habituellement une requête COUNT. M1 Informatique – Bases de données avancées – TD 1 3/3 Correction -- Exercice 1 DECLARE ntot INTEGER; nman INTEGER; pman REAL; personne EXCEPTION; ----- Nombre total d’employés Nombre de managers Proportion de managers Exception : pas d’employés BEGIN SELECT COUNT(*) INTO ntot FROM emp; IF (ntot = 0) THEN RAISE personne; END IF; SELECT COUNT(*) INTO nman FROM emp WHERE job = 'MANAGER'; pman := 100 * nman / ntot; DBMS_OUTPUT.PUT_LINE('Proportion de managers = ' || pman || ' %'); EXCEPTION WHEN personne THEN RAISE_APPLICATION_ERROR(-20500, 'La table EMP est vide !'); END; -- Exercice 2 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; -- Exercice 3 DECLARE CURSOR ranking IS SELECT product_name, list_price FROM demo_product_info ORDER BY list_price DESC; p ranking%ROWTYPE; BEGIN OPEN ranking; FETCH ranking INTO p; -- Premier produit WHILE ranking%FOUND AND ranking%ROWCOUNT <= 5 LOOP DBMS_OUTPUT.PUT_LINE(ranking%ROWCOUNT || ') ' || p.product_name || ' : ' || p.list_price || ' €'); FETCH ranking INTO p; -- Produit suivant END LOOP; CLOSE ranking; END; M1 Informatique – Bases de données avancées – TD 1 4/3 -- Exercice 4 DECLARE CURSOR com_val IS SELECT qty FROM ord WHERE qty IS NOT NULL; prec com_val%ROWTYPE; -- n-uplet précédent cour com_val%ROWTYPE; -- n-uplet courant total REAL := 0; n_val INTEGER; pas_assez EXCEPTION; BEGIN -- Test du nombre de commandes valuées SELECT COUNT(*) INTO n_val FROM ord WHERE qty IS NOT NULL; IF n_val < 2 THEN RAISE pas_assez; END IF; -- Accès 1er n-uplet OPEN com_val; FETCH com_val INTO prec; -- 1er précédent -- Accès aux suivants et cumul FETCH com_val INTO cour; -- 1er courant WHILE com_val%FOUND LOOP total := total + ABS(cour.qty - prec.qty); prec := cour; -- Le courant devient le précédent FETCH com_val INTO cour; -- Courant suivant END LOOP; CLOSE com_val; -- Calcul et affichage de la moyenne DBMS_OUTPUT.PUT_LINE('Moyenne = ' || (total / (n_val - 1))); EXCEPTION WHEN pas_assez THEN RAISE_APPLICATION_ERROR(-20501, 'Pas assez de commandes !'); END; M1 Informatique – Bases de données avancées – TD 1 5/3