Licence SMI 2007 – 2008

Ce document présente un ensemble d'exercices pratiques en PL/SQL destinés à évaluer la maîtrise des déclarations de variables, des blocs PL/SQL, des manipulations de données, des boucles, des curseurs, et de la gestion des exceptions. Ces exercices permettent de développer des compétences en programmation Oracle PL/SQL, en particulier dans l'insertion, la suppression, la mise à jour, la gestion des curseurs et des erreurs.

Exercice 1

Déterminer quelles déclarations de variables sont incorrectes parmi les propositions données.

• A - DECLARE v_id : Correcte. La déclaration d'une variable simple sans type explicite est possible si le type est implicite ou par défaut.
• B - DECLARE v_x,v_y,v_z : Incorrecte. En PL/SQL, chaque variable doit être déclarée sur une ligne distincte avec son type. Ici, plusieurs identifiants sont déclarés sur une seule ligne sans type.
• C - DECLARE v_date_naissance DATE NOT NULL; : Incorrecte. Une variable déclarée NOT NULL doit être initialisée immédiatement, ce qui n'est pas le cas ici.
• D - DECLARE v_en_stock BOOLEAN := 1; : Incorrecte. En PL/SQL, le type BOOLEAN ne peut prendre que TRUE, FALSE ou NULL, pas un entier comme 1.
• E - DECLARE emp_record EMP_RECORD_TYPE; : Incorrecte. Le type EMP_RECORD_TYPE n'est pas déclaré dans le contexte, donc la variable ne peut pas être définie.
• F - Déclaration d'un type TABLE et d'une variable dept_table_nom : Correcte. La syntaxe est valide pour déclarer un type TABLE indexé par BINARY_INTEGER et une variable de ce type.

Conclusion : Les déclarations B, C, D, et E sont incorrectes, tandis que A et F sont correctes.

Exercice 2

1 - Créer un bloc PL/SQL pour insérer un nouveau département dans la table DEPARTEMENTS, en utilisant une séquence pour l'ID et un paramètre pour le nom, avec la région à NULL.

a) Le bloc PL/SQL suivant utilise la séquence DEPT_ID_SEQ pour générer l'ID et un paramètre pour le nom :

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) En exécutant ce bloc avec la valeur "Santé" :

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

c) Pour vérifier l'insertion :

SQL> SELECT * FROM departements 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, avec un paramètre pour l'ID et affichage du nombre de lignes affectées.

a) Le bloc PL/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) Test avec un numéro de département inexistant (134) :

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

c) Test avec un département ayant des employés (31) :

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

La suppression échoue car des employés sont liés à ce département.

d) Test 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).

Vérification que le département n'existe plus :

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

Conclusion : Le bloc fonctionne correctement pour insérer et supprimer un département, avec gestion des contraintes d'intégrité référentielle.

Exercice 3

1 - Créer un bloc PL/SQL pour mettre à jour le pourcentage de commission d’un employé selon le total de ses ventes.

Avant l'exercice, il faut supprimer la contrainte sur la colonne commission :

ALTER TABLE employes DROP CONSTRAINT employes_commission_ck;

a) Le bloc PL/SQL doit :

• Prendre en paramètre un numéro d’employé.
• Calculer la somme totale des commandes traitées par cet employé.
• Mettre à jour la commission selon les règles :
• Somme < 100,000 → commission = 10
• 100,000 ≤ Somme ≤ 1,000,000 → commission = 15
• Somme > 1,000,000 → commission = 20
• Aucune commande → commission = 0
• Valider la modification avec COMMIT.

Le bloc proposé (complété) :

ACCEPT p_id PROMPT 'Entrer un numéro de vendeur : '
DECLARE
   v_somme_total NUMBER(11,2);
   v_comm employes.commission%TYPE;
BEGIN
   SELECT NVL(SUM(total), 0)
   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) Test avec les employés 1, 11, 12, 14 :

SQL> SELECT id, commission FROM employes 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 sur les régions (1 à 5) pour modifier la solvabilité des clients selon la parité du numéro de région, sans valider (pas de COMMIT).

• Si le numéro de région est pair, mettre la solvabilité à "EXCELLENTE".
• Sinon, mettre la solvabilité à "BONNE".
• Afficher un message selon le nombre de lignes modifiées :
• Moins de 3 lignes modifiées : afficher "Moins de trois lignes ont été modifiées pour la région x".
• Sinon : afficher "y lignes ont été modifiées pour la région x".
• Annuler les modifications avec ROLLBACK.

Bloc PL/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

Conclusion : Ce bloc met à jour la solvabilité selon la parité de la région, affiche le nombre de lignes modifiées par région, puis annule les modifications.

Exercice 4

Créer un bloc PL/SQL pour déterminer les employés ayant les plus hauts salaires.

a) Créer une table meilleurs pour stocker les noms et salaires :

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

b) Créer un bloc PL/SQL avec un paramètre n pour récupérer les n meilleurs employés selon leur salaire, en utilisant un curseur et une boucle FOR.

Le bloc :

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;
    EXIT WHEN emp_cursor%NOTFOUND;
    INSERT INTO meilleurs(nom, salaire)
    VALUES (emp_record.nom, emp_record.salaire);
  END LOOP;
  CLOSE emp_cursor;
  COMMIT;
END;
/

c) Tests :

• Avec n=4 :

SQL> start p4q1
Entrer une valeur numérique : 4
PL/SQL procedure successfully completed.

SELECT nom, TO_CHAR(salaire,'fm$9,999,999') salaire FROM meilleurs;

NOM         SALAIRE
---------------------------
Velasquez   $2,500
Ropeburn   $1,550
Nguyen     $1,525
Sedeghi    $1,515

• Avec n=0 :

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

• Avec n=30 (plus que le nombre d'employés) :

SQL> start p4q1
Entrer une valeur numérique : 30
PL/SQL procedure successfully completed.

-- Affiche les 25 employés avec leurs salaires, car il n'y a que 25 employés.

Après chaque test, la table meilleurs est vidée avec TRUNCATE TABLE meilleurs.

Conclusion : Le bloc fonctionne correctement pour extraire les n meilleurs salaires, avec gestion des cas limites.

Exercice 5

Modifier un bloc PL/SQL pour gérer les exceptions lors de la mise à jour du numéro de région d’un département.

a) Bloc initial :

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écution avec département 50 et région "US" :

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

Erreur car la région "US" n'existe pas.

c) Modification pour gérer l'exception NO_DATA_FOUND :

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

d) Test avec département 31 et région "Asie" :

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

Violation de contrainte unique car un département du même nom existe déjà dans cette région.

e) Gestion de l'exception DUP_VAL_ON_INDEX :

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

Test :

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) Test avec département 99 et région Europe :

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

Problème : le département 99 n'existe pas, mais le message indique qu'il a été affecté.

g) Gestion de l'exception pour département inexistant avec levée manuelle :

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

Test :

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

Conclusion : Le bloc gère désormais correctement les exceptions liées à une région inexistante, à un doublon de département dans une région, et à un département inexistant.

Méthode

Ce sujet récompense une approche rigoureuse et progressive des blocs PL/SQL :

• Respecter la syntaxe et les règles de déclaration des variables, notamment la nécessité d'initialiser les variables NOT NULL.
• Utiliser les séquences pour générer des identifiants uniques lors des insertions.
• Gérer les contraintes d'intégrité référentielle en testant les suppressions et en anticipant les erreurs.
• Utiliser les structures conditionnelles pour appliquer des règles métier (comme la mise à jour des commissions).
• Maîtriser les boucles et curseurs pour parcourir des ensembles de données.
• Gérer les exceptions explicitement, en particulier NO_DATA_FOUND, DUP_VAL_ON_INDEX, et les cas où aucune ligne n'est affectée (SQL%NOTFOUND).
• Afficher des messages clairs à l'utilisateur, notamment en cas d'erreur, pour faciliter le diagnostic.

Les erreurs fréquentes pénalisées sont :

• Oublier d'initialiser une variable NOT NULL.
• Ne pas gérer les exceptions, ce qui conduit à des arrêts brutaux.
• Ne pas vérifier l'existence des données avant mise à jour ou suppression.
• Confondre les types, notamment pour les variables BOOLEAN.
• Ne pas utiliser SQL%ROWCOUNT ou SQL%NOTFOUND pour contrôler les effets des requêtes DML.

Une bonne méthode consiste à écrire un code clair, commenté, avec des tests intermédiaires et une gestion complète des erreurs.