Informatique · PL/pgSQL (PostgreSQL)

Exercices sur les curseurs

Un énoncé de TP, une procédure à écrire, des messages exacts au caractère près. On suit la méthode sur AffiClub, un exercice complet.

Déjà à l'aise ? Aller au quiz

Le problème

Une procédure doit lister des clubs et leurs membres, sans rien oublier. Avant d'écrire une ligne, il faut savoir ce qu'elle doit afficher.

énoncé patrons pseudo-code code tests Dans cet ordre, sans sauter d'étape.

La sortie à obtenir

Pour CALL AffiClub('3'), la sortie est celle-ci : l'en-tête du club, trois noms, « ... et 2 autres », puis le dernier.

=> CALL AffiClub('3') NOTICE: Liste des membres du club n°3 Théâtre : NOTICE: - Léa Bernasconi NOTICE: - Noah Dubois NOTICE: - Zoé Favre NOTICE: - ... et 2 autres NOTICE: - Maé Vuille

Après cette fiche, tu sais

  • passer d'un énoncé au code en cinq étapes, sans te jeter sur le clavier ;
  • choisir le bon patron : cast dans un bloc EXCEPTION, format avec %I, FOR … IN EXECUTE, COUNT(*) INTO, RAISE NOTICE ;
  • écrire, tracer et tester la procédure AffiClub, cas limites compris ;
  • lire l'état d'un curseur en PL/pgSQL (FOUND) et en Oracle (%FOUND, %ROWCOUNT…), et reconnaître les autres formes Oracle : curseur implicite, boucle FOR de curseur, paramètres, FOR UPDATE.
Un club compte exactement quatre membres. La procédure doit-elle écrire « ... et 0 autres » ?

Non, ça n'a pas de sens. Mais je ne suis pas sûr.

Garde ta réponse en tête : la scène 3 la vérifie.

Une méthode avant le clavier

Étape 0 · La méthode

De l'énoncé au code, en cinq étapes

La plupart des copies ratées le sont avant la première ligne de code : une sortie mal lue, un cas oublié. La méthode force à régler ces questions dans l'ordre.

1. Analyser l'énoncé

Les entrées, les sorties exactes au caractère près, et les cas limites : club sans membre, numéro inconnu.

2. Choisir les patrons

Pour chaque besoin, la construction PL/pgSQL qui y répond.

3. Écrire le pseudo-code

La logique en français, sans se soucier de la syntaxe.

4. Traduire en PL/pgSQL

Chaque ligne du pseudo-code devient une ou deux instructions.

5. Tester

Chaque cas de l'énoncé, en comparant la sortie obtenue à la sortie attendue.

Une ligne, deux instructions

La ligne « compter les membres » du pseudo-code devient deux instructions : le SELECT qui range le nombre, puis le IF qui le teste.

compter les membres SELECT COUNT(*) INTOv_nb_membres ; IF v_nb_membres = 0THEN ... END IF ; Deux instructions pour une seule ligne.

Le même curseur, deux syntaxes

Rappel · Le curseur

Le même curseur, en PL/pgSQL et en Oracle

Un curseur encapsule une requête et en livre le résultat ligne après ligne : on le déclare, on l'ouvre (il se place avant la première ligne), on lit avec FETCH jusqu'à la fin, on le ferme.1

Le cycle de vie

déclarer OPEN FETCH CLOSE la requête avant la 1re ligne une ligne lue le curseur se ferme on relit tant qu'il reste une ligne

La logique est la même en Oracle ; seules la syntaxe et la façon de lire l'état changent.6 Le cours complet est dans la fiche « Déclencheurs et curseurs ».

Scène 1

Deux dialectes, une boucle

Fais défiler : le curseur parcourt les quatre membres du club 1. Touche Oracle pour voir la même boucle écrite en PL/SQL, avec les attributs du curseur.

Scène 1 · Dialectes

PL/pgSQL (PostgreSQL)

résultat de c AEBY CHAPPUIS GIROUD PERRET état du curseur

PL/pgSQL (PostgreSQL)PL/SQL (Oracle)
Déclarerc CURSOR FOR SELECT …;CURSOR c IS SELECT …;
Fin du résultatEXIT WHEN NOT FOUND;EXIT WHEN c%NOTFOUND;
Lignes déjà luesun compteur à tenir soi-mêmec%ROWCOUNT
AfficherRAISE NOTICE '%', v;DBMS_OUTPUT.PUT_LINE(v);
GET DIAGNOSTICS ne remplace pas %ROWCOUNTGET DIAGNOSTICS n = ROW_COUNT; donne le nombre de lignes traitées par la dernière commande seulement.2 Testé : il vaut 1 après chaque FETCH réussi, jamais 2 ou 3. Pour savoir combien de lignes on a lues, on compte dans la boucle.
Remarque la différence d'affichage : en Oracle, PUT_LINE remplit un tampon, qui n'est lu qu'à la fin du bloc. Ne t'étonne pas de tout voir arriver d'un coup.

Oracle · Les autres formes

Quatre formes à reconnaître

Le curseur explicite n'est pas le seul. Oracle a aussi les formes suivantes, à lire avant d'écrire.

Exemple · Curseur implicite

Un DELETE qui touche deux lignes : SQL%ROWCOUNT vaut 2, SQL%FOUND vaut TRUE, et SQL%ISOPEN vaut FALSE.7

DELETE … WHERE per_clu_no = 2 ; SQL%ROWCOUNT2 SQL%FOUNDTRUE SQL%ISOPENFALSE Le curseur implicite est fermé tout seul.

Curseur implicite

Chaque INSERT, UPDATE ou DELETE en a un : SQL%ROWCOUNT donne le nombre de lignes touchées, SQL%FOUND dit s'il y en a. SQL%ISOPEN vaut toujours FALSE : Oracle ferme ce curseur tout seul.7

Boucle FOR de curseur

FOR r IN c LOOP ouvre le curseur, lit chaque ligne dans r et le referme à la fin : pas de OPEN, FETCH ni CLOSE à écrire.8

Curseur paramétré

CURSOR c (p_club NUMBER) IS SELECT … WHERE per_clu_no = p_club;, puis OPEN c(2); ou FOR r IN c(2) LOOP.6

FOR UPDATE

SELECT … FOR UPDATE verrouille les lignes du résultat ; WHERE CURRENT OF c modifie la ligne courante du curseur.9

Avant le code, l'énoncé

Étape 1 · L'énoncé

La procédure AffiClub

Créer une procédure AffiClub(i_param TEXT). Le paramètre peut servir de deux façons.

i_param '3' : un clubcelui qui a ce numéro 'clu_nom' : une colonnetous les clubs, triés

Les règles

  • Entrée : soit un numéro de club, et on n'affiche que ce club ; soit un nom de colonne (par exemple 'clu_no'), et on liste tous les clubs triés par cette colonne.
  • Pour chaque club : afficher l'en-tête « Liste des membres du club n°X Nom : », compter les membres ; s'il n'y en a aucun, afficher « Aucun membre ! ».

Sinon

  • afficher les trois premiers (tri par nom puis prénom) ; s'il y a plus de quatre membres, « ... et X autres » ; puis le dernier, s'il ne fait pas déjà partie des trois premiers.
Entréenuméro ou nomde colonne Chaque cluben-tête, puiscomptage Membrestrois premiers,« et X autres »,le dernier

Les tables, avec des données d'essai qui couvrent tous les cas : un club de six membres, un de quatre, un de cinq, un de deux et un club vide.

Tables et données d'essaiSQL
CREATE TABLE heg_club (
    clu_no  INTEGER PRIMARY KEY,
    clu_nom VARCHAR(30) NOT NULL
);
CREATE TABLE heg_personne (
    per_no     INTEGER PRIMARY KEY,
    per_nom    VARCHAR(30) NOT NULL,
    per_prenom VARCHAR(30) NOT NULL,
    per_clu_no INTEGER REFERENCES heg_club
);
INSERT INTO heg_club VALUES (1, 'Badminton'), (2, 'Chorale'),
    (3, 'Théâtre'), (4, 'Football'), (5, 'Robotique');
INSERT INTO heg_personne VALUES
    (1, 'ROCHAT', 'elias', 3), (2, 'BERNASCONI', 'léa', 3),
    (3, 'VUILLE', 'maé', 3), (4, 'DUBOIS', 'noah', 3),
    (5, 'FAVRE', 'zoé', 3), (6, 'MULLER', 'lina', 3),
    (7, 'GIROUD', 'sami', 1), (8, 'AEBY', 'nora', 1),
    (9, 'PERRET', 'luca', 1), (10, 'CHAPPUIS', 'emma', 1),
    (11, 'BONVIN', 'adam', 2), (12, 'MARTIN', 'chloé', 2),
    (13, 'ZAUGG', 'tim', 4), (14, 'ROTH', 'mia', 4),
    (15, 'JACOT', 'leo', 4), (16, 'HUGUENIN', 'ella', 4),
    (17, 'CUENDET', 'noé', 4);

Avant d'écrire une ligne, l'étape 1 fixe la sortie attendue. Pour CALL AffiClub('3'), c'est exactement la console de l'accroche ; pour le club 5, l'en-tête puis « - Aucun membre ! ».

Chaque besoin, son patron

Étapes 2 et 3 · Patrons et pseudo-code

Un patron par besoin

Cinq besoins, cinq constructions. Le premier décide si i_param est un nombre ou un nom de colonne.

Le premier patron

i_param tenter ::INTEGER '3' : réussitv_clu_no = 3 'clu_nom' : échoueinvalid_text_representation EXCEPTION : format(… %I)

Cinq besoins, cinq patrons

Nombre ou nom de colonne ?

Tenter la conversion i_param::INTEGER dans un bloc BEGIN … EXCEPTION : un texte comme 'clu_nom' lève invalid_text_representation (code 22P02).4

Trier par une colonne choisie

Construire la requête avec format('… ORDER BY %I', i_param) : %I traite la valeur comme un identifiant et l'entoure de guillemets si besoin.4

Parcourir une requête construite

FOR r IN EXECUTE v_sql LOOP : la chaîne est évaluée et planifiée à chaque entrée dans la boucle.3

Compter

SELECT COUNT(*) INTO v_nb FROM … WHERE …; range le résultat dans une variable.

Afficher

RAISE NOTICE 'texte % %', v1, v2; : un % par valeur, et INITCAP pour mettre les noms en forme.4

Le trieur sûr

Pour trier par une colonne reçue en paramètre, le bon réflexe est format avec %I. La chaîne reçue reste un nom de colonne, jamais du SQL.

format('… ORDER BY %I', 'clu_nom')i_param vaut 'clu_nom' … ORDER BY clu_nom%I : identifiant valide, sans guillemets Un nom qui n'a pas besoin de guillemets reste nu.
Exercice · choisir

Quel patron pour quel besoin ?

Cinq besoins de l'énoncé, cinq constructions. Associe chaque besoin à la bonne.

Étape 3 · Le pseudo-code

Avant le PL/pgSQL, la logique en français : elle se lit sans syntaxe, et chaque ligne se traduira ensuite.

Le pseudo-code

La logique en françaispseudo-code
SI i_param se convertit en entier ALORS
    v_sql := les clubs dont le numéro vaut i_param
SINON
    v_sql := tous les clubs, triés par la colonne i_param
FIN SI
POUR CHAQUE club DE v_sql
    afficher l'en-tête
    v_nb := nombre de membres du club
    SI v_nb = 0 ALORS afficher « Aucun membre ! »
    SINON
        POUR CHAQUE membre, trié par nom puis prénom
            compteur := compteur + 1
            SI compteur ≤ 3 ALORS afficher le membre
            retenir le membre comme dernier vu
        FIN POUR
        SI v_nb > 4 ALORS afficher « ... et v_nb − 4 autres »
        SI v_nb > 3 ALORS afficher le dernier
    FIN SI
FIN POUR
SI aucun club ALORS le signaler
Exercice · remettre dans l'ordre

Reconstruis la règle d'affichage

Les cinq lignes qui décident quels membres s'affichent ont été mélangées. Touche une ligne, puis celle avec laquelle l'échanger.

Le code, pas à pas

Étape 4 · Le code

AffiClub en PL/pgSQL

Chaque bloc du pseudo-code se retrouve, dans le même ordre. Tout a été exécuté dans PostgreSQL 18 avec les données d'essai.

1 · paramètreBEGIN … EXCEPTION 2 · le clubFOR r_club IN EXECUTE 3 · les membresCOUNT(*) INTO, puis FOR 4 · la finIF NOT FOUND Le pseudo-code se lit dans le même ordre que le code.
La procédure complètePL/pgSQL
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) un numéro de club, ou un nom de colonne ?
    BEGIN
        v_clu_no := i_param::INTEGER;
        -- v_clu_no est un entier : rien à échapper
        v_sql := format('SELECT clu_no, clu_nom FROM heg_club
                         WHERE clu_no = %s', v_clu_no);
    EXCEPTION
        WHEN invalid_text_representation THEN
            v_sql := format('SELECT clu_no, clu_nom FROM heg_club
                             ORDER BY %I', i_param);
    END;

    -- 2) parcourir le ou les clubs
    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;
                v_last_nom := r_membre.per_nom;       -- le dernier vu
                v_last_prenom := r_membre.per_prenom;
            END LOOP;
            IF v_nb_membres = 5 THEN
                RAISE NOTICE '- ... et 1 autre';
            ELSIF v_nb_membres > 5 THEN
                RAISE NOTICE '- ... et % autres', v_nb_membres - 4;
            END IF;
            IF v_nb_membres > 3 THEN
                RAISE NOTICE '- % %', INITCAP(v_last_prenom),
                    INITCAP(v_last_nom);
            END IF;
        END IF;
    END LOOP;

    -- 3) FOUND dit si la boucle a tourné au moins une fois
    IF NOT FOUND THEN
        RAISE NOTICE 'Aucun club ne correspond à %', i_param;
    END IF;
END;
$$ LANGUAGE plpgsql;

Pourquoi ce code

Quatre points demandent une explication. Chacun répond à une erreur fréquente.

WHEN invalid_text_representation

On attrape l'erreur précise du cast. WHEN OTHERS attraperait presque tout, y compris une vraie faute de frappe dans le bloc.3 Un nombre trop grand ('99999999999') lève une autre erreur, out of range for type integer, et s'arrête là : c'est voulu.

IF NOT FOUND après la boucle

À la sortie d'une boucle FOR, FOUND vaut true si elle a tourné au moins une fois.2 Sans ce test, CALL AffiClub('99') ne dit rien du tout (testé).

« et 1 autre »

Avec cinq membres, la formule de l'énoncé donnerait « et 1 autres » (testé) ; un IF de plus garde l'accord.

Pas d'EXIT pour le club unique

On voit souvent IF v_sql LIKE '%WHERE%' THEN EXIT; en fin de boucle. Inutile : clu_no est la clé primaire, la requête renvoie au plus une ligne.

Compter, puis choisir

COUNT(*) INTOv_nb_membres v_nb_membres = 0 ? oui : Aucun membre ! non : la boucletrois premiers, puis « ... »et le dernier

FOUND après la boucle

À la sortie de la boucle, FOUND dit si au moins un club a été lu. Pour CALL AffiClub('99'), aucune ligne : le message part.

FOR r IN c LOOP au moins un tourFOUND = truerien d'ajouté aucune ligneFOUND = falseIF NOT FOUND : message
Scène 2

Ce que fait CALL AffiClub('3')

Fais défiler : on suit les variables et la console, comme dans un débogueur.

CALL AffiClub('3'); variables i_param v_clu_no v_sql v_nb_membres dernier vu
Étape 1 sur 6 · le paramètre

'3'::INTEGER réussit : v_clu_no vaut 3, et v_sql cherche le club dont le numéro vaut 3.

Étape 2 sur 6 · le club

FOR r_club IN EXECUTE v_sql renvoie une seule ligne, (3, Théâtre) : l'en-tête s'affiche.

Étape 3 sur 6 · le comptage

SELECT COUNT(*) INTO donne 6 : on passe dans la branche ELSE.

Étape 4 sur 6 · les membres

La boucle triée affiche les trois premiers, puis continue sans afficher, en retenant le dernier vu : Maé Vuille.

Étape 5 sur 6 · après la boucle

6 > 5 : « ... et 2 autres ». 6 > 3 : le dernier, Maé Vuille. Fin, FOUND est vrai.

Étape 6 sur 6 · avec 'clu_nom'

Le cast échoue, le bloc EXCEPTION construit ORDER BY clu_nom : les cinq clubs défilent, Badminton d'abord.

Scène 3

La règle d'affichage

Fais défiler : le club gagne un membre à la fois, de 0 à 7. Touche « fautif » pour voir ce que donne la condition > 1, qu'on trouve souvent dans les copies.

Scène 3 · Règle

Un club de 0 membre

dernier : IF v_nb_membres > 3 version juste

Réponse à ma question : non. Avec quatre membres, personne n'est caché : les trois premiers, puis le dernier, et aucune ligne « et X autres ». Elle n'apparaît qu'à partir de cinq.
Exercice · prévoir

Qu'affiche la procédure ?

Quatre clubs des données d'essai. Prévois ce qui s'affiche sous l'en-tête, puis vérifie.

Chaque cas, sa sortie

Étape 5 · Tester

Chaque cas, et sa sortie

Un appel par cas de l'énoncé, plus deux cas limites. Toutes les sorties ci-dessous sont celles de PostgreSQL 18.

Quatre appels, quatre sorties

CALL AffiClub('4') Football : « ... et 1 autre » CALL AffiClub('5') Robotique : « Aucun membre ! » CALL AffiClub('99') « Aucun club ne correspond à 99 » CALL AffiClub('clu_nom') les cinq clubs, triés par nom

Cas du cast

Le cast ::INTEGER accepte ' 3 ', mais refuse 'abc' et un nombre trop grand.

' 3 '::INTEGER 3les espaces sont acceptés 'abc'::INTEGER 22P02invalid_text_representation '99999999999'::INTEGER 22003out of range for type integer
TestsPL/pgSQL
CALL AffiClub('4');         -- un seul club : Football
CALL AffiClub('5');         -- club vide
CALL AffiClub('99');        -- numéro inconnu
CALL AffiClub('clu_nom');   -- tous les clubs, triés par nom
NOTICE:  Liste des membres du club n°4 Football :
NOTICE:  - Noé Cuendet
NOTICE:  - Ella Huguenin
NOTICE:  - Leo Jacot
NOTICE:  - ... et 1 autre
NOTICE:  - Tim Zaugg
NOTICE:  Liste des membres du club n°5 Robotique :
NOTICE:  - Aucun membre !
NOTICE:  Aucun club ne correspond à 99
NOTICE:  Liste des membres du club n°1 Badminton :
NOTICE:  - Nora Aeby
…  puis Chorale, Football, Robotique et Théâtre

Avant d'exécuter

  • le bon nom de table : heg_club, pas heag_club ;
  • autant de % que de valeurs dans chaque RAISE NOTICE ;
  • un ; après chaque instruction, END LOOP; et END IF; bien appariés ;
  • $$ LANGUAGE plpgsql; à la fin, avec son point-virgule ;
  • les mots-clés SQL en majuscules : PostgreSQL ne l'exige pas, mais le code se relit mieux.

Piège · Le texte devient du SQL

Avec une concaténation, la valeur reçue est collée dans la requête. Avec %I, elle reste un nom.

concaténation : 'clu_nom DESC'DESC devient du SQL tri inversésans erreur, mais faux concaténation : '(SELECT 1/0)'la sous-requête s'exécute division by zeroerreur levée par le SQL %I : 'clu_nom DESC'un seul identifiant column "clu_nom DESC"does not exist
Deux écritures de ORDER BY. Laquelle est sûre ?

La seconde, avec %I ? Mais pourquoi exactement ?

Avec %I, la valeur ne devient jamais du SQL : elle reste un identifiant entre guillemets.
Piège : la concaténation d'un nom de colonne

Avec '… ORDER BY ' || i_param, la valeur reçue devient du SQL. Testé : 'clu_nom DESC' inverse le tri, et '(SELECT 1/0)' fait exécuter une sous-requête (erreur division by zero). Avec %I, les deux deviennent un nom de colonne entre guillemets, qui n'existe pas : column "clu_nom DESC" does not exist. La documentation recommande %I (ou quote_ident) pour les identifiants, et USING pour les valeurs.2

Piège : la casse d'un nom de colonne

%I garde la casse : 'Clu_Nom' devient "Clu_Nom", qui n'existe pas, car PostgreSQL range les noms non entourés de guillemets en minuscules. Erreur : column "Clu_Nom" does not exist.

Exercice · classer

Accepté ou refusé par PostgreSQL ?

Huit lignes ou situations, toutes testées dans PostgreSQL 18. Range chacune.

Un seul nom, deux versions

Bonus · La surcharge

Deux procédures, un seul nom

Au lieu de deviner si le paramètre est un nombre, on peut écrire deux procédures du même nom avec des types de paramètres différents : c'est la surcharge, que PostgreSQL autorise tant que les types d'entrée diffèrent.5 PostgreSQL choisit la version selon le type de l'argument.

CALL AffiClub(3) CALL AffiClub('clu_nom') version INTEGERAffiClub(i_clu_no INTEGER) version VARCHARAffiClub(i_tri_colonneVARCHAR) PostgreSQL choisit selon le type de l'argument.
Version par numéro, version par colonnePL/pgSQL
-- Version 1 : un club, par son 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
    -- STRICT : sans lui, aucune erreur si le club n'existe pas
    SELECT clu_no, clu_nom INTO STRICT 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 = 5 THEN
            RAISE NOTICE '- ... et 1 autre';
        ELSIF v_nb_membres > 5 THEN
            RAISE NOTICE '- ... et % autres', v_nb_membres - 4;
        END IF;
        IF v_nb_membres > 3 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 : tous les clubs, triés par une colonne
CREATE OR REPLACE PROCEDURE AffiClub(i_tri_colonne VARCHAR) AS $$
DECLARE
    r_club RECORD;
BEGIN
    FOR r_club IN EXECUTE format('SELECT clu_no FROM heg_club
                                  ORDER BY %I', i_tri_colonne) LOOP
        CALL AffiClub(r_club.clu_no);   -- appelle la version 1
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CALL AffiClub(3);           -- version INTEGER
CALL AffiClub(99);          -- Club n°99 introuvable
CALL AffiClub('clu_nom');   -- version VARCHAR

INTO STRICT exige exactement une ligne : sinon, PostgreSQL lève NO_DATA_FOUND (aucune ligne) ou TOO_MANY_ROWS (plusieurs).2 Sans STRICT, la variable resterait à NULL et l'en-tête s'afficherait vide.

Piège : l'ancienne version TEXT toujours là

Si AffiClub(TEXT) existe encore, CALL AffiClub('clu_nom') l'appelle elle, pas la version VARCHAR : face à une chaîne sans type, PostgreSQL préfère le type text.5 On la supprime en précisant les types : DROP PROCEDURE AffiClub(TEXT);. Sans les types, PostgreSQL répond procedure name "afficlub" is not unique (testé).

Version par colonne

La version VARCHAR ne calcule rien elle-même. Elle lit les clubs, puis appelle la version par numéro pour chacun.

CALL AffiClub('clu_nom') version 2 : FOR r_club IN EXECUTE format(… ORDER BY %I)le texte devient le nom de colonne pour chaque club : CALL AffiClub(r_club.clu_no) La version 1 fait le travail, club par club.

INTO STRICT

Avec INTO STRICT, la requête doit renvoyer exactement une ligne. Sinon, PostgreSQL arrête la procédure.

SELECT … INTO STRICT r_club 0 ligneNO_DATA_FOUND 1 ligner_club rempli plusieursTOO_MANY_ROWS Exactement une ligne, sinon erreur.

Quatre réflexes à garder

Habitudes de pro

Quatre familles de réflexes

Ces réflexes reviennent dans tous les exercices de curseurs de cette fiche.

Sécurité

%I pour un identifiant, %L pour une valeur littérale, et mieux encore EXECUTE … USING pour les valeurs : moins de conversions, et bien moins de risques d'injection SQL.2 Jamais de concaténation d'un paramètre.

Erreurs

Nommer la condition attendue, par exemple invalid_text_representation (la liste complète est dans l'annexe A de la documentation).4 OTHERS attrape toutes les erreurs sauf deux ; un bloc avec EXCEPTION coûte nettement plus cher qu'un bloc sans.3

Boucles

FOR r IN SELECT … LOOP pour une requête fixe, FOR r IN EXECUTE … LOOP pour une requête construite ; les colonnes se lisent r.nom_colonne.3

RAISE NOTICE

Un % par valeur, sinon too few ou too many parameters specified for RAISE (testé) ; INITCAP('BERNASCONI') donne Bernasconi.

Entraînement

Trois exercices corrigés

Sur les mêmes tables et les mêmes données d'essai. Tous les corrigés ont été exécutés dans PostgreSQL 18.

1Avec un curseur explicite (OPEN, FETCH, CLOSE), affiche chaque club et son nombre de membres, par ordre alphabétique.Voir le corrigé
DO $$
DECLARE
    c CURSOR FOR
        SELECT c.clu_nom, count(p.per_no) AS nb
        FROM heg_club c
        LEFT JOIN heg_personne p ON p.per_clu_no = c.clu_no
        GROUP BY c.clu_no, c.clu_nom
        ORDER BY c.clu_nom;
    r RECORD;
BEGIN
    OPEN c;
    LOOP
        FETCH c INTO r;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '% : % membre(s)', r.clu_nom, r.nb;
    END LOOP;
    CLOSE c;
END $$;
NOTICE:  Badminton : 4 membre(s)
NOTICE:  Chorale : 2 membre(s)
NOTICE:  Football : 5 membre(s)
NOTICE:  Robotique : 0 membre(s)
NOTICE:  Théâtre : 6 membre(s)

Le LEFT JOIN garde Robotique, qui n'a aucun membre ; count(p.per_no) compte alors 0, là où count(*) compterait 1.

2Déclare un curseur paramétré qui liste les membres d'un club donné, puis utilise-le pour le club 2 et pour le club 1.Voir le corrigé
DO $$
DECLARE
    c CURSOR (p_club INTEGER) FOR
        SELECT per_nom, per_prenom FROM heg_personne
        WHERE per_clu_no = p_club
        ORDER BY per_nom, per_prenom;
BEGIN
    FOR r IN c(2) LOOP
        RAISE NOTICE '% %', INITCAP(r.per_prenom), INITCAP(r.per_nom);
    END LOOP;
    FOR r IN c(1) LOOP
        RAISE NOTICE '% %', INITCAP(r.per_prenom), INITCAP(r.per_nom);
    END LOOP;
END $$;
NOTICE:  Adam Bonvin
NOTICE:  Chloé Martin
NOTICE:  Nora Aeby
NOTICE:  Emma Chappuis
NOTICE:  Sami Giroud
NOTICE:  Luca Perret

Les paramètres suivent le mot CURSOR ; on leur donne une valeur à l'ouverture, ici dans la boucle FOR, qui ouvre et ferme le curseur à chaque fois.1

3Ce morceau de copie ne se crée même pas, puis, corrigé, n'affiche pas ce qu'on attend. Trouve les trois fautes.Voir le corrigé
FOR r_club IN EXECUTE 'SELECT clu_no, clu_nom FROM heag_club
                       ORDER BY ' || i_param LOOP
    RAISE NOTICE 'Liste des membres du club n°% % :', r_club.clu_no;
    ...
    IF v_nb_membres > 1 THEN
        RAISE NOTICE '- % %', INITCAP(v_last_prenom), INITCAP(v_last_nom);
    END IF;
  • heag_club au lieu de heg_club, et une concaténation au lieu de format('… ORDER BY %I', i_param) ;
  • deux % pour une seule valeur : too few parameters specified for RAISE, il manque r_club.clu_nom ;
  • > 1 au lieu de > 3 : avec deux ou trois membres, le dernier s'affiche deux fois (scène 3).

À retenir

La fiche en sept lignes

Méthodeénoncé, patrons, pseudo-code, code, tests : dans cet ordre.
Nombre ou texte ?cast dans BEGIN … EXCEPTION WHEN invalid_text_representation.
Requête construiteformat avec %I pour un identifiant, puis FOR r IN EXECUTE.
FOUNDfalse au départ ; mis à jour par FETCH et à la sortie d'une boucle FOR.
Règle d'affichagetrois premiers, « et X autres » au-delà de quatre, le dernier au-delà de trois.
OracleCURSOR c IS, c%NOTFOUND, c%ROWCOUNT, DBMS_OUTPUT.PUT_LINE.
Surchargemême nom, types différents ; DROP PROCEDURE avec les types.

Douze questions pour finir

Dernière étape : le quiz. Douze questions tirées au hasard, différentes à chaque partie. Si tu te trompes, lis bien l'explication.
Quiz final

Teste-toi

Sources

  1. PostgreSQL 18 Documentation, PL/pgSQL 41.7 : Cursors (déclaration, curseurs paramétrés, OPEN, FETCH, CLOSE, boucle FOR). postgresql.org
  2. PostgreSQL 18 Documentation, PL/pgSQL 41.5 : Basic Statements (41.5.3 STRICT, NO_DATA_FOUND et TOO_MANY_ROWS ; 41.5.4 commandes dynamiques, format, quote_ident, USING ; 41.5.5 FOUND et ROW_COUNT). postgresql.org
  3. PostgreSQL 18 Documentation, PL/pgSQL 41.6 : Control Structures (41.6.6 FOR … IN EXECUTE ; 41.6.8 interception des erreurs, OTHERS, coût d'un bloc EXCEPTION). postgresql.org
  4. PostgreSQL 18 Documentation : 9.4 String Functions and Operators (format et ses spécificateurs %s, %I, %L ; initcap) et annexe A PostgreSQL Error Codes (22P02 invalid_text_representation). fonctions de chaînes, codes d'erreur (postgresql.org)
  5. PostgreSQL 18 Documentation : CREATE PROCEDURE (surcharge) et 10.3 Functions (résolution des appels : une chaîne sans type va vers la catégorie chaîne, dont text est le type préféré). CREATE PROCEDURE, résolution (postgresql.org)
  6. Oracle, Database PL/SQL Language Reference 19c : « Explicit Cursor Declaration and Definition », « OPEN Statement », « Named Cursor Attribute » ; PL/SQL Packages and Types Reference : « DBMS_OUTPUT » (tampon, SET SERVEROUTPUT ON). attributs de curseur, DBMS_OUTPUT (docs.oracle.com)
  7. Oracle, Database PL/SQL Language Reference 19c : curseur implicite SQL (attributs SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN, toujours FALSE).
  8. Oracle, Database PL/SQL Language Reference 19c : « Cursor FOR LOOP Statement » (ouverture, lecture et fermeture automatiques du curseur).
  9. Oracle, Database SQL Language Reference 19c : « SELECT », clause FOR UPDATE (verrouillage des lignes du résultat) ; « UPDATE » et « DELETE », clause WHERE CURRENT OF.

Fiche relue et sourcée en septembre 2026, refaite en récit en octobre 2026. Tout le code PL/pgSQL a été exécuté dans PostgreSQL 18 avec les données d'essai de la fiche.

Glisser pour continuer vers Triggers avancés
Université