Correction TP6- PL/SQL

Séquence et Trigger de préparation

Avant d'aborder les exercices, le document de travail indique la création d'une séquence et d'un trigger d'auto-incrémentation. Voici le code corrigé correspondant, qui servira de base pour la suite :

CREATE SEQUENCE new_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
/

CREATE OR REPLACE TRIGGER auto_increment
BEFORE INSERT ON Joueur
FOR EACH ROW
BEGIN
    IF (:new.NuJoueur IS NULL) THEN
        SELECT new_seq.nextval INTO :new.NuJoueur FROM dual;
    END IF;
END;
/

Exercice 1 - Les Triggers

Question 1 - Analyse du trigger BaseTENNIS-Trigger.sql

Ce premier trigger (nommé auto_increment ci-dessus) sert à garantir l'intégrité de la clé primaire NuJoueur lors de l'ajout d'un nouveau joueur. Plus précisément :

1. Il vérifie si la valeur du champ NuJoueur que l'on essaie d'insérer dans la table Joueur est absente (NULL).
2. Si cette valeur est effectivement NULL, il interroge la séquence new_seq pour récupérer la valeur suivante (nextval) et l'assigne à l'attribut :new.NuJoueur.
3. Il se déclenche systématiquement avant l'insertion (clause BEFORE INSERT) d'une nouvelle ligne (FOR EACH ROW), ce qui est obligatoire puisque l'on souhaite modifier la valeur de la ligne avant qu'elle ne soit écrite dans la base de données.

Question 2 - Création du trigger trig1 pour le formatage du nom

Ce trigger a pour but de transformer automatiquement le nom du joueur en majuscules avant son insertion.

DROP TRIGGER trig1;

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT ON Joueur
FOR EACH ROW
BEGIN
    :new.Nom := UPPER(:new.Nom);
END;
/

Note : Il est indispensable d'utiliser un trigger BEFORE INSERT pour pouvoir modifier la valeur de :new.Nom.

Question 3 - Test du trigger trig1

Le test consiste à formater l'affichage dans SQL*Plus puis à insérer un joueur avec un nom en minuscules pour vérifier que le trigger s'active bien.

-- Ajustement de la largeur des colonnes pour un affichage propre
COL 'Nom' FOR a14;
COL 'Prenom' FOR a14;
COL 'Nationalite' FOR a10;

INSERT INTO joueur (Nom, Prenom, Annais, Nationalite)
VALUES ('gasket', 'Richard', 1986, 'France');

Si vous effectuez un SELECT * FROM joueur; après cette insertion, vous constaterez que le nom enregistré est "GASKET".

Question 4 - Modification du trigger trig1 pour gérer les mises à jour

Actuellement, si un utilisateur fait un UPDATE sur un nom en minuscules, le trigger ne se déclenche pas. Il faut ajouter l'événement OR UPDATE pour couvrir ce cas.

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT OR UPDATE ON joueur
FOR EACH ROW
BEGIN
    :new.Nom := UPPER(:new.Nom);
END;
/

Question 5 - Test de la modification du trigger trig1

UPDATE joueur SET nom = 'Gasquet' WHERE Nom = 'GASKET';

Grâce à la modification précédente, la valeur "Gasquet" sera automatiquement reconvertie en "GASQUET" dans la base lors de la mise à jour.

Question 6 - Création du trigger trig2 sur la table Gain

L'objectif est de convertir une prime en euros si l'année du tournoi est strictement antérieure à 2001 (taux de conversion de 0.152).

Attention à l'énoncé source : L'énoncé textuel indique que le trigger se déclenche "après l'insertion". C'est une erreur conceptuelle ! En PL/SQL, on ne peut pas modifier la pseudo-valeur :new dans un trigger AFTER. Le code correct fourni dans la correction utilise bien un trigger BEFORE INSERT, ce qui est la seule méthode techniquement valide ici.

DROP TRIGGER trig2;

CREATE OR REPLACE TRIGGER trig2
BEFORE INSERT ON Gain
FOR EACH ROW
BEGIN
    IF (:new.annee < 2001) THEN
        :new.prime := :new.prime * 0.152;
    END IF;
END;
/

Test et nettoyage de ce trigger :

-- Insertion avant 2001 (la prime sera convertie : 100 * 0.152 = 15.2)
INSERT INTO gain VALUES (2, 'Roland Garros', 2000, 100, 'Nike');

-- Insertion en 2001 ou après (la prime reste à 100)
INSERT INTO gain VALUES (1, 'Roland Garros', 2001, 100, 'Nike');

-- Nettoyage des lignes de test
DELETE FROM gain WHERE prime = 100;
DELETE FROM gain WHERE prime = 100 * 0.152;

Exercice 2 - PL/SQL : Requêtes à résultat unique

Question 1 - Script moyennePrime.sql

Ce script demande des informations à l'utilisateur via SQL*Plus, calcule la moyenne des primes pour un tournoi et une année donnés, et utilise une exception personnalisée (FIN) pour gérer le cas où la requête renverrait une moyenne nulle (ce qui arrive quand la fonction d'agrégation AVG ne trouve aucune ligne).

ACCEPT V_lieutournoi PROMPT "Quel lieu de tournoi: "
ACCEPT V_annee NUMBER PROMPT "Quelle annee: "

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
    V_moyenne GAIN.prime%TYPE;
    FIN EXCEPTION;
BEGIN
    -- Requête de calcul de la moyenne des primes
    SELECT AVG(GAIN.prime) INTO V_moyenne 
    FROM gain
    WHERE (GAIN.lieutournoi = '&V_lieutournoi' AND GAIN.annee = &V_annee);

    -- Levée de l'exception si le résultat est nul
    IF V_moyenne IS NULL THEN 
        RAISE FIN;
    END IF;

    DBMS_OUTPUT.PUT_LINE('&V_lieutournoi' || ' ' || TO_CHAR(&V_annee) || ': ' || TO_CHAR(V_moyenne));

EXCEPTION
    WHEN FIN THEN 
        DBMS_OUTPUT.PUT_LINE('Tournoi non repertorie');
END;
/

Question 2 - Test du script moyennePrime.sql

START moyennePrime.sql

Question 3 - Script moyennePrime2.sql

Ce script réalise exactement la même opération, mais remplace l'utilisation de l'exception personnalisée par une structure conditionnelle classique IF ... ELSE. Cette approche est souvent plus lisible pour des règles de gestion simples.

ACCEPT V_lieutournoi PROMPT "Quel lieu de tournoi: "
ACCEPT V_annee NUMBER PROMPT "Quelle annee: "

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
    V_moyenne GAIN.prime%TYPE;
BEGIN
    SELECT AVG(GAIN.prime) INTO V_moyenne 
    FROM gain
    WHERE (GAIN.lieutournoi = '&V_lieutournoi' AND GAIN.annee = &V_annee);

    IF V_moyenne IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('Tournoi non repertorie');
    ELSE
        DBMS_OUTPUT.PUT_LINE('&V_lieutournoi' || ' ' || TO_CHAR(&V_annee) || ': ' || TO_CHAR(V_moyenne));
    END IF;
END;
/

Question 4 - Test du script moyennePrime2.sql

START moyennePrime2.sql

Exercice 3 - PL/SQL : Requêtes à résultat multiple, utilisation des curseurs

Question 1 - Script primeJoueur.sql

Ce script extrait le nom de chaque joueur et sa prime maximale remportée sur une période donnée (entre deux années fournies par l'utilisateur). Une requête ramenant plusieurs lignes, l'utilisation d'un curseur explicite est obligatoire. On utilise l'attribut %ROWCOUNT pour gérer l'affichage dynamique.

SET SERVEROUTPUT ON
SET VERIFY OFF

ACCEPT V_Annee1 NUMBER PROMPT 'Annee de depart: '
ACCEPT V_Annee2 NUMBER PROMPT 'Derniere annee: '

DECLARE
    V_N VARCHAR(20);
    V_M Gain.Prime%TYPE;
    
    CURSOR C_M IS
        SELECT nom, MAX(prime)
        FROM GAIN, JOUEUR
        WHERE annee BETWEEN &V_Annee1 AND &V_Annee2
        AND gain.NuJoueur = joueur.NuJoueur
        GROUP BY nom;
BEGIN
    OPEN C_M;
    LOOP
        FETCH C_M INTO V_N, V_M;
        EXIT WHEN C_M%NOTFOUND;
        
        -- Affiche l'entête uniquement si c'est la première ligne récupérée
        IF C_M%ROWCOUNT = 1 THEN 
            DBMS_OUTPUT.PUT_LINE('Le resultat est :');
        END IF;
        
        DBMS_OUTPUT.PUT_LINE(V_N || ' ' || TO_CHAR(V_M));
    END LOOP;

    -- Si après la boucle le compteur est toujours à 0, le curseur était vide
    IF C_M%ROWCOUNT = 0 THEN 
        DBMS_OUTPUT.PUT_LINE('Aucun tournoi n''est répertorié entre ces dates');
    END IF;
    
    CLOSE C_M;
END;
/

Question 2 - Test du script primeJoueur.sql

START primeJoueur.sql

Question 3 - Script joueurSponsor.sql

De la même façon que l'exercice précédent, ce script demande le nom d'un sponsor et liste tous les joueurs (pouvant être multiples) associés à ce sponsor. On gère le cas du sponsor introuvable via l'attribut %ROWCOUNT.

SET SERVEROUTPUT ON
SET VERIFY OFF

ACCEPT V_sponsor PROMPT "Donnez le nom du sponsor: "

DECLARE
    V_nom Joueur.Nom%TYPE;
    
    CURSOR C_sponsor IS
        SELECT joueur.nom 
        FROM joueur, gain
        WHERE gain.nujoueur = joueur.nujoueur 
        AND gain.nomsponsor = '&V_sponsor';
BEGIN
    OPEN C_sponsor;
    LOOP
        FETCH C_sponsor INTO V_nom;
        EXIT WHEN C_sponsor%NOTFOUND;
        
        IF C_sponsor%ROWCOUNT = 1 THEN
            DBMS_OUTPUT.PUT_LINE('Les joueurs sponsorises par &V_sponsor :');
        END IF;
        
        DBMS_OUTPUT.PUT_LINE(V_nom);
    END LOOP;

    IF C_sponsor%ROWCOUNT = 0 THEN 
        DBMS_OUTPUT.PUT_LINE('&V_sponsor est un Sponsor inconnu');
    END IF;
    
    CLOSE C_sponsor;
END;
/

Méthode

Pour aborder sereinement les examens et TP portant sur PL/SQL sous Oracle, gardez en tête les points suivants :

1. Choix du type de Trigger : Dès qu'un trigger a pour but de vérifier, modifier ou formater la donnée en cours de traitement (manipulation des champs :new), il DOIT être déclaré en BEFORE. La modification de l'état :new dans un trigger AFTER produira une erreur de compilation.
2. Requêtes multi-lignes vs mono-ligne :
   • Dès qu'une requête SQL ramène logiquement une et une seule ligne, ou un résultat agrégé (comme un AVG, un SUM), utilisez un bloc anonyme classique avec un SELECT ... INTO .... Attention : si le résultat ne trouve rien, AVG renverra NULL sans erreur, mais un SELECT * renverra l'exception NO_DATA_FOUND.
   • Dès qu'une requête peut potentiellement ramener plusieurs lignes (ou zéro), vous devez impérativement déclarer un CURSOR, l'ouvrir (OPEN), boucler dessus (FETCH + EXIT WHEN %NOTFOUND), puis le fermer (CLOSE).
3. Environnement SQL*Plus : Pensez toujours à inclure la commande SET SERVEROUTPUT ON au début de vos scripts si vous utilisez DBMS_OUTPUT.PUT_LINE, sinon vos messages s'exécuteront correctement mais seront invisibles à l'écran. SET VERIFY OFF sert quant à lui à cacher la substitution technique des variables (les lignes "old / new") pour ne pas polluer l'affichage.