Base de Données Avancées

Oracle PL/SQL

Cours pratique et résolution de TP

Enseignant

Mohamed Mohamed Beirouk

Contact

beirouk.ext@iscae.mr

Établissement

ISCAE

sqlplus_terminal
-- Initialisation du cours
SET SERVEROUTPUT ON;

BEGIN
  -- Message de bienvenue
  DBMS_OUTPUT.PUT_LINE(
    'Bienvenue dans Oracle PL/SQL'
  );
  DBMS_OUTPUT.PUT_LINE(
    'Préparé pour l''ISCAE'
  );
END;
/
PL/SQL procedure successfully completed.

Année Académique

2025-2026

Introduction à PL/SQL

Concepts Fondamentaux

02

Qu'est-ce que PL/SQL ?

Procedural Language extensions to SQL. C'est le langage procédural propriétaire d'Oracle qui combine la puissance de manipulation de données du SQL avec la puissance de traitement des langages procéduraux.

Pourquoi utiliser PL/SQL ?

  • Performance : Exécution côté serveur, réduisant le trafic réseau.

  • Sécurité : Contrôle d'accès granulaire via procédures stockées.

  • Modularité : Développement par blocs logiques réutilisables.

Application
Moteur PL/SQL
Procédural SQL
Oracle DB

Exemple : Hello World

Code 1.0
script_intro.sql
-- Activation de l'affichage dans SQL*Plus / SQL Developer SET SERVEROUTPUT ON; -- Bloc PL/SQL Anonyme BEGIN -- Affichage d'un message simple DBMS_OUTPUT.PUT_LINE('PL/SQL prêt !'); -- Calcul simple pour tester le moteur DBMS_OUTPUT.PUT_LINE('Calcul 5*5 = ' || (5*5)); END; / -- Résultat attendu : -- PL/SQL prêt ! -- Calcul 5*5 = 25

Blocs PL/SQL : structure et exemples

Les fondations du langage

03

Anatomie d'un Bloc

DECLARE -- Optionnel

Déclaration de variables, curseurs, types, exceptions locales.

BEGIN Obligatoire

Instructions exécutables : SQL (DML) et PL/SQL.

EXCEPTION -- Optionnel

Gestion des erreurs d'exécution.

END;

Types de blocs

Anonyme

Non nommé, non stocké dans la BD. Compilé et exécuté à la volée.

Nommé

Procédures, Fonctions, Triggers, Packages. Stockés et réutilisables.

Exemple : Bloc avec Exception

Code 2.0
structure_demo.sql
DECLARE -- Section déclarative v_x NUMBER := 10; v_y NUMBER := 0; v_z NUMBER; BEGIN -- Section exécutable DBMS_OUTPUT.PUT_LINE('Début du calcul...'); -- Cette instruction va provoquer une erreur v_z := v_x / v_y; -- Cette ligne ne sera jamais atteinte si erreur DBMS_OUTPUT.PUT_LINE('z = ' || v_z); EXCEPTION -- Section de gestion des exceptions WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Erreur interceptée : Division par zéro impossible !'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Une erreur inconnue est survenue.'); END; /

Les Conditions en PL/SQL

Structures de Contrôle

04

Instruction IF

Permet l'exécution conditionnelle de blocs de code. Trois variantes principales :

  • IF-THEN - Condition simple
  • IF-THEN-ELSE - Alternative binaire
  • IF-THEN-ELSIF - Choix multiples

Instruction CASE

Alternative plus lisible aux séries de IF imbriqués. Deux formes :

CASE Simple

Compare une expression à une liste de valeurs.

CASE Recherché

Évalue une série de conditions booléennes (WHEN condition).

Conseil : Utilisez CASE plutôt que de multiples ELSIF lorsque vous testez une seule variable contre plusieurs valeurs constantes pour une meilleure lisibilité.

Exemple : Évaluation de notes

Code 4.0
conditions_demo.sql
DECLARE v_note NUMBER := 13.5; v_app VARCHAR2(20); BEGIN -- 1. Utilisation de IF ... ELSIF ... ELSE IF v_note >= 16 THEN v_app := 'A (Très Bien)'; ELSIF v_note >= 12 THEN v_app := 'B (Bien)'; ELSE v_app := 'C (Moyen)'; END IF; DBMS_OUTPUT.PUT_LINE('Appréciation : ' || v_app); -- 2. Utilisation de CASE (Recherché) CASE WHEN v_note < 10 THEN DBMS_OUTPUT.PUT_LINE('Résultat : Ajourné'); WHEN v_note BETWEEN 10 AND 12 THEN DBMS_OUTPUT.PUT_LINE('Résultat : Passable'); ELSE DBMS_OUTPUT.PUT_LINE('Résultat : Admis'); END CASE; END; /

Les Boucles en PL/SQL

Structures Itératives

05

Les 3 Types de Boucles

PL/SQL propose trois structures itératives principales pour répéter des instructions. La gestion correcte des sorties de boucle est essentielle pour éviter les boucles infinies.

Caractéristiques Principales

  • LOOP Simple

    Boucle de base. Nécessite une clause EXIT WHEN pour s'arrêter.

  • WHILE Loop

    Répète tant que la condition est VRAIE. Condition évaluée avant chaque itération.

  • FOR Loop

    Itère un nombre de fois défini (ex: 1..10). Compteur géré automatiquement.

EXIT Arrêt immédiat
CONTINUE Itération suivante

Exemple Pratique : 3 Syntaxes

Code 5.0
boucles_demo.sql
DECLARE i PLS_INTEGER := 1; BEGIN -- 1. LOOP Simple avec EXIT WHEN LOOP DBMS_OUTPUT.PUT_LINE('LOOP i='||i); i := i + 1; EXIT WHEN i > 3; -- Condition de sortie END LOOP; -- 2. WHILE Loop i := 1; WHILE i <= 3 LOOP DBMS_OUTPUT.PUT_LINE('WHILE i='||i); i := i + 1; END LOOP; -- 3. FOR Loop (compteur j automatique) FOR j IN 1..3 LOOP IF j = 2 THEN CONTINUE; -- Sauter itération 2 END IF; DBMS_OUTPUT.PUT_LINE('FOR j='||j); END LOOP; END; /

Les Curseurs en PL/SQL

Traitement ligne par ligne

06

Concept & Types

Un curseur est une zone mémoire privée SQL utilisée pour traiter plusieurs lignes retournées par une requête SELECT ou DML.

Implicite

Géré automatiquement par Oracle (INSERT, UPDATE, DELETE, SELECT INTO).

Explicite

Déclaré par le programmeur pour les requêtes multi-lignes.

Cycle de vie (Curseur Explicite)

1
DECLARE
2
OPEN
3
FETCH
4
CLOSE
%FOUND / %NOTFOUND : Ligne trouvée ou non ?
%ROWCOUNT : Nombre de lignes traitées.
%ISOPEN : Le curseur est-il ouvert ?

Exemple : Curseur Explicite

Loop & Fetch
cursor_demo.sql
DECLARE -- 1. Déclaration du curseur CURSOR c_mod IS SELECT code_module, titre FROM modules; -- Variables pour stocker les données v_code modules.code_module%TYPE; v_titre modules.titre%TYPE; BEGIN -- 2. Ouverture : exécution de la requête OPEN c_mod; LOOP -- 3. Récupération ligne par ligne FETCH c_mod INTO v_code, v_titre; -- Condition de sortie (Attribut %NOTFOUND) EXIT WHEN c_mod%NOTFOUND; DBMS_OUTPUT.PUT_LINE( '#' || c_mod%ROWCOUNT || ': ' || v_code || ' - ' || v_titre ); END LOOP; -- 4. Fermeture : libération mémoire CLOSE c_mod; END; /

Gestion des Exceptions

Fiabilité et Contrôle d'Erreurs

07

Mécanisme d'Exception

En PL/SQL, les erreurs ne plantent pas le programme brutalement. Elles lèvent des exceptions qui peuvent être interceptées (caught) et traitées proprement dans la section EXCEPTION.

Catégories d'Exceptions

  • Prédéfinies (Oracle)

    Erreurs standards nommées : NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE.

  • Définies par l'Utilisateur

    Erreurs métier spécifiques déclarées avec le type EXCEPTION.

  • RAISE_APPLICATION_ERROR

    Procédure pour renvoyer une erreur personnalisée à l'application (codes -20000 à -20999).

Exemple : Exception Personnalisée

Code 7.0
exceptions_demo.sql
DECLARE -- Déclaration d'une exception nommée e_coeff_invalide EXCEPTION; -- Association au code d'erreur -20001 PRAGMA EXCEPTION_INIT(e_coeff_invalide, -20001); v_coeff NUMBER := -1; BEGIN -- Validation métier IF v_coeff < 0 THEN -- Lève une erreur personnalisée avec message RAISE_APPLICATION_ERROR(-20001, 'Coefficient négatif interdit'); END IF; DBMS_OUTPUT.PUT_LINE('Coefficient validé.'); EXCEPTION -- Interception spécifique WHEN e_coeff_invalide THEN DBMS_OUTPUT.PUT_LINE('Erreur métier : ' || SQLERRM); -- Interception générique (sécurité) WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Erreur inattendue.'); END; /

Les Procédures

Blocs Nommés & Modulaires

08

Syntaxe & Définition

Une procédure est un sous-programme stocké qui exécute une action spécifique. Elle est créée avec CREATE OR REPLACE PROCEDURE.

Contrairement aux fonctions, une procédure ne retourne pas obligatoirement une valeur via RETURN, mais peut utiliser des paramètres de sortie.

Modes de Paramètres

  • IN

    Entrée (Défaut)

    Passe une valeur à la procédure. Lecture seule.

  • OUT

    Sortie

    Renvoie une valeur à l'environnement appelant.

  • IN OUT

    Entrée & Sortie

    Passe une valeur initiale et retourne une valeur modifiée.

Bonnes Pratiques

Valider toujours les paramètres entrants et gérer les exceptions à l'intérieur de la procédure pour éviter les erreurs non contrôlées.

Exemple : Mise à jour Email

Code 8.0
proc_update_email.sql
CREATE OR REPLACE PROCEDURE p_maj_email( p_matricule IN etudiants.matricule%TYPE, p_email IN etudiants.email%TYPE ) IS BEGIN -- Tentative de mise à jour UPDATE etudiants SET email = p_email WHERE matricule = p_matricule; -- Vérification si une ligne a été touchée IF SQL%ROWCOUNT = 0 THEN RAISE_APPLICATION_ERROR(-20010, 'Matricule introuvable : ' || p_matricule); END IF; -- Validation de la transaction COMMIT; DBMS_OUTPUT.PUT_LINE('Email mis à jour avec succès.'); EXCEPTION WHEN OTHERS THEN ROLLBACK; -- Annulation en cas d'erreur RAISE_APPLICATION_ERROR(-20011, 'Erreur MAJ: ' || SQLERRM); END; / -- Appel de la procédure EXEC p_maj_email('ETU001', 'nouveau.email@iscae.mr');

Les Fonctions en PL/SQL

Valeurs de retour et utilisation SQL

09

Définition

Une fonction est un sous-programme nommé qui prend des paramètres et retourne obligatoirement une valeur unique. Contrairement à une procédure, elle est conçue pour calculer et renvoyer un résultat.

Caractéristiques Principales

  • Clause RETURN obligatoire dans l'en-tête et le corps.

  • Utilisation en SQL : Peut être appelée directement dans une requête SELECT, WHERE, etc.

  • Idéale pour encapsuler des calculs ou des règles métier réutilisables.

Procédure

Exécute une action (DML). Retourne valeurs via paramètres OUT.

Fonction

Calcule une donnée. Retourne une valeur via RETURN.

Exemple : Fonction d'Arrondi

Code 9.0
f_arrondi.sql
-- Création de la fonction CREATE OR REPLACE FUNCTION f_arrondi2( p_val IN NUMBER ) RETURN NUMBER DETERMINISTIC IS BEGIN -- RETURN est obligatoire pour renvoyer le résultat RETURN ROUND(p_val, 2); END; / -- CAS 1: Utilisation dans un bloc PL/SQL DECLARE v_res NUMBER; BEGIN v_res := f_arrondi2(15.6789); DBMS_OUTPUT.PUT_LINE('Résultat: ' || v_res); END; / -- CAS 2: Utilisation directe en SQL (Puissant !) SELECT code_module, AVG(notecc) AS moyenne_brute, f_arrondi2(AVG(notecc)) AS moyenne_arrondie FROM evaluations GROUP BY code_module;

Les Triggers (Déclencheurs)

Automatisation et Audit

10

Définition

Un trigger est un bloc PL/SQL stocké qui s'exécute automatiquement (se "déclenche") en réponse à un événement spécifique sur la base de données (INSERT, UPDATE, DELETE).

Concepts Clés

  • Timing & Événements

    Timing: BEFORE, AFTER, INSTEAD OF (Vues).
    Events: INSERT, UPDATE, DELETE.

  • Niveau d'exécution

    Instruction (défaut) : une fois par commande SQL.
    Ligne (FOR EACH ROW) : une fois par ligne affectée.

  • Pseudo-enregistrements

    :OLD (ancienne valeur) et :NEW (nouvelle valeur).


User DML
BEFORE
Trigger
TABLE
Modification
AFTER
Trigger

Exemple : Auto-update Date

Trigger Niveau Ligne
trg_modules_touch.sql
-- Mettre à jour la date_maj avant toute modification CREATE OR REPLACE TRIGGER trg_modules_touch BEFORE UPDATE ON modules FOR EACH ROW BEGIN -- Accès au pseudo-record :NEW pour modifier -- la valeur qui sera insérée dans la table :NEW.date_maj := SYSDATE; -- Exemple de règle de gestion conditionnelle IF :NEW.coeff < 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Coeff invalide'); END IF; -- Comparaison possible -- IF :NEW.titre != :OLD.titre THEN ... END; / -- Test du trigger UPDATE modules SET titre = 'SQL Avancé' WHERE code_module = 'M101'; -- La colonne date_maj sera mise à jour automatiquement

Les Collections (Tableaux)

Structures de données composites

11

Types de Collections

PL/SQL propose trois types principaux pour stocker des ensembles de données de même type.

VARRAY (Variable-Size Array)

Borné

Taille fixe maximum définie à la création. Dense (pas de trous). Conserve l'ordre.

Table Imbriquée (Nested Table)

Non borné

Taille dynamique illimitée. Peut contenir des trous après suppression. Peut être stockée en base.

Table Associative (Index-by)

Mémoire

Paires Clé-Valeur (Hash Map). L'index peut être un nombre ou une chaîne (VARCHAR2).

Méthodes Essentielles

.EXTEND(n) .COUNT .DELETE(i) .FIRST .LAST .EXISTS(i) .TRIM

Exemple Complet

Code 11.0
demo_collections.sql
DECLARE -- 1. VARRAY (Taille Max = 5) TYPE t_varray IS VARRAY(5) OF PLS_INTEGER; v_va t_varray := t_varray(10, 20); -- Init avec 2 valeurs -- 2. Table Imbriquée (Dynamique) TYPE t_tab IS TABLE OF VARCHAR2(10); v_nt t_tab := t_tab(); -- Init vide obligatoire -- 3. Table Associative (Clé = String) TYPE t_assoc IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(10); v_aa t_assoc; -- Pas de constructeur nécessaire BEGIN -- Manipulation VARRAY v_va.EXTEND; -- Ajoute 1 slot (devient 3) v_va(3) := 30; -- Manipulation Table Imbriquée v_nt.EXTEND(2); -- Ajoute 2 slots v_nt(1) := 'Oracle'; v_nt(2) := 'Java'; v_nt.DELETE(1); -- Supprime index 1 (devient clairsemé) -- Manipulation Table Associative v_aa('prof') := 'Mr. Beirouk'; v_aa('cours') := 'PL/SQL'; -- Affichage DBMS_OUTPUT.PUT_LINE('Varray Count: ' || v_va.COUNT); DBMS_OUTPUT.PUT_LINE('Table 1st: ' || v_nt(2)); -- Index 1 supprimé DBMS_OUTPUT.PUT_LINE('Prof: ' || v_aa('prof')); END; /
TP EXERCICE 1

Boucles FOR et WHILE

Solution Pratique 12

Énoncé

En utilisant la boucle FOR, écrire un programme PL/SQL qui affiche 7 fois le mot « Bonjour ».

Note :

La boucle FOR est idéale lorsque le nombre d'itérations est connu à l'avance (ici 7).

Résultat Attendu

Le programme va itérer de 1 à 7 et exécuter l'instruction d'affichage à chaque passage.

Bonjour Bonjour Bonjour Bonjour Bonjour Bonjour Bonjour -- PL/SQL procedure successfully completed.
solution_ex1_for.sql
PL/SQL Oracle 19c+
-- 1. Activation de la sortie standard SET SERVEROUTPUT ON; DECLARE -- Variable compteur (optionnelle avec FOR, implicite) v_limite PLS_INTEGER := 7; BEGIN -- Début de la boucle FOR -- La variable 'i' est déclarée implicitement FOR i IN 1..v_limite LOOP -- Instruction à répéter DBMS_OUTPUT.PUT_LINE('Bonjour (itération ' || i || ')'); END LOOP; END; /
Syntaxe PL/SQL validée
Base de Données Avancées - TP Corrigé
TP EXERCICE 1 (Suite)

Boucles FOR et WHILE

Solution Pratique 13

Énoncé

En utilisant la boucle WHILE, écrire un programme PL/SQL qui affiche 7 fois le mot « Bonjour ».

Point d'attention :

Avec WHILE, vous devez gérer manuellement l'initialisation et l'incrémentation de la variable compteur pour éviter une boucle infinie.

Résultat Attendu

Le résultat est identique à la boucle FOR, mais la logique interne diffère.

Bonjour Bonjour Bonjour Bonjour Bonjour Bonjour Bonjour -- PL/SQL procedure successfully completed.
solution_ex1_while.sql
PL/SQL Oracle 19c+
-- 1. Activation de la sortie standard SET SERVEROUTPUT ON; DECLARE -- Initialisation OBLIGATOIRE avant la boucle i PLS_INTEGER := 1; BEGIN -- Condition vérifiée au début de chaque itération WHILE i <= 7 LOOP -- Instruction à exécuter DBMS_OUTPUT.PUT_LINE('Bonjour'); -- Incrémentation MANUELLE indispensable -- Si oublié => Boucle infinie ! i := i + 1; END LOOP; END; /
Syntaxe PL/SQL validée
Base de Données Avancées - TP Corrigé
TP EXERCICE 2-4

Collections (Tableaux VARRAY)

Solution Pratique 14

Objectifs

  • 2

    Créer un TYPE tableau (VARRAY) pouvant contenir jusqu'à 30 entiers.

  • 3

    Déclarer une variable, faire une allocation dynamique de 10 emplacements.

  • 4

    Remplir avec les 10 premiers carrés (4, 16, 36... 400).

Résultat Attendu

v_tab(1) = 4 v_tab(2) = 16 v_tab(3) = 36 v_tab(4) = 64 v_tab(5) = 100 v_tab(6) = 144 v_tab(7) = 196 v_tab(8) = 256 v_tab(9) = 324 v_tab(10) = 400 -- PL/SQL procedure successfully completed.
solution_collections.sql
SQL + PL/SQL Oracle
-- Étape 2 : Création du TYPE (Niveau Schéma) CREATE OR REPLACE TYPE t_int30 AS VARRAY(30) OF NUMBER; / DECLARE -- Étape 3 : Déclaration et initialisation v_tab t_int30 := t_int30(); BEGIN -- Allocation dynamique de 10 espaces v_tab.EXTEND(10); -- Étape 4 : Remplissage et Affichage FOR i IN 1..10 LOOP -- Calcul : Carré des nombres pairs (2*i)^2 -- i=1 -> 4, i=2 -> 16, etc. v_tab(i) := POWER(2 * i, 2); -- Affichage du résultat DBMS_OUTPUT.PUT_LINE('v_tab(' || i || ') = ' || v_tab(i)); END LOOP; -- Vérification taille finale DBMS_OUTPUT.PUT_LINE('Taille totale: ' || v_tab.COUNT); END; /
VARRAY .EXTEND .COUNT
Base de Données Avancées - TP Corrigé
TP EXERCICE 5

Fonction Module (GET_MODULE_TITRE)

Solution Pratique 15

Énoncé

Créez une fonction PL/SQL nommée GET_MODULE_TITRE qui prend en paramètre le code d'un module et retourne son titre.

La fonction doit gérer le cas où le code n'existe pas en levant une exception avec un message clair.

Point clé :

Utilisez l'exception NO_DATA_FOUND et RAISE_APPLICATION_ERROR pour une gestion d'erreur robuste.

Tests d'Exécution

Exemples d'appel SQL de la fonction :

-- Test 1: Succès SQL> SELECT get_module_titre('M101') FROM DUAL; Result: Base de Données -- Test 2: Erreur SQL> SELECT get_module_titre('XXX') FROM DUAL; ORA-20020: Module introuvable: XXX ORA-06512: at "GET_MODULE_TITRE", line 11
create_function_get_titre.sql
FUNCTION PL/SQL
CREATE OR REPLACE FUNCTION get_module_titre( p_code_module IN modules.code_module%TYPE ) RETURN modules.titre%TYPE IS -- Variable locale pour stocker le résultat v_titre modules.titre%TYPE; BEGIN -- Tentative de récupération du titre SELECT titre INTO v_titre FROM modules WHERE code_module = p_code_module; RETURN v_titre; EXCEPTION -- Gestion du cas où le module n'existe pas WHEN NO_DATA_FOUND THEN -- Levée d'une erreur utilisateur (-20000 à -20999) RAISE_APPLICATION_ERROR( -20020, 'Module introuvable: ' || p_code_module ); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20021, 'Erreur inattendue: ' || SQLERRM); END; /
Syntaxe PL/SQL validée
Base de Données Avancées - TP Corrigé
TP EXERCICE 6

Fonction Moyenne Étudiant

Solution Pratique 16

Énoncé

Créez une fonction PL/SQL qui prend en paramètre un matricule d'étudiant et retourne sa moyenne (moyenne de toutes ses évaluations CC).

Rappel :

Utilisez la fonction d'agrégation SQL AVG(). Si aucune note n'existe, la fonction retournera NULL, ce qui est correct.

Test & Résultat

Exemple d'appel via une requête SQL SELECT sur la table DUAL.

SQL> SELECT calculer_moyenne('ETU001') AS Moyenne FROM DUAL; MOYENNE ---------- 14.5 -- Test avec matricule inconnu: MOYENNE ---------- (null)
solution_ex6_moyenne.sql
FUNCTION AVG
CREATE OR REPLACE FUNCTION calculer_moyenne( p_matricule IN evaluations.matricule%TYPE ) RETURN NUMBER IS -- Variable pour stocker le résultat v_moy NUMBER(4,2); BEGIN -- Calcul direct de la moyenne des notes CC -- AVG ignore les NULLs, ROUND arrondit à 2 décimales SELECT ROUND(AVG(notecc), 2) INTO v_moy FROM evaluations WHERE matricule = p_matricule; -- Retourne le résultat (ou NULL si aucune note) RETURN v_moy; EXCEPTION WHEN OTHERS THEN -- Gestion générique d'erreur (optionnelle ici) DBMS_OUTPUT.PUT_LINE('Erreur: ' || SQLERRM); RETURN NULL; END; /
Code compilé et validé
TP Oracle PL/SQL - Fonctions
TP EXERCICE 6

Fonction Moyenne Étudiant

Solution Pratique 16

Énoncé

Créez une fonction PL/SQL qui prend en paramètre un matricule d'étudiant et retourne sa moyenne (moyenne de toutes ses évaluations CC).

Rappel :

Utilisez la fonction d'agrégation SQL AVG(). Si aucune note n'existe, la fonction retournera NULL, ce qui est correct.

Test & Résultat

Exemple d'appel via une requête SQL SELECT sur la table DUAL.

SQL> SELECT calculer_moyenne('ETU001') AS Moyenne FROM DUAL; MOYENNE ---------- 14.5 -- Test avec matricule inconnu: MOYENNE ---------- (null)
solution_ex6_moyenne.sql
FUNCTION AVG
CREATE OR REPLACE FUNCTION calculer_moyenne( p_matricule IN evaluations.matricule%TYPE ) RETURN NUMBER IS -- Variable pour stocker le résultat v_moy NUMBER(4,2); BEGIN -- Calcul direct de la moyenne des notes CC -- AVG ignore les NULLs, ROUND arrondit à 2 décimales SELECT ROUND(AVG(notecc), 2) INTO v_moy FROM evaluations WHERE matricule = p_matricule; -- Retourne le résultat (ou NULL si aucune note) RETURN v_moy; EXCEPTION WHEN OTHERS THEN -- Gestion générique d'erreur (optionnelle ici) DBMS_OUTPUT.PUT_LINE('Erreur: ' || SQLERRM); RETURN NULL; END; /
Code compilé et validé
TP Oracle PL/SQL - Fonctions
TP EXERCICE 8

Curseurs Paramétrés & Jointures

Solution Pratique 18

Énoncé

Créez un curseur qui récupère toutes les évaluations d'un étudiant spécifié par son matricule.

Affichez pour chaque ligne : le matricule, le code du module, le titre du module (depuis la table modules) et la noteCC.

Astuce :

Déclarez un curseur qui prend un paramètre (p_mat) et utilisez un JOIN naturel ou explicite entre les tables.

Résultat Attendu

Exemple d'exécution pour l'étudiant 'ETU001' :

--- Notes pour ETU001 --- M101 | Bases de Données : 14.5 M102 | Java Avancé : 16 M103 | Réseaux IP : 12 -- PL/SQL procedure successfully completed.
solution_ex8_cursor_join.sql
PL/SQL JOIN
DECLARE -- Déclaration du curseur avec paramètre CURSOR c_eval(p_mat VARCHAR2) IS SELECT e.matricule, e.code_module, m.titre, e.notecc FROM evaluations e JOIN modules m ON e.code_module = m.code_module WHERE e.matricule = p_mat ORDER BY e.code_module; -- Variables locales pour récupérer les données v_mat evaluations.matricule%TYPE; v_code evaluations.code_module%TYPE; v_titre modules.titre%TYPE; v_note evaluations.notecc%TYPE; BEGIN -- Ouverture du curseur pour un étudiant spécifique OPEN c_eval('ETU001'); DBMS_OUTPUT.PUT_LINE('--- Notes pour ETU001 ---'); LOOP -- Récupération ligne par ligne FETCH c_eval INTO v_mat, v_code, v_titre, v_note; -- Condition de sortie : plus de lignes trouvées EXIT WHEN c_eval%NOTFOUND; -- Affichage des résultats formatés DBMS_OUTPUT.PUT_LINE( v_code || ' | ' || RPAD(v_titre, 20) || ' : ' || v_note ); END LOOP; -- Fermeture obligatoire du curseur CLOSE c_eval; END; /
Syntaxe Oracle PL/SQL Standard
Base de Données Avancées - TP Corrigé
TP EXERCICE 9

Trigger Audit Modifications

Solution Pratique 19

Énoncé

Créez un déclencheur (trigger) pour enregistrer les modifications apportées aux étudiants (une nouvelle table qui contient l'ancienne valeur, la nouvelle valeur et la date de modification).

Stratégie :

1. Créer une table historique (log).

2. Trigger AFTER UPDATE.

3. Utiliser les pseudo-enregistrements :OLD et :NEW.

Table d'Audit

Structure recommandée pour AUDIT_ETUDIANTS :

ID_AUDIT (PK)

MATRICULE

ANCIEN_NOM / NOUVEAU_NOM

ANCIEN_PRENOM / NOUVEAU_PRENOM

DATE_MODIF

UTILISATEUR

solution_ex9_audit.sql
PL/SQL Trigger
-- 1. Création de la table d'audit pour stocker l'historique CREATE TABLE AUDIT_ETUDIANTS ( id_audit NUMBER GENERATED ALWAYS AS IDENTITY, matricule VARCHAR2(20), old_nom VARCHAR2(50), new_nom VARCHAR2(50), old_prenom VARCHAR2(50), new_prenom VARCHAR2(50), date_modif DATE, utilisateur VARCHAR2(30) ); / -- 2. Création du trigger AFTER UPDATE CREATE OR REPLACE TRIGGER trg_audit_etudiants AFTER UPDATE ON etudiants FOR EACH ROW BEGIN -- Insertion dans la table d'audit des valeurs AVANT (:OLD) et APRÈS (:NEW) INSERT INTO AUDIT_ETUDIANTS ( matricule, old_nom, new_nom, old_prenom, new_prenom, date_modif, utilisateur ) VALUES ( :OLD.matricule, :OLD.nom, :NEW.nom, :OLD.prenom, :NEW.prenom, SYSDATE, USER ); END; / -- Test : UPDATE etudiants SET nom = 'NouveauNom' WHERE matricule = 'ETU001';
Solution complète testée sur Oracle 19c
Base de Données Avancées - ISCAE
TP EXERCICE 10

Trigger de Suppression en Cascade

Solution Pratique 20

Énoncé

Mettre en place une suppression en cascade à l'aide de déclencheurs (triggers). Lorsqu'un étudiant est supprimé, toutes ses évaluations doivent être supprimées automatiquement pour éviter les erreurs d'intégrité référentielle.

Note :

C'est une alternative logicielle aux contraintes de clé étrangère ON DELETE CASCADE.

Mécanisme

Le trigger BEFORE DELETE s'exécute juste avant la suppression de l'étudiant, nettoyant d'abord les tables enfants.

SQL> DELETE FROM etudiants WHERE matricule = 'ETU001'; -- Le trigger s'active ici -- -- Suppression des évals de ETU001 -- 1 row deleted. SQL> SELECT count(*) FROM evaluations WHERE matricule = 'ETU001'; COUNT(*) ---------- 0
solution_ex10_cascade.sql
PL/SQL Trigger
-- Création du Trigger pour suppression en cascade CREATE OR REPLACE TRIGGER trg_cascade_etudiants -- Doit s'exécuter AVANT la suppression du parent BEFORE DELETE ON etudiants FOR EACH ROW BEGIN -- 1. Suppression des enregistrements enfants (évaluations) -- On utilise :OLD pour référencer la ligne qui va être supprimée DELETE FROM evaluations WHERE matricule = :OLD.matricule; -- Message de débogage (visible si serveroutput on) DBMS_OUTPUT.PUT_LINE('Cascade : Évaluations supprimées pour ' || :OLD.matricule); -- Note : Pas de COMMIT ici (géré par la transaction principale) EXCEPTION WHEN OTHERS THEN -- En cas d'erreur, on empêche la suppression de l'étudiant RAISE_APPLICATION_ERROR(-20010, 'Erreur lors de la suppression en cascade : ' || SQLERRM); END; /
Intégrité référentielle garantie
Base de Données Avancées - TP Corrigé

Bonnes Pratiques PL/SQL

Optimisation & Maintenance

21

Écrire du code qui fonctionne ne suffit pas. Un code PL/SQL professionnel doit être lisible, maintenable et performant pour supporter la charge des bases de données de production.

Conventions de Nommage

Utiliser des préfixes pour identifier la portée :

  • v_nom : Variable locale
  • p_nom : Paramètre de procédure/fonction
  • g_nom : Variable globale (package)
  • c_nom : Constante

Performance & Optimisation

Réduire les changements de contexte (Context Switches) :

  • Utiliser BULK COLLECT pour les requêtes ramenant beaucoup de lignes.

  • Utiliser FORALL pour les insertions/mises à jour de masse.

Robustesse

Toujours capturer les exceptions. Éviter WHEN OTHERS THEN NULL qui masque les erreurs critiques. Utiliser %TYPE et %ROWTYPE.

Exemple : Performance (FORALL)

Code 10.0
optimisation_bulk.sql
DECLARE -- Collection de matricules à mettre à jour TYPE t_mats IS TABLE OF etudiants.matricule%TYPE; l_mats t_mats := t_mats('ETU001', 'ETU002', 'ETU003'); BEGIN -- MAUVAISE PRATIQUE : Boucle FOR classique (3 switchs) -- FOR i IN 1..l_mats.COUNT LOOP -- UPDATE etudiants SET actif='O' WHERE matricule=l_mats(i); -- END LOOP; -- BONNE PRATIQUE : FORALL (1 seul switch contexte) FORALL i IN INDICES OF l_mats UPDATE etudiants SET actif = 'O', date_maj = SYSDATE WHERE matricule = l_mats(i); DBMS_OUTPUT.PUT_LINE('MAJ rapide : ' || SQL%ROWCOUNT || ' lignes'); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Erreur critique : ' || SQLERRM); END; /

Conclusion & Ressources

Synthèse du cours

22

Concepts Maîtrisés

Blocs PL/SQL

Structure & Variables

Contrôle

IF, CASE, Boucles

Curseurs

Traitement ligne/ligne

Exceptions

Gestion d'erreurs

Procédures & Fonctions

Sous-programmes

Triggers

Automatisation

Collections

Tableaux & Varrays

TP Validé

10 Exercices résolus

Pour aller plus loin

Oracle Database PL/SQL Language Reference

Documentation Officielle

Oracle Live SQL

livesql.oracle.com - Environnement de test

Test Final des Fonctions du TP

final_check.sql
BEGIN -- Vérification des composants développés DBMS_OUTPUT.PUT_LINE('--- TEST FINAL ---'); -- Test Ex. 5 DBMS_OUTPUT.PUT_LINE( 'Titre M101: ' || get_module_titre('M101') ); -- Test Ex. 6 DBMS_OUTPUT.PUT_LINE( 'Moyenne ETU001: ' || NVL(TO_CHAR(calculer_moyenne('ETU001')), 'NULL') ); -- Test Ex. 7 DBMS_OUTPUT.PUT_LINE( 'Coeff 3 => ' || classifier_coefficient(3) ); END; /