Exercices Corrigés PL/SQL TD SQL Oracle TP Langage PL/SQL ave correction

Exercice 1 :

  Parmi les déclarations de variables suivantes, déterminer celles qui sont incorrectes :
 
A -    DECLARE
          v_id     NUMBER(4);
Correcte

B -    DECLARE
         v_x,v_y,v_z    VARCHAR2(10);
Incorrecte : un seul identifiant par ligne

C -   DECLARE
         v_date_naissance DATE NOT NULL;
Incorrecte : une valeur NOT NULL doit être initialisée

D -    DECLARE
          v_en_stock    BOOLEAN := 1;
Incorrecte : 1 n’est pas une valeur booléenne

E -    DECLARE
         emp_record    emp_record_type;
Incorrecte : EMP_RECORD_TYPE doit être déclaré

F -    DECLARE
         TYPE      type_table_nom IS TABLE OF VARCHAR2(20)
         INDEX BY BINARY_INTEGER;
         dept_table_nom     type_table_nom;
Correcte

Exercice 2 :
 

1 - Créer un bloc PL/SQL pour insérer un nouveau département dans la table DEPARTEMENTS
 

a) Utiliser  la séquence DEPT_ID_SEQ pour générer un numéro de département. Créer un paramètre pour  le nom du département. Laisser le numéro de région à NULL.

Fichier p2q1.sql

       
ACCEPT p_dept_nom PROMPT ‘Entrer un nom de département : ‘
BEGIN
    INSERT INTO departements(id, nom, region_id)
    VALUES (dept_id_seq.NEXTVAL, ‘&p_dept_nom’, NULL);
COMMIT;
END;
/

b) Exécuter le bloc PL/SQL avec la valeur “Santé” pour le nom du département.

 
SQL> start p2q1
Entrer un nom de département : Santé
PL/SQL procedure successfully completed.

c) Afficher le nouveau département créé

 
SQL> SELECT * FROM departements
  2  WHERE nom = 'Santé';
       ID NOM                       REGION_ID
--------- ------------------------- ---------
       82 Santé

2 - Créer un bloc PL/SQL pour supprimer le département créé précédemment

a) Créer un paramètre pour le numéro de département. Faire afficher à l’écran le nombre de lignes affectées.

Fichier p2q2.sql

ACCEPT p_dept_id PROMPT 'Entrer un numéro de département : '
VARIABLE g_mess VARCHAR2(30)
DECLARE
   v_resultat NUMBER(2);
BEGIN
   DELETE FROM departements
   WHERE id = &p_dept_id;
      v_resultat := SQL%ROWCOUNT;
      :g_mess := TO_CHAR(v_resultat)||' ligne(s) supprimée(s).';
   COMMIT;
END;
/
PRINT g_mess

b) Tester le bloc. Que se passe-t-il si on saisit un numéro de département qui n’existe pas?  

SQL> start p2q2
Entrer un numéro de département : 134
PL/SQL procedure successfully completed.
G_MESS
--------------------------------
0 ligne(s) supprimée(s).

Si on saisit un numéro de département qui a des employés?

 
SQL> start p2q2
Entrer un numéro de département : 31
DECLARE
*
ERROR at line 1:
ORA-02292: integrity constraint (BOHEZ.EMPLOYES_DEPT_ID_FK) violated - child record found
ORA-06512: at line 4

c) Tester le bloc avec le département Santé (82)

SQL> start p2q2
Entrer un numéro de département : 82
PL/SQL procedure successfully completed.
G_MESS
--------------------------------
1 ligne(s) supprimée(s).

d) Vérifier que le département n’existe plus

SQL> SELECT *
  2  FROM departements
  3  WHERE id = 82;
no rows selected 

Exercice 3 :

 

1 - Créer un bloc PL/SQL permettant de mettre à jour le pourcentage de commission d’un employé en fonction du total de ses ventes

Cet exercice nécessite la suppression de la contrainte sur la colonne commission de la table EMPLOYES :

SQL> ALTER TABLE employes
    2     DROP CONSTRAINT employes_commission_ck;

a) Créer un paramètre qui reçoit un numéro d’employé

     Trouver la somme totale de toutes les commandes traitées par cet employé
     Mettre à jour le pourcentage de commission de l’employé :
- si la somme est inférieure à 100,000 passer la commission à 10
- si la somme est comprise entre 100,000 et 1,000,000 inclus passer la commission à 15
- si la somme excède 1,000,000 passer la commission à 20
- si aucune commande n’existe pour cet employé, mettre la commission à 0
     Valider la modification (commit)

Fichier p3q1.sql.

ACCEPT p_id PROMPT 'Entrer un numéro de vendeur : '
DECLARE
   v_somme_total NUMBER(11,2);
   v_comm employes.commission%TYPE;
BEGIN
   SELECT  SUM(total)
   INTO    v_somme_total
   FROM   commandes
   WHERE  vendeur_id = &p_id;

   IF v_somme_total < 100000 THEN
  v_comm := 10;
   ELSIF v_somme_total <= 1000000 THEN
  v_comm := 15;
   ELSIF v_somme_total > 1000000 THEN
  v_comm := 20;
   ELSE 
  v_comm := 0; 

   END IF;
   UPDATE employes

   SET commission = v_comm
   WHERE id = &p_id;
   COMMIT;
END;
/

b) Tester le bloc et visualiser les résultats (on teste avec les employés 1,11,12,14)

 
SQL> SELECT id, commission
  2       FROM employes
  3       WHERE id IN (1,11,12,14);
       ID COMMISSION
--------- ----------
       14         10
       12         15
       11         20
        1          0

2 - Créer un bloc PL/SQL qui boucle pour chaque région (le numéro des régions va de 1 à 5) afin de modifier  le code de solvabilité de tous les clients. Ne pas valider (pas de commit).

     Si  le numéro de région est pair mettre  la solvabilité à EXCELLENTE (même si elle  l’est déjà), sinon mettre à BONNE pour les numéros de région impairs.

     Une fois les lignes modifiées, trouvez combien de lignes ont été mises à jour.

     Afficher les résultats suivants en fonction du nombre de lignes modifiées :

• Si  moins  de  trois  lignes  ont  été  modifiées,  afficher  :  ‘Moins  de  trois  lignes  ont  été  modifiées pour la région x’ (x étant le numéro de la région).
• Sinon afficher  :  ‘y  lignes ont été modifiées pour  la  région x’(y étant  le nombre de  lignes modifiées).

     Annuler les modifications  (rollback). 

Fichier p3q2.sql.

VARIABLE g_mess VARCHAR2(500)
DECLARE
   v_sortie   VARCHAR2(500);
   v_modifies  NUMBER(2);
   v_solvable  VARCHAR2(25);
   c_peu  CONSTANT VARCHAR2(100) 
    := 'Moins de 3 lignes ont été modifiées pour la région ';

BEGIN
   FOR i IN 1..5 LOOP

      IF MOD(i,2) <> 0 THEN
  v_solvable := 'EXCELLENTE';
      ELSE 

    v_solvable := 'BONNE';
      END IF;

      UPDATE clients
  SET solvabilite = v_solvable
  WHERE region_id = i;
  v_modifies := SQL%ROWCOUNT;

      IF v_modifies < 3 THEN
  v_sortie := v_sortie||c_peu||TO_CHAR(i)||CHR(10);
      ELSE
   v_sortie := v_sortie||TO_CHAR(v_modifies)||
    ' lignes ont été modifiées pour la région '||TO_CHAR(i)||CHR(10);
      END IF;
   END LOOP;
   :g_mess := v_sortie;
END;
/
PRINT g_mess 

Exercice 4 :

 
Créer un bloc PL/SQL qui détermine les employés de plus haut salaire.

a) Créer pour cet exercice une nouvelle table pour stocker les employés et leurs salaires

SQL> CREATE TABLE meilleurs
    2     (nom  VARCHAR2(25),
    3     salaire  NUMBER(11,2));

b) Utiliser un paramètre pour prendre une valeur n en entrée pour identifier les n meilleurs.

 Ecrire une boucle WHILE  avec  curseur pour  récupérer  le nom  et  salaire des n meilleurs  employés  selon  leur salaire dans la table EMPLOYES

    Enregistrer les noms et salaires dans la table MEILLEURS.
 
    On suppose qu’aucun employé n’a le même salaire qu’un autre.

c) Tester le bloc avec différents cas tels que n=0 ou n supérieur au nombre total d’employés (25).

     Vider  la table MEILLEURS après chaque test.

Fichier p4q1.sql.

ACCEPT p_n PROMPT 'Entrer une valeur numérique : '
DECLARE
  CURSOR emp_cursor IS
    SELECT nom, salaire
    FROM employes
    WHERE salaire IS NOT NULL
    ORDER BY salaire DESC;
  emp_record emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    FOR i IN 1..&p_n
    LOOP 
  FETCH emp_cursor INTO emp_record;
  INSERT INTO meilleurs(nom, salaire)
  VALUES (emp_record.nom,emp_record.salaire);
    END LOOP;
    CLOSE emp_cursor;
    COMMIT;
END;
/
SELECT nom,TO_CHAR(salaire,'fm$9,999,999') salaire FROM meilleurs;
TRUNCATE TABLE meilleurs

Test du bloc avec n=4 

 

SQL> start p4q1
Entrer une valeur numérique : 4
PL/SQL procedure successfully completed.
NOM                 SALAIRE
-----------------------------   -------------
Velasquez                   $2,500
Ropeburn                    $1,550
Nguyen                      $1,525
Sedeghi                     $1,515
Table truncated.

Test du bloc avec n=0

SQL> start p4q1
Entrer une valeur numérique : 0
PL/SQL procedure successfully completed.
no rows selected
Table truncated.

Test du bloc avec n=30

SQL> START p4q1
Entrer une valeur numérique : 30
PL/SQL procedure successfully completed.
NOM                         SALAIRE
-------------------------   -----------
Velasquez                   $2,500
Ropeburn                    $1,550
Nguyen                      $1,525
Sedeghi                     $1,515
Giljum                      $1,490 

Ngao                        $1,450
Quick-To-See                $1,450
Dumas                       $1,450
Nagayama                    $1,400
Maduro                      $1,400
Magee                       $1,400
Havel                       $1,307
Catchpole                   $1,300
Menchu                      $1,250
Urguhart                    $1,200
Nozaki                      $1,200
Biri                        $1,100
Schwartz                    $1,100
Smith                       $940
Dancs                       $860
Markarian                   $850
Chang                       $800
Patel                       $795
Patel                       $795
Newman                      $750
Newman                      $750
Newman                      $750
Newman                      $750
Newman                      $750
Newman                      $750
30 rows selected.
Table truncated.

Exercice 5 :

Modifier le bloc PL/SQL fourni pour gérer les exceptions.    

Le traitement essaie de mettre à jour des numéros de région pour des départements existants.

a) Charger le fichier p5qa.sql. 

  

ACCEPT p_dept_id PROMPT 'Numéro de département : '
ACCEPT p_nom_region PROMPT 'Nom de région : '
VARIABLE g_mess VARCHAR2(50)
DECLARE
  v_region_id  regions.id%TYPE;
BEGIN
  SELECT   id
   INTO  v_region_id
   FROM  regions
   WHERE   UPPER(nom)=UPPER('&p_nom_region');
  UPDATE  departements
   SET  region_id=v_region_id
   WHERE   id = &p_dept_id;
  :g_mess := 'Le département : '||TO_CHAR(&p_dept_id)||
  ’ est affecté à la région '||TO_CHAR(v_region_id);
  COMMIT;
END;
/
PRINT g_mess

b) Exécuter le bloc avec comme valeur 50 pour le numéro de département et US pour le nom de région.

SQL> start p5qa
Numéro de département : 50
Nom de région : US
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
G_MESS
-----------------------------------------------------------------

c) Sauvegarder  le  fichier p5qa.sql sous  le nom p5q1.sql. Modifier p5q1.sql pour écrire un  traitement d’exception pour l’anomalie constatée afin de passer un message à l’utilisateur lorsque la région spécifiée n’existe pas.

ACCEPT p_dept_id PROMPT 'Numéro de département : ' 

ACCEPT p_nom_region PROMPT 'Nom de région : '
VARIABLE g_mess VARCHAR2(50)
DECLARE
  v_region_id  regions.id%TYPE;
BEGIN
  SELECT   id
   INTO  v_region_id
   FROM  regions
   WHERE   UPPER(nom)=UPPER('&p_nom_region');
  UPDATE  departements
   SET  region_id=v_region_id
   WHERE   id = &p_dept_id;
  :g_mess := 'Le département : '||TO_CHAR(&p_dept_id)||
  ’ est affecté à la région '||TO_CHAR(v_region_id);
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
   ROLLBACK;
   :g_mess := '&p_nom_region'||' : région inexistante.';
END;
/
PRINT g_mess
SQL> start p5q1
Numéro de département : 50
Nom de région : US
PL/SQL procedure successfully completed.
G_MESS
---------------------------------------------------------------------
US : région inexistante.

d) Exécuter le bloc avec comme valeur 31 pour le numéro de département et Asie pour le nom de région.

SQL> START p5q1
Numéro de département : 31
Nom de région : Asie
DECLARE
* 

ERROR at line 1:
ORA-00001: unique constraint (BOHEZ.DEPARTEMENTS_NOM_ET_REGION_UK) violated
ORA-06512: at line 9
G_MESS
---------------------------------------------------------------------------

e) Ecrire un  traitement d’exception pour  l’anomalie constatée afin de passer un message à  l’utilisateur  lorsque  la région spécifiée est déjà référencée par un département du même nom

ACCEPT p_dept_id PROMPT 'Numéro de département : '
ACCEPT p_nom_region PROMPT 'Nom de région : '
VARIABLE g_mess VARCHAR2(80)
DECLARE
  v_dept  VARCHAR2(20);
  v_region_id  regions.id%TYPE;
BEGIN
  SELECT   id
   INTO  v_region_id
   FROM  regions
   WHERE   UPPER(nom)=UPPER('&p_nom_region');
  UPDATE  departements
   SET  region_id=v_region_id
   WHERE   id = &p_dept_id;
  :g_mess := 'Le département : '||TO_CHAR(&p_dept_id)||
  ’ est affecté à la région '||TO_CHAR(v_region_id);
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
   ROLLBACK;
   :g_mess := '&p_nom_region'||' : région inexistante.'; 

  WHEN DUP_VAL_ON_INDEX THEN
   ROLLBACK;
   SELECT   nom||' (N°'||TO_CHAR(id)||')'
    INTO  v_dept
    FROM   departements
    WHERE   region_id = v_region_id
    AND nom = 
  (SELECT   nom
  FROM   departements
  WHERE   id = &p_dept_id);
   :g_mess := 'Il existe déjà un département '||v_dept||
  ' pour la région '||'&p_nom_region';
END;
/
PRINT g_mess 

SQL> start p5q1
Numéro de département : 31
Nom de région : Asie
PL/SQL procedure successfully completed.
G_MESS
--------------------------------------------------------------------
Il existe déjà un département Ventes (N°34) pour la région Asie 

f) Exécuter le bloc avec comme valeur 99 pour le numéro de département et Europe pour le nom de la région

SQL> start p5q1
Numéro de département : 99
Nom de région : Europe
PL/SQL procedure successfully completed.
G_MESS
----------------------------------------------------------------------------------
Le département : 99 est affecté à la région 5

 
Anomalie constatée : la procédure ne remarque pas que le département 99 n’existe pas.

g) Ecrire un traitement d’exception pour l’anomalie constatée afin de passer un message à l’utilisateur lorsque le numéro de département spécifié n’existe pas.

     Rappel : penser à utiliser l’attribut SQL%NOTFOUND et déclencher une exception manuellement

 

ACCEPT p_dept_id PROMPT 'Numéro de département : '
ACCEPT p_nom_region PROMPT 'Nom de région : '
VARIABLE g_mess VARCHAR2(80)
DECLARE
  v_dept  VARCHAR2(20);
  v_region_id  regions.id%TYPE;
  e_count  EXCEPTION;
BEGIN
  SELECT   id
   INTO  v_region_id
   FROM  regions
   WHERE   UPPER(nom)=UPPER('&p_nom_region');
  UPDATE  departements
   SET  region_id=v_region_id
   WHERE   id = &p_dept_id;
  IF SQL%NOTFOUND THEN
   RAISE e_count;
  END IF;
  :g_mess := 'Le département : '||TO_CHAR(&p_dept_id)||
  ' est affecté à la région '||TO_CHAR(v_region_id);
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
   ROLLBACK;
   :g_mess := '&p_nom_region'||' : région inexistante.';
  WHEN DUP_VAL_ON_INDEX THEN
   ROLLBACK;
   SELECT   nom||' (N°'||TO_CHAR(id)||')' 

    INTO  v_dept
    FROM   departements
    WHERE   region_id = v_region_id
    AND nom = 
  (SELECT   nom
  FROM   departements
  WHERE   id = &p_dept_id);
   :g_mess := 'Il existe déjà un département '||v_dept||
  ' pour la région '||'&p_nom_region';
  WHEN e_count THEN
   ROLLBACK;
   :g_mess := 'Le département numéro '||TO_CHAR(&p_dept_id)||' n''existe pas.';
END;
/
PRINT g_mess

SQL> start p5q1
Numéro de département : 99
Nom de région : Europe
PL/SQL procedure successfully completed.
G_MESS
--------------------------------------------------------------------
Le département numéro 99 n'existe pas

Structure et Données des Tables 

SQL>desc  departements 

SQL>SELECT  * FROM departements

 

SQL>desc  Commandes 

SQL>desc  employes 

SQL>SELECT  * FROM employes