Jan 1, 0001

Correction détaillée — Bloc 4 : Curseurs et traitement séquentiel

Partie A – Sur feuille

1. Lecture et compréhension

Différences fondamentales :

AspectRequête SQL directeCurseur PL/pgSQL
Contexte d’exécutionInterprété et optimisé par PostgreSQL à la voléeContrôlé ligne par ligne dans un bloc PL/pgSQL
RésultatEnsemble complet retourné d’un coupRésultat parcouru séquentiellement
Usage typiqueRequêtes d’agrégation, jointures, traitements globauxTraitements impératifs nécessitant logique par ligne
MémoireGéré côté serveur, optimisation ensemblisteZone mémoire temporaire dédiée au curseur

Justification (dans l’application de séjours) Les curseurs sont pertinents pour :

  • le parcours des séjours pour archivage, vérification ou purge,
  • les audits (par exemple vérification de capacités),
  • les traitements nécessitant action unitaire sur chaque tuple.

Ils ne sont pas nécessaires pour les requêtes purement déclaratives (INSERT ... SELECT, UPDATE ... FROM, etc.).

2. Structure d’un curseur explicite

Code complet et fonctionnel :

DO $$
DECLARE
  c_village CURSOR FOR
    SELECT ville, capacite FROM village;
  v_ville    text;
  v_capacite int;
BEGIN
  OPEN c_village;               -- Exécution et mise en mémoire du résultat
  LOOP
    FETCH c_village INTO v_ville, v_capacite;  -- Lecture d’une ligne
    EXIT WHEN NOT FOUND;                        -- Fin du curseur
    RAISE NOTICE 'Ville : %, Capacité : %', v_ville, v_capacite;
  END LOOP;
  CLOSE c_village;              -- Libération mémoire serveur
END;
$$;

Commentaires :

  • DECLARE : définit la requête associée au curseur.
  • OPEN : exécute la requête et initialise le pointeur avant la première ligne.
  • FETCH : charge la ligne courante dans les variables et avance le pointeur.
  • EXIT WHEN NOT FOUND : condition d’arrêt automatique à la fin du jeu de résultats.
  • CLOSE : obligatoire pour libérer les ressources.

3. Variante implicite

Version simplifiée (curseur implicite) :

DO $$
BEGIN
  FOR rec IN
    SELECT ville, capacite FROM village
  LOOP
    RAISE NOTICE 'Ville : %, Capacité : %', rec.ville, rec.capacite;
  END LOOP;
END;
$$;

Analyse comparative :

  • Pas besoin de OPEN, FETCH, ni CLOSE : tout est géré automatiquement.
  • Lisibilité et robustesse accrues : un seul bloc logique.
  • Recommandé pour la majorité des cas.
  • Identique sémantiquement à la version explicite, mais plus concise.

Partie B – Sur machine

Exercice 1 — Fonction clients_sans_sejour()

Correction :

CREATE OR REPLACE FUNCTION clients_sans_sejour()
RETURNS TABLE(idc bigint, nom text)
LANGUAGE plpgsql AS $$
BEGIN
  FOR rec IN
    SELECT idc, nom
    FROM client c
    WHERE NOT EXISTS (
      SELECT 1 FROM sejour s WHERE s.idc = c.idc
    )
  LOOP
    idc := rec.idc;
    nom := rec.nom;
    RETURN NEXT;
  END LOOP;
END;
$$;

Explication :

  • NOT EXISTS implémente la logique d’anti-jointure.
  • RETURN NEXT permet de renvoyer plusieurs lignes au fur et à mesure.
  • FOR IN SELECT suffit : curseur implicite.
  • Test : SELECT * FROM clients_sans_sejour();

Exercice 2 — Procédure audit_capacites()

Correction :

CREATE OR REPLACE PROCEDURE audit_capacites()
LANGUAGE plpgsql AS $$
DECLARE
  v_nb int;
BEGIN
  FOR rec IN
    SELECT idv, ville, capacite FROM village
  LOOP
    SELECT count(*) INTO v_nb
    FROM sejour
    WHERE idv = rec.idv
      AND CURRENT_DATE BETWEEN debut AND fin;

    RAISE NOTICE 'Village % : % / % lits occupés',
      rec.ville, v_nb, rec.capacite;

    IF v_nb >= rec.capacite THEN
      RAISE NOTICE '⚠ Village saturé : %', rec.ville;
    END IF;
  END LOOP;
END;
$$;

Test :

CALL audit_capacites();

Commentaires :

  • FOR rec IN : lecture ligne par ligne de village.
  • Calcul du nombre de séjours actifs en temps réel.
  • IF facultatif pour générer une alerte.

**Exercice 3 — Procédure purge_archivée(p_date_limite)

Correction :

CREATE OR REPLACE PROCEDURE purge_archivee(p_date_limite date)
LANGUAGE plpgsql AS $$
DECLARE
  v_count int := 0;
BEGIN
  FOR rec IN
    SELECT * FROM sejour WHERE fin < p_date_limite
  LOOP
    INSERT INTO archivesejour(ids, idc, idv, debut, fin, date_archivage)
    VALUES (rec.ids, rec.idc, rec.idv, rec.debut, rec.fin, now());

    DELETE FROM sejour WHERE ids = rec.ids;

    v_count := v_count + 1;
  END LOOP;

  RAISE NOTICE '% séjours archivés et supprimés', v_count;
END;
$$;

Test :

CALL purge_archivee('2025-09-01');

Points clés :

  • Traitement transactionnel par défaut (toutes les suppressions dans une même transaction).
  • Utilisation d’un curseur implicite.
  • Respect de la règle métier : archivage avant suppression.

**Exercice 4 — Procédure bilan_client(p_idc)

Correction :

CREATE OR REPLACE PROCEDURE bilan_client(p_idc bigint)
LANGUAGE plpgsql AS $$
DECLARE
  v_duree int;
  v_montant numeric;
  v_total numeric := 0;
BEGIN
  FOR rec IN
    SELECT s.ids, s.debut, s.fin, v.prix, v.ville
    FROM sejour s
    JOIN village v USING(idv)
    WHERE s.idc = p_idc
  LOOP
    v_duree := (rec.fin - rec.debut);
    v_montant := v_duree * rec.prix;
    v_total := v_total + v_montant;

    RAISE NOTICE 'Séjour % (%): % nuits, % €',
      rec.ids, rec.ville, v_duree, v_montant;
  END LOOP;

  RAISE NOTICE 'Montant total pour le client % : % €', p_idc, v_total;
END;
$$;

Test :

CALL bilan_client(1);

Commentaires :

  • Calcul cumulatif du montant total.
  • FOR rec IN gère implicitement le curseur.
  • Exemple d’usage typique : génération d’un relevé client.

Partie C – En autonomie

1. Fonction villages_disponibles (version capacitaire)

CREATE OR REPLACE FUNCTION villages_disponibles(p_ville text, p_debut date, p_fin date)
RETURNS TABLE(idv bigint, activite text, prix numeric)
LANGUAGE plpgsql AS $$
DECLARE
  v_nb int;
BEGIN
  FOR rec IN
    SELECT idv, activite, prix, capacite
    FROM village
    WHERE ville ILIKE p_ville
  LOOP
    SELECT count(*) INTO v_nb
    FROM sejour
    WHERE idv = rec.idv
      AND daterange(debut, fin, '[]') && daterange(p_debut, p_fin, '[]');

    IF v_nb < rec.capacite THEN
      idv := rec.idv;
      activite := rec.activite;
      prix := rec.prix;
      RETURN NEXT;
    END IF;
  END LOOP;
END;
$$;

Test :

SELECT * FROM villages_disponibles('Nice', '2025-07-01', '2025-07-10');

2. Procédure reservation_automatique

CREATE OR REPLACE PROCEDURE reservation_automatique(p_idc bigint, p_ville text, p_debut date, p_fin date)
LANGUAGE plpgsql AS $$
DECLARE
  v_idv bigint;
  v_activite text;
  v_prix numeric;
  v_ids bigint;
BEGIN
  SELECT idv, activite, prix
  INTO v_idv, v_activite, v_prix
  FROM villages_disponibles(p_ville, p_debut, p_fin)
  ORDER BY prix DESC, idv ASC
  LIMIT 1;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Aucun village disponible pour la période demandée';
  END IF;

  INSERT INTO sejour(idc, idv, debut, fin)
  VALUES (p_idc, v_idv, p_debut, p_fin)
  RETURNING ids INTO v_ids;

  RAISE NOTICE 'Réservation : client %, séjour %, village %, activité %',
    p_idc, v_ids, v_idv, v_activite;
END;
$$;

Test :

CALL reservation_automatique(1, 'Nice', '2025-07-01', '2025-07-10');

Partie D – Synthèse

Bonnes pratiquesJustification
Préférer FOR IN SELECTGère automatiquement ouverture/fermeture
Toujours fermer les curseurs explicitesÉvite les fuites mémoire
Utiliser EXIT WHEN NOT FOUNDArrêt sûr et idiomatique
Éviter les curseurs quand SQL ensembliste suffitMeilleure performance
Ajouter des RAISE NOTICE pour debugLisibilité et traçabilité des traitements

Conclusion : Les curseurs servent ici à implémenter la logique procédurale exigée par le cahier des charges (archivage, bilans, vérifications). Leur usage doit rester exceptionnel, réservé aux traitements séquentiels nécessitant une action par tuple.

Pierre-Henri Paris
Pierre-Henri Paris
Associate Professor in Artificial Intelligence

My research interests include Knowlegde Graphs, Information Extraction, and NLP.