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.
Informatique · PL/pgSQL (PostgreSQL)
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 quizLe 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.
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.
Après cette fiche, tu sais
format avec %I, FOR … IN EXECUTE, COUNT(*) INTO, RAISE NOTICE ;%FOUND, %ROWCOUNT…), et reconnaître les autres formes Oracle : curseur implicite, boucle FOR de curseur, paramètres, FOR UPDATE.Non, ça n'a pas de sens. Mais je ne suis pas sûr.
Une méthode avant le clavier
Étape 0 · La méthode
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.
Les entrées, les sorties exactes au caractère près, et les cas limites : club sans membre, numéro inconnu.
Pour chaque besoin, la construction PL/pgSQL qui y répond.
La logique en français, sans se soucier de la syntaxe.
Chaque ligne du pseudo-code devient une ou deux instructions.
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.
Le même curseur, deux syntaxes
Rappel · Le curseur
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
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 ».
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.
PL/pgSQL (PostgreSQL)
| PL/pgSQL (PostgreSQL) | PL/SQL (Oracle) | |
|---|---|---|
| Déclarer | c CURSOR FOR SELECT …; | CURSOR c IS SELECT …; |
| Fin du résultat | EXIT WHEN NOT FOUND; | EXIT WHEN c%NOTFOUND; |
| Lignes déjà lues | un compteur à tenir soi-même | c%ROWCOUNT |
| Afficher | RAISE NOTICE '%', v; | DBMS_OUTPUT.PUT_LINE(v); |
GET 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.Oracle · Les autres formes
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
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
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
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
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é
Créer une procédure AffiClub(i_param TEXT). Le paramètre peut servir de deux façons.
Les règles
'clu_no'), et on liste tous les clubs triés par cette colonne.Sinon
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.
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
Cinq besoins, cinq constructions. Le premier décide si i_param est un nombre ou un nom de colonne.
Le premier patron
Tenter la conversion i_param::INTEGER dans un bloc BEGIN … EXCEPTION : un texte comme 'clu_nom' lève invalid_text_representation (code 22P02).4
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
FOR r IN EXECUTE v_sql LOOP : la chaîne est évaluée et planifiée à chaque entrée dans la boucle.3
SELECT COUNT(*) INTO v_nb FROM … WHERE …; range le résultat dans une variable.
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.
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.
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 signalerLes 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
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.
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.
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.
À 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é).
Avec cinq membres, la formule de l'énoncé donnerait « et 1 autres » (testé) ; un IF de plus garde l'accord.
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
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.
Fais défiler : on suit les variables et la console, comme dans un débogueur.
'3'::INTEGER réussit : v_clu_no vaut 3, et v_sql cherche le club dont le numéro vaut 3.
FOR r_club IN EXECUTE v_sql renvoie une seule ligne, (3, Théâtre) : l'en-tête s'affiche.
SELECT COUNT(*) INTO donne 6 : on passe dans la branche ELSE.
La boucle triée affiche les trois premiers, puis continue sans afficher, en retenant le dernier vu : Maé Vuille.
6 > 5 : « ... et 2 autres ». 6 > 3 : le dernier, Maé Vuille. Fin, FOUND est vrai.
Le cast échoue, le bloc EXCEPTION construit ORDER BY clu_nom : les cinq clubs défilent, Badminton d'abord.
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.
Un club de 0 membre
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
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
Cas du cast
Le cast ::INTEGER accepte ' 3 ', mais refuse 'abc' et un nombre trop grand.
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
heg_club, pas heag_club ;% que de valeurs dans chaque RAISE NOTICE ;; après chaque instruction, END LOOP; et END IF; bien appariés ;$$ LANGUAGE plpgsql; à la fin, avec son point-virgule ;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.
La seconde, avec %I ? Mais pourquoi exactement ?
%I, la valeur ne devient jamais du SQL : elle reste un identifiant entre guillemets.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
%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.
Huit lignes ou situations, toutes testées dans PostgreSQL 18. Range chacune.
Un seul nom, deux versions
Bonus · La surcharge
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.
-- 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 VARCHARINTO 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.
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.
INTO STRICT
Avec INTO STRICT, la requête doit renvoyer exactement une ligne. Sinon, PostgreSQL arrête la procédure.
Quatre réflexes à garder
Habitudes de pro
Ces réflexes reviennent dans tous les exercices de curseurs de cette fiche.
%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.
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
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
Un % par valeur, sinon too few ou too many parameters specified for RAISE (testé) ; INITCAP('BERNASCONI') donne Bernasconi.
Entraînement
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.
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.
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
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) ;% 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
Douze questions pour finir
SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN, toujours FALSE).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.