Database Operations Examples

Ce laboratoire propose une série d'exemples pratiques d'opérations sur une base de données Oracle, notamment la suppression, la modification, la lecture et l'insertion de données dans les tables emp et dept. Il enseigne la manipulation des curseurs, la gestion des exceptions, l'utilisation des procédures stockées et fonctions PL/SQL. Pour réaliser ce TP, un accès à une base Oracle avec les tables emp et dept est nécessaire, ainsi que la possibilité d'exécuter des scripts PL/SQL.

Objectifs

• Supprimer un département donné en gérant les contraintes d'intégrité.
• Modifier le salaire d'un employé selon son nom.
• Afficher des listes d'employés filtrées par fonction et département.
• Mettre à jour les salaires des employés sous un certain seuil.
• Insérer dans une table la liste des employés par responsable ou par département.
• Créer et utiliser une fonction pour calculer le revenu total d'un employé.
• Écrire des procédures stockées pour afficher des informations sur les employés et départements.
• Gérer les exceptions et afficher des messages d'erreur ou de succès.

Prérequis et configuration

• Base de données Oracle avec les tables emp et dept déjà créées.
• Accès à SQL*Plus ou un outil équivalent permettant d'exécuter des scripts PL/SQL.
• Connaissances de base en SQL et PL/SQL, notamment sur les curseurs, exceptions et procédures stockées.
• Activation de l'affichage des sorties serveur avec la commande set serveroutput on.

Suppression d'un département donné

Cette étape montre comment supprimer un département en demandant à l'utilisateur de saisir le numéro du département. Elle affiche le nombre d'enregistrements supprimés et gère le cas où aucun département ne correspond.

set serveroutput on
VARIABLE g_result VARCHAR2(50);
ACCEPT p_deptno PROMPT 'Donner le numéro du département :';

DECLARE
  v_result NUMBER(2);
BEGIN
  DELETE FROM dept WHERE deptno = &p_deptno;
  dbms_output.put_line('La suppression de ' || SQL%ROWCOUNT || ' enregistrement(s)');
  v_result := SQL%ROWCOUNT;
  :g_result := TO_CHAR(v_result) || ' enregistrement(s) supprimé(s)';
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('Aucun enregistrement n’a été trouvé');
  END IF;
END;
/

Pour gérer l'erreur liée à la suppression d'un département qui a des employés (clé étrangère), on utilise une exception spécifique :

set serveroutput on

DECLARE
  emp_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(emp_exist, -2292);
  v_deptno dept.deptno%TYPE := 20;
BEGIN
  DELETE FROM dept WHERE deptno = v_deptno;
  COMMIT;
EXCEPTION
  WHEN emp_exist THEN
    DBMS_OUTPUT.PUT_LINE('Suppression Impossible du dept: ' || TO_CHAR(v_deptno) || ' Employés existants');
END;
/

Un résultat correct affiche le nombre d'enregistrements supprimés ou un message d'erreur si la suppression est impossible.

Modification du salaire d'un employé par son nom

Cette étape permet de modifier le salaire d'un employé donné par son nom, en affichant un message de succès ou d'erreur si l'employé n'est pas trouvé.

SET SERVEROUTPUT ON;
ACCEPT nom PROMPT 'Donner le nom de l''employé : ';

DECLARE
  nomemp emp.ename%TYPE := '&nom';
  OK EXCEPTION;
  Non_trouve EXCEPTION;
BEGIN
  UPDATE emp SET sal = 200 WHERE ename = nomemp;
  IF SQL%NOTFOUND THEN
    RAISE Non_trouve;
  ELSE
    RAISE OK;
  END IF;
EXCEPTION
  WHEN Non_trouve THEN
    DBMS_OUTPUT.PUT_LINE('Employé non trouvé ! ! !');
  WHEN OK THEN
    DBMS_OUTPUT.PUT_LINE('MODIFICATION FAITE');
END;
/

Un résultat correct affiche "MODIFICATION FAITE" ou "Employé non trouvé".

Affichage des employés CLERK du département 20 et SALESMAN du département 30

On utilise un curseur paramétré pour afficher les employés selon leur fonction et département, suivi du nombre d'employés affichés.

DECLARE
  CURSOR emp_cursor(p_job VARCHAR2, p_deptno NUMBER) IS
    SELECT empno, ename FROM emp WHERE job = p_job AND deptno = p_deptno;
  emp_record emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor('CLERK', 20);
  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_record.empno || ' ' || emp_record.ename);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Nombre d''employés: ' || emp_cursor%ROWCOUNT);
  CLOSE emp_cursor;

  OPEN emp_cursor('SALESMAN', 30);
  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_record.empno || ' ' || emp_record.ename);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Nombre d''employés: ' || emp_cursor%ROWCOUNT);
  CLOSE emp_cursor;
END;
/

Le résultat affiche la liste des employés CLERK du département 20, leur nombre, puis les SALESMAN du département 30 et leur nombre.

Mise à jour des salaires inférieurs à 2000 dans le département 20

Cette étape utilise un curseur avec clause FOR UPDATE OF sal NOWAIT pour parcourir les employés du département 20 et augmenter leur salaire de 10% si celui-ci est inférieur à 2000.

DECLARE
  CURSOR emp_cursor IS
    SELECT ename, sal, deptno FROM emp WHERE deptno = 20 FOR UPDATE OF sal NOWAIT;
BEGIN
  FOR emp_rec IN emp_cursor LOOP
    IF emp_rec.sal < 2000 THEN
      DBMS_OUTPUT.PUT_LINE(emp_rec.ename || ' ' || emp_rec.sal);
      UPDATE emp SET sal = sal * 1.1 WHERE CURRENT OF emp_cursor;
    END IF;
  END LOOP;
  COMMIT;
END;
/

Un résultat correct affiche les noms et salaires des employés concernés avant la mise à jour, puis applique la modification.

Insertion dans une table des messages pour chaque responsable la liste de ses employés

Cette procédure crée une table messages puis insère pour chaque responsable la liste de ses employés.

DROP TABLE messages;
CREATE TABLE messages (msg VARCHAR2(100));
SET serveroutput ON;

DECLARE
  CURSOR C1 IS SELECT DISTINCT mgr FROM emp ORDER BY mgr;
  CURSOR C2(v_mgr NUMBER) IS SELECT empno FROM emp WHERE mgr = v_mgr ORDER BY mgr;
  v_resp C1%ROWTYPE;
  v_emp C2%ROWTYPE;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO v_resp;
    EXIT WHEN C1%NOTFOUND;
    IF v_resp.mgr IS NOT NULL THEN
      INSERT INTO messages VALUES ('responsable: ' || v_resp.mgr);
    END IF;
    IF C2%ISOPEN THEN
      CLOSE C2;
    END IF;
    OPEN C2(v_resp.mgr);
    LOOP
      FETCH C2 INTO v_emp;
      EXIT WHEN C2%NOTFOUND;
      INSERT INTO messages VALUES ('employé : ' || v_emp.empno);
    END LOOP;
    CLOSE C2;
  END LOOP;
  CLOSE C1;
END;
/

Le contenu de la table messages doit contenir une entrée "responsable: <numéro>" suivie des employés correspondants.

Insertion dans une table Résultat des noms d'employés par département

On insère dans la table resultat pour chaque département les noms de ses employés sous forme d'une chaîne concaténée.

DECLARE
  CURSOR v_cursor1 IS
    SELECT deptno, dname FROM dept;
  v_record1 v_cursor1%ROWTYPE;
  CURSOR v_cursor2(v_deptno dept.deptno%TYPE) IS
    SELECT ename FROM emp WHERE deptno = v_deptno;
  v_record2 v_cursor2%ROWTYPE;
  v_msg VARCHAR2(100) := ' ';
BEGIN
  OPEN v_cursor1;
  LOOP
    FETCH v_cursor1 INTO v_record1;
    EXIT WHEN v_cursor1%NOTFOUND;
    OPEN v_cursor2(v_record1.deptno);
    LOOP
      FETCH v_cursor2 INTO v_record2;
      EXIT WHEN v_cursor2%NOTFOUND;
      v_msg := v_msg || v_record2.ename || ', ';
    END LOOP;
    INSERT INTO resultat VALUES (v_record1.deptno, v_record1.dname, v_msg);
    CLOSE v_cursor2;
    v_msg := '';
  END LOOP;
  CLOSE v_cursor1;
  COMMIT;
END;
/

Un résultat correct est l'insertion dans resultat de chaque département avec la liste concaténée des employés.

Insertion dans une table messages des employés par département

Cette procédure insère dans la table messages une ligne pour chaque département, suivie des employés qui y travaillent.

DROP TABLE messages;
CREATE TABLE messages (msg VARCHAR2(100));
SET serveroutput ON;

DECLARE
  CURSOR C1 IS SELECT DISTINCT deptno FROM emp ORDER BY deptno;
  CURSOR C2(v_deptno NUMBER) IS
    SELECT empno, ename FROM emp WHERE deptno = v_deptno;
  v_dept C1%ROWTYPE;
  v_emp C2%ROWTYPE;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO v_dept;
    EXIT WHEN C1%NOTFOUND;
    INSERT INTO messages VALUES ('département numéro: ' || v_dept.deptno);
    IF C2%ISOPEN THEN
      CLOSE C2;
    END IF;
    OPEN C2(v_dept.deptno);
    LOOP
      FETCH C2 INTO v_emp;
      EXIT WHEN C2%NOTFOUND;
      INSERT INTO messages VALUES ('l’employé ' || v_emp.empno || ' ' || v_emp.ename);
    END LOOP;
    CLOSE C2;
  END LOOP;
  CLOSE C1;
END;
/

Le contenu de messages doit afficher les départements et leurs employés respectifs.

Fonction total_revenu : calcul du revenu total des employés avec commission dans le même département qu'un employé donné

Cette fonction retourne la somme des salaires et commissions des employés ayant une commission dans le même département qu'un employé identifié par son numéro.

CREATE OR REPLACE FUNCTION total_revenu(numemp IN emp.empno%TYPE) RETURN NUMBER IS
  total NUMBER := 0;
  CURSOR emp_cursor IS
    SELECT sal, comm FROM emp
    WHERE deptno = (SELECT deptno FROM emp WHERE empno = numemp)
      AND comm IS NOT NULL;
  TYPE emp_record_type IS RECORD (
    com emp.comm%TYPE,
    salaire emp.sal%TYPE
  );
  emp_record emp_record_type;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    total := total + (emp_record.salaire + emp_record.com);
  END LOOP;
  CLOSE emp_cursor;
  RETURN total;
END;
/

Exécution et test :

VARIABLE x NUMBER
EXECUTE :x := total_revenu(7369);
PRINT x

Le résultat attendu pour l'exemple donné est 5950.

Procédure afficher_tables : afficher le nom de toutes les tables utilisateur

Cette procédure affiche la liste des noms de tables présentes dans le schéma utilisateur.

SET serveroutput ON;

CREATE OR REPLACE PROCEDURE afficher_tables IS
  CURSOR afficher_nom IS
    SELECT table_name FROM user_tables;
BEGIN
  dbms_output.put_line('Nom des tables :');
  FOR nom IN afficher_nom LOOP
    dbms_output.put_line(nom.table_name);
  END LOOP;
END;
/

Exécution :

EXECUTE afficher_tables;

Le résultat affiche la liste des tables existantes.

Procédure Interr_emp : afficher nom, salaire et commission d’un employé donné

Cette procédure prend en entrée un numéro d'employé et retourne son nom, salaire et commission.

CREATE OR REPLACE PROCEDURE Interr_emp(
  v_id IN emp.empno%TYPE,
  v_name OUT emp.ename%TYPE,
  v_salary OUT emp.sal%TYPE,
  v_comm OUT emp.comm%TYPE)
IS
BEGIN
  SELECT ename, sal, comm INTO v_name, v_salary, v_comm FROM emp WHERE empno = v_id;
END Interr_emp;
/

Procédure TEST : afficher nom et ville d’un département donné

Cette procédure affiche le nom et la localisation d’un département donné par son numéro.

SET serveroutput ON;
SET verify OFF;

CREATE OR REPLACE PROCEDURE TEST(
  n IN dept.deptno%TYPE,
  nom OUT dept.dname%TYPE,
  ville OUT dept.loc%TYPE)
IS
BEGIN
  SELECT dname, loc INTO nom, ville FROM dept WHERE deptno = n;
END;
/

Programme principal pour tester :

ACCEPT numero PROMPT 'Donner le numéro du dept : ';
DECLARE
  dnom dept.dname%TYPE;
  dville dept.loc%TYPE;
BEGIN
  TEST(&numero, dnom, dville);
  dbms_output.put_line('Le nom du département est : ' || dnom || ' situé à : ' || dville);
END;
/

Procédure emp_lieu : afficher le lieu de travail d’un employé par son nom

Cette procédure affiche le nom du département où travaille un employé donné (nom supposé unique).

SET serveroutput ON;

CREATE OR REPLACE PROCEDURE emp_lieu(empname IN emp.ename%TYPE) IS
  lieu_travail dept.dname%TYPE;
BEGIN
  SELECT dname INTO lieu_travail FROM dept
  WHERE deptno IN (SELECT deptno FROM emp WHERE ename = empname);
  dbms_output.put_line(empname || ' travaille à ' || lieu_travail);
END;
/

Procédure ENSEMBLES : afficher les employés travaillant avec un employé donné

Cette procédure affiche la liste des employés appartenant au même département qu’un employé donné, à l’exception de cet employé lui-même.

SET serveroutput ON;

CREATE OR REPLACE PROCEDURE ENSEMBLES(empnum IN emp.empno%TYPE) IS
  CURSOR C_ENS IS
    SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE empno = empnum);
  rec C_ENS%ROWTYPE;
  nom_emp emp.ename%TYPE;
BEGIN
  SELECT ename INTO nom_emp FROM emp WHERE empno = empnum;
  dbms_output.put_line('Les employés qui travaillent avec l’employé ' || nom_emp || ' sont :');
  FOR rec IN C_ENS LOOP
    IF rec.ename != nom_emp THEN
      DBMS_OUTPUT.PUT_LINE(rec.ename);
    END IF;
  END LOOP;
END;
/

Résultats attendus

• Suppression : message indiquant le nombre d'enregistrements supprimés ou impossibilité si employés liés.
• Modification salaire : confirmation "MODIFICATION FAITE" ou message d'erreur si employé absent.
• Affichage employés : listes correctes des employés CLERK et SALESMAN avec leur nombre.
• Mise à jour salaires : affichage des employés concernés et augmentation effective.
• Insertion messages : table messages contenant les responsables et leurs employés.
• Fonction total_revenu : calcul correct du total des salaires + commissions.
• Procédures d’affichage : affichage correct des noms, salaires, commissions, lieux et départements.
• Procédure ENSEMBLES : liste des collègues d’un employé dans le même département.

Pièges courants

• Ne pas activer set serveroutput on empêche d’afficher les messages DBMS_OUTPUT.
• Oublier de gérer l’exception lors de la suppression d’un département lié à des employés.
• Utiliser un nom d’employé non unique dans la procédure emp_lieu peut causer une erreur.
• Ne pas fermer les curseurs ouverts peut provoquer des erreurs ou fuites de ressources.
• Confondre les variables de curseur et les variables locales dans les boucles.
• Ne pas faire de commit après les modifications empêche la persistance des données.
• Dans les procédures avec paramètres OUT, ne pas récupérer les valeurs après l’appel.
• Concaténer les noms d’employés sans réinitialiser la variable v_msg peut produire des résultats erronés.