TD2 : PL/SQL avec Oracle
Exercice 1 :
Soit la table EMPLOYE qui contient des informations sur les employés de l’établissement
ESP. On souhaite déterminer la proportion de managers parmi eux.
§ Recopier la table [Link] sur votre compte sous le nom EMP à l’aide de la
requête SQL : CREATE TABLE EMP AS SELECT * FROM [Link]
§ Ecrire un bloc PL/SQL anonyme permettant de :
o Compter le nombre total de n-uplets dans la table EMP et stocker le résultat
dans une variable ;
o Compter le nombre d’employés dont la fonction (JOB) est « Maître-assistant »
dans la table EMP et stocker le résultat dans une deuxième variable ;
o Calculer la proportion (en pourcentage), stocker le résultat dans une troisième
variable et afficher le résultat à l’écran.
§ 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-uplet dans EMP est égal
à zéro. Dans ce cas, déclencher une erreur fatale (on ne peut pas permettre une division
par zéro). Testez le bon fonctionnement de l’exception en suivant la procédure
suivante :
o Valider les mises à jour précédentes à l’aide de la commande SQL COMMIT ;
o Effacer le contenu de la table EMP (DELETE FROM EMP) ;
o Exécuter le bloc PL/SQL ;
o Annuler l’effacement de la table EMP à l’aide de la commande SQL
ROLLBACK.
Exercice 2 : curseur explicite
1. Recopier la table ESP. ELEMENT _MODULE_INFO sur votre compte (cf Exercice
1) ;
2. Écrire un bloc PL/SQL anonyme permettant d’afficher le nom
(ELEMENT_MODULE_INFO.NOM) et la liste de 3 éléments de modules les plus
importants en terme des nombre de crédit de la table ELEMENT_MODULE_INFO ;
§ Indication :
o Définir un curseur qui liste les éléments de modules par nombre de crédit
décroissant ;
o Effectuer un parcours explicite du curseur qui s’arrête tant que 3 n-uplet
n’ont pas été lus.
Exercice 3 : 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
commande (ORDRE). La table ORDRE 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.
Système de gestion de base de données 1
§ É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 ORDRE à l’unique attribut QTY.
Exemple :
Indication :
§ Recopier la table [Link] 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) ;
§ Exepction : interrompre le programme immédiatement si la table
ORDRE contient moins de deux contraintes values. Pour ce type de
traitements, on utilise habituellement une requête COUNT.
Système de gestion de base de données 2