Exercices Pratiques Curseurs PL/pgSQL

Méthodologie : De l'énoncé au code

Recette pas à pas pour réussir tous tes TP

📋 Les 5 étapes de la réussite

1️⃣

Analyser l'énoncé

Identifier les besoins

2️⃣

Patrons du cours

Quels concepts utiliser

3️⃣

Pseudo-code

Logique en français

4️⃣

Code PL/pgSQL

Traduction technique

5️⃣

Tests

Vérification

📝 Exercice : Procédure AffiClub

1️⃣ Énoncé

Créer une procédure AffiClub(i_param TEXT) qui :

• Entrée : Soit un n° de club → afficher ce club uniquement
Soit un nom de colonne (ex. 'clu_no') → lister tous les clubs triés par cette colonne
• Pour chaque club :
→ Afficher l'entête "Liste des membres du club n°X Nom :"
→ Compter les membres
→ Si aucun membre : afficher "Aucun membre !"
→ Sinon : afficher les 3 premiers (tri nom/prénom), puis si >4 "... et X autres", puis le dernier

2️⃣ Patrons PL/pgSQL à utiliser

🔧 Techniques nécessaires

✓ Cast avec gestion d'erreur :
BEGIN ... ::INTEGER EXCEPTION ...
✓ Requête dynamique :
EXECUTE format('... ORDER BY %I', col)
✓ Curseur FOR LOOP :
FOR r IN EXECUTE sql LOOP
✓ Comptage :
SELECT COUNT(*) INTO variable
✓ Affichage :
RAISE NOTICE

📦 Structure du code

DECLARE

variables...

BEGIN

-- Déterminer mode

BEGIN...EXCEPTION...END

-- Parcourir clubs

FOR club IN EXECUTE sql

-- Compter membres

SELECT COUNT(*) INTO...

-- Parcourir membres

FOR membre IN SELECT...

affichage conditionnel

END LOOP

END

3️⃣ Pseudo-code (logique en français)

SI i_param est convertible en INTEGER ALORS

v_sql := SELECT clu_no, clu_nom FROM heg_club WHERE clu_no = <num>

SINON

v_sql := SELECT clu_no, clu_nom FROM heg_club ORDER BY <colonne fournie>

FIN SI

POUR CHAQUE club DANS (EXECUTE v_sql) FAIRE

afficher entête

v_nb := count(membres du club)

SI v_nb = 0 ALORS

"Aucun membre !"

SINON

compteur := 0

POUR CHAQUE membre trié nom/prénom FAIRE

compteur++

SI compteur ≤ 3 ALORS

afficher membre

FIN SI

mémoriser dernier (nom/prénom)

FIN POUR

SI v_nb > 4 ALORS

afficher "... et v_nb-4 autres"

FIN SI

SI v_nb > 1 ALORS

afficher dernier

FIN SI

SI mode "un seul club" ALORS

EXIT

FIN SI

FIN POUR

4️⃣ Code PL/pgSQL corrigé et prêt à exécuter

CREATE OR REPLACE PROCEDURE AffiClub(i_param TEXT) AS $$
DECLARE
    v_clu_no      INTEGER;
    v_sql         TEXT;
    r_club        RECORD;
    r_membre      RECORD;
    v_nb_membres  INTEGER;
    v_compteur    INTEGER;
    v_last_nom    heg_personne.per_nom%TYPE;
    v_last_prenom heg_personne.per_prenom%TYPE;
BEGIN
    -- 1) Déterminer : numéro de club OU nom de colonne pour tri
    BEGIN
        v_clu_no := i_param::INTEGER;  -- conversion → un seul club
        v_sql := 'SELECT clu_no, clu_nom FROM heg_club WHERE clu_no = ' || v_clu_no;
    EXCEPTION
        WHEN others THEN
            -- tri dynamique sécurisé sur nom de colonne
            v_sql := format('SELECT clu_no, clu_nom FROM heg_club ORDER BY %I', i_param);
    END;
    -- 2) Parcourir les clubs ciblés
    FOR r_club IN EXECUTE v_sql LOOP
        RAISE NOTICE 'Liste des membres du club n°% % :', r_club.clu_no, r_club.clu_nom;
        SELECT COUNT(*) INTO v_nb_membres
        FROM heg_personne
        WHERE per_clu_no = r_club.clu_no;
        IF v_nb_membres = 0 THEN
            RAISE NOTICE '- Aucun membre !';
        ELSE
            v_compteur := 0;
            FOR r_membre IN
                SELECT per_nom, per_prenom
                FROM heg_personne
                WHERE per_clu_no = r_club.clu_no
                ORDER BY per_nom, per_prenom
            LOOP
                v_compteur := v_compteur + 1;
                IF v_compteur <= 3 THEN
                    RAISE NOTICE '- % %', INITCAP(r_membre.per_prenom), INITCAP(r_membre.per_nom);
                END IF;
                -- mémorise en continu le dernier vu
                v_last_nom := r_membre.per_nom;
                v_last_prenom := r_membre.per_prenom;
            END LOOP;
            IF v_nb_membres > 4 THEN
                RAISE NOTICE '- ... et % autres', v_nb_membres - 4;
            END IF;
            IF v_nb_membres > 1 THEN
                RAISE NOTICE '- % %', INITCAP(v_last_prenom), INITCAP(v_last_nom);
            END IF;
        END IF;
        -- si c'est un club unique, on sort après le premier
        IF v_sql LIKE '%WHERE%' THEN
            EXIT;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

5️⃣ Checklist avant Exécuter

Table bien nommée heg_club (pas heag_club)
RAISE NOTICE 'n°% % :', var1, var2 (autant de % que de variables)
Un ; à chaque instruction
END LOOP; / END; correctement appariés
$$ LANGUAGE plpgsql; avec point-virgule
Majuscules SQL : SELECT, WHERE, FROM

🧪 Tests rapides

Tous les clubs triés par numéro :

CALL AffiClub('clu_no');

Tous les clubs triés par nom :

CALL AffiClub('clu_nom');

Club n°3 uniquement :

CALL AffiClub('3');

Club sans membres :

CALL AffiClub('5');

🎯 Points Clés à Retenir

🔐 Sécurité SQL

✓ Utiliser format(..., %I) pour les identifiants (colonnes, tables)
✓ Jamais de concaténation directe avec des noms de colonnes
✓ %I = identifiant, %L = littéral

🎭 Gestion des Erreurs

✓ BEGIN...EXCEPTION...END pour tester un cast
✓ WHEN others THEN attrape toutes les erreurs
✓ Toujours avoir un plan B dans EXCEPTION

🔄 Curseurs FOR LOOP

✓ FOR r IN EXECUTE sql LOOP pour SQL dynamique
✓ FOR r IN SELECT ... LOOP pour SQL statique
✓ Accès aux colonnes via r.nom_colonne

📝 RAISE NOTICE

✓ Un % par variable après le message
✓ RAISE NOTICE 'X: %', var;
✓ Utiliser INITCAP() pour formater les noms

🚀 Bonus : Version avec Surcharge

Pour être encore plus "à la lettre du prof", on peut créer deux versions distinctes avec surcharge :

Version 1 : Par numéro

AffiClub(i_clu_no INTEGER)

Affiche un club spécifique

Version 2 : Par colonne de tri

AffiClub(i_tri_colonne VARCHAR)

Liste tous les clubs triés

-- Version 1 : Affichage d'un club par numéro
CREATE OR REPLACE PROCEDURE AffiClub(i_clu_no INTEGER) AS $$
DECLARE
    r_club        RECORD;
    r_membre      RECORD;
    v_nb_membres  INTEGER;
    v_compteur    INTEGER;
    v_last_nom    heg_personne.per_nom%TYPE;
    v_last_prenom heg_personne.per_prenom%TYPE;
BEGIN
    SELECT clu_no, clu_nom INTO r_club
    FROM heg_club
    WHERE clu_no = i_clu_no;
    RAISE NOTICE 'Liste des membres du club n°% % :', r_club.clu_no, r_club.clu_nom;
    SELECT COUNT(*) INTO v_nb_membres
    FROM heg_personne
    WHERE per_clu_no = i_clu_no;
    IF v_nb_membres = 0 THEN
        RAISE NOTICE '- Aucun membre !';
    ELSE
        v_compteur := 0;
        FOR r_membre IN
            SELECT per_nom, per_prenom
            FROM heg_personne
            WHERE per_clu_no = i_clu_no
            ORDER BY per_nom, per_prenom
        LOOP
            v_compteur := v_compteur + 1;
            IF v_compteur <= 3 THEN
                RAISE NOTICE '- % %', INITCAP(r_membre.per_prenom), INITCAP(r_membre.per_nom);
            END IF;
            v_last_nom := r_membre.per_nom;
            v_last_prenom := r_membre.per_prenom;
        END LOOP;
        IF v_nb_membres > 4 THEN
            RAISE NOTICE '- ... et % autres', v_nb_membres - 4;
        END IF;
        IF v_nb_membres > 1 THEN
            RAISE NOTICE '- % %', INITCAP(v_last_prenom), INITCAP(v_last_nom);
        END IF;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'Club n°% introuvable', i_clu_no;
END;
$$ LANGUAGE plpgsql;
-- Version 2 : Liste tous les clubs avec tri
CREATE OR REPLACE PROCEDURE AffiClub(i_tri_colonne VARCHAR) AS $$
DECLARE
    v_sql TEXT;
    r_club RECORD;
BEGIN
    v_sql := format('SELECT clu_no FROM heg_club ORDER BY %I', i_tri_colonne);
    FOR r_club IN EXECUTE v_sql LOOP
        -- Appel de la version 1 pour chaque club
        CALL AffiClub(r_club.clu_no);
    END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Tests
CALL AffiClub(3);          -- Version INTEGER
CALL AffiClub('clu_nom');  -- Version VARCHAR