Jan 1, 0001

Correction TD3

Correction — Partie A : PL/pgSQL (sur feuille)

1) Requête côté client vs fonction côté serveur

Différences clés

Côté client (SQL “ad hoc”) : la logique est dans l’application ; chaque requête traverse le réseau et le SGBD n’en voit qu’une à la fois.
Fonction/procédure stockée (PL/pgSQL, côté serveur) : la logique est stockée et exécutée dans PostgreSQL (analyse + planification à la création), appelée ensuite par l’application.

Deux avantages concrets côté serveur (dans notre contexte)

Moins d’allers/retours réseau : un bloc PL/pgSQL exécute plusieurs opérations d’un coup → latence réduite.
Cohérence et atomacité : on encapsule une suite d’actions transactionnelles dans une seule unité, avec gestion d’erreurs centralisée (plus facile à tester et à réutiliser). (+ bonus) Sécurité/encapsulation (droits sur fonctions, API SQL stable), performance (plans réutilisés), portabilité (même logique partagée par toutes les applis).

2) Compléter un bloc `DO` minimal

Attendu : déclaration, affectation, affichage.

DO $$
DECLARE
  message text := 'Hello PL/pgSQL';
BEGIN
  RAISE NOTICE '%', message;
END;
$$;

Commentaires

DO $$ ... $$; exécute un bloc anonyme (pas stocké).
DECLARE pour les variables ; BEGIN ... END pour le corps ; RAISE NOTICE pour afficher.

3) Traduction d’un scénario en PL/pgSQL

Énoncé : “Si la capacité du village idv = 5 est < 10, augmenter la capacité de 2 ; sinon, afficher un avertissement.”

a) Pseudo-code

lire capacite du village 5 → v_cap
si aucune ligne trouvée → erreur "village introuvable"
si v_cap < 10 alors
    mettre à jour capacite = v_cap + 2
    afficher "capacité passée de v_cap à v_cap+2"
sinon
    afficher "pas de modification (capacité >= 10)"
fin si

b) Version PL/pgSQL (propre et robuste)

DO $$
DECLARE
  v_cap int;
BEGIN
  -- 1) Lire la capacité
  SELECT capacite INTO v_cap
  FROM village
  WHERE idv = 5;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Village % introuvable', 5
      USING ERRCODE = 'P0001';
  END IF;

  -- 2) Décider et agir
  IF v_cap < 10 THEN
    UPDATE village
    SET capacite = v_cap + 2
    WHERE idv = 5;

    RAISE NOTICE 'Capacité mise à jour: % -> %', v_cap, v_cap + 2;
  ELSE
    RAISE NOTICE 'Aucune modification: capacité actuelle = % (>= 10)', v_cap;
  END IF;
END;
$$;

Variante SQL pure (pour culture générale, non exigée ici) Sans PL/pgSQL, on peut condenser la logique :

UPDATE village
SET capacite = capacite + 2
WHERE idv = 5 AND capacite < 10;

-- Puis informer selon le nombre de lignes affectées

Mais l’exercice vise IF/ELSE et l’usage de variables en PL/pgSQL.

4) Rôle des paramètres `IN`, `OUT`, `INOUT` (procédures)

IN : valeur d’entrée uniquement. La procédure reçoit la valeur, mais toute modification interne ne remonte pas au code appelant.
OUT : valeur de sortie. La procédure fixe cette valeur ; elle est renvoyée à l’appelant (initialement non définie côté appelant).
INOUT : entrée + sortie. L’appelant fournit une valeur initiale ; la procédure peut la lire et la modifier, et la valeur modifiée est renvoyée.

Idée d’exécution

Les procédures s’appellent avec CALL ….
Les valeurs OUT/INOUT sont renvoyées par la procédure ; en pratique, on les récupère facilement depuis un langage client (Python, Java, etc.) ou on les capture dans un bloc PL/pgSQL.

Exemple illustratif (schématique)

-- Définition
CREATE OR REPLACE PROCEDURE maj(
  x IN int,      -- lu uniquement
  y OUT int,     -- produit par la procédure
  z INOUT int    -- lu et modifié
)
AS $$
BEGIN
  y := x * 2;    -- on fabrique la sortie y
  z := z + 10;   -- on modifie z (retournera la nouvelle valeur)
END;
$$ LANGUAGE plpgsql;

-- Usage typique depuis un bloc PL/pgSQL (pour capturer OUT/INOUT) :
DO $$
DECLARE
  vy int; 
  vz int := 5;
BEGIN
  CALL maj(3, vy, vz);
  RAISE NOTICE 'y=%, z=%', vy, vz;  -- y=6, z=15
END;
$$;

À retenir

Utiliser IN pour les paramètres lus/consommés ;
OUT pour renvoyer des résultats calculés ;
INOUT quand le même paramètre sert d’entrée et de sortie.

Correction — Partie B : sur machine (PL/pgSQL : les bases)

Préambule

Vous travaillez sur la base issue du Bloc 2 (tables client, village, sejour, archivesejour déjà créées et contraintes posées).
Chaque code peut être exécuté dans psql, DBeaver, ou pgAdmin.

Si nécessaire, réactivez l’extension :

CREATE EXTENSION IF NOT EXISTS plpgsql;

Exercice 1 – Fonction de diagnostic

Objectif

Compter le nombre de lignes d’une table client.

Solution

CREATE OR REPLACE FUNCTION compter_clients()
RETURNS integer AS $$
DECLARE
  nb integer;
BEGIN
  SELECT COUNT(*) INTO nb FROM client;
  RAISE NOTICE 'Nombre de clients = %', nb;
  RETURN nb;
END;
$$ LANGUAGE plpgsql;

Test

SELECT compter_clients();

Commentaires

INTO nb : stocke le résultat du SELECT dans la variable locale.
RAISE NOTICE affiche un message (comme print en Python).

Alternative “SQL pure” (plus légère) :

CREATE OR REPLACE FUNCTION compter_clients_sql()
RETURNS integer AS
  'SELECT COUNT(*) FROM client;'
LANGUAGE sql;

Exercice 2 – Procédure d’inscription

Objectif

Insérer un nouveau client et afficher son identifiant.

Solution

CREATE OR REPLACE PROCEDURE inscrire_client(p_nom text, p_age int)
AS $$
DECLARE
  new_id bigint;
BEGIN
  IF p_age < 0 THEN
    RAISE EXCEPTION 'Âge invalide : %', p_age
      USING ERRCODE = 'P0001';
  END IF;

  INSERT INTO client(nom, age)
  VALUES (p_nom, p_age)
  RETURNING idc INTO new_id;

  RAISE NOTICE 'Client ajouté : % (idc=%)', p_nom, new_id;
END;
$$ LANGUAGE plpgsql;

Test

CALL inscrire_client('Alice', 30);
CALL inscrire_client('Bob', -5);  -- génère une exception

Commentaires

RETURNING … INTO récupère la clé générée.
CALL exécute une procédure (pas de valeur de retour).
L’usage de RAISE EXCEPTION démontre la gestion d’erreur.

Exercice 3 – Fonction “montant du séjour”

Objectif

Calculer le coût total (nombre de nuits × prix journalier).

Solution

CREATE OR REPLACE FUNCTION montant_sejour(p_ids bigint)
RETURNS numeric AS $$
DECLARE
  v_duree int;
  v_prix numeric;
  v_montant numeric;
BEGIN
  SELECT (fin - debut), prix
    INTO v_duree, v_prix
  FROM sejour s
  JOIN village v USING(idv)
  WHERE s.ids = p_ids;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Séjour % introuvable', p_ids;
  END IF;

  v_montant := v_duree * v_prix;
  RAISE NOTICE 'Durée : % jours — Prix/jour : % € — Total : % €',
               v_duree, v_prix, v_montant;

  RETURN v_montant;
END;
$$ LANGUAGE plpgsql;

Test

SELECT montant_sejour(1);

Commentaires

(fin - debut) retourne un entier (différence de jours).
Si le séjour n’existe pas, on lève une erreur explicite.
Permet d’introduire la notion de variable calculée et de RAISE NOTICE formaté.

Exercice 4 – Procédure “purge des séjours terminés”

Objectif

Supprimer les séjours terminés avant une date donnée.

Solution

CREATE OR REPLACE PROCEDURE purge_sejours(p_date_limite date)
AS $$
DECLARE
  nb_suppr integer;
BEGIN
  DELETE FROM sejour
  WHERE fin < p_date_limite
  RETURNING * INTO nb_suppr;  -- compte le nombre supprimé

  RAISE NOTICE '% séjours supprimés (fin < %)',
               nb_suppr, p_date_limite;
END;
$$ LANGUAGE plpgsql;

Variante robuste :

DELETE FROM sejour
WHERE fin < p_date_limite;
GET DIAGNOSTICS nb_suppr = ROW_COUNT;

Test

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

Commentaires

GET DIAGNOSTICS nb_suppr = ROW_COUNT; compte les lignes affectées sans dépendre du RETURNING.
Gestion transactionnelle : les suppressions peuvent être confirmées ou annulées (ROLLBACK).

Exercice 5 – Procédure “annuler un séjour”

Objectif

Annuler un séjour et archiver l’information.

Solution

CREATE OR REPLACE PROCEDURE annuler_sejour(p_ids bigint, p_motif text)
AS $$
DECLARE
  r record;
BEGIN
  SELECT * INTO r
  FROM sejour
  WHERE ids = p_ids;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Séjour % introuvable', p_ids
      USING ERRCODE = 'P0001';
  END IF;

  INSERT INTO archivesejour(ids, idc, idv, debut, fin, date_archivage)
  VALUES (r.ids, r.idc, r.idv, r.debut, r.fin, NOW());

  DELETE FROM sejour WHERE ids = p_ids;

  RAISE NOTICE 'Séjour % annulé (%). Archivé le %.',
               p_ids, p_motif, NOW();
END;
$$ LANGUAGE plpgsql;

Test

CALL annuler_sejour(1, 'Annulation administrative');

Commentaires

Le record permet de capturer une ligne complète.
L’ordre “insertion → suppression” garantit que les données sont sauvegardées avant d’être effacées.
Prépare le terrain pour l’automatisation via trigger (Bloc 5).

Exercice 6 – Fonction “villages disponibles”

Objectif

Lister les villages d’une ville donnée (sans gestion de capacité pour l’instant).

Solution

CREATE OR REPLACE FUNCTION disponibilite(
  p_ville text, p_debut date, p_fin date
)
RETURNS TABLE(idv bigint, activite text, prix numeric) AS $$
BEGIN
  RETURN QUERY
  SELECT idv, activite, prix
  FROM village
  WHERE ville ILIKE p_ville
  ORDER BY prix DESC;
END;
$$ LANGUAGE plpgsql;

Test

SELECT * FROM disponibilite('Rio', '2025-07-01', '2025-07-10');

Commentaires

RETURN QUERY : renvoie plusieurs lignes.
ILIKE : insensible à la casse.
Dans les blocs futurs, la logique de capacité et de chevauchement sera intégrée ici.

Exercice 7 – Bloc anonyme de test

Objectif

Combiner plusieurs appels et affichages.

Solution

DO $$
DECLARE
  avant int;
  apres int;
  delta int;
BEGIN
  SELECT COUNT(*) INTO avant FROM sejour;

  CALL purge_sejours('2025-06-01');

  SELECT COUNT(*) INTO apres FROM sejour;

  delta := avant - apres;
  RAISE NOTICE 'Avant : %, Après : %, Supprimés : %', avant, apres, delta;
END;
$$;

Commentaires

Les blocs DO sont idéaux pour tester des procédures.
delta illustre une variable purement calculée.
C’est un bon exercice de synthèse : SELECT INTO + CALL + RAISE NOTICE.

✅ Bilan et conseils

Compétence	Illustration dans le TD
Déclaration de variables	`DECLARE …`
Affectation & calcul	`:=`, expressions
Conditions	`IF … THEN … ELSE … END IF;`
Requêtes en variables	`SELECT … INTO`
Boucles	(à aborder plus tard, Bloc 4)
Retour de fonction	`RETURN`, `RETURN QUERY`
Appel procédure/fonction	`CALL` / `SELECT`
Messages & erreurs	`RAISE NOTICE / EXCEPTION`

Bonnes pratiques :

Toujours nommer et versionner vos fonctions (CREATE OR REPLACE).
Documenter (COMMENT ON FUNCTION … IS …;).
Tester les cas d’erreur (IF NOT FOUND, RAISE EXCEPTION).
Préférer des fonctions courtes, cohérentes, réutilisables.

Correction — Partie C : Devoir maison / en autonomie

Exercice 1 — Fonction `plus_cher_par_ville()`

Objectif

Renvoyer, pour chaque ville, le village le plus cher, sous la forme d’un tableau : (ville, idv, prix).

Solution

CREATE OR REPLACE FUNCTION plus_cher_par_ville()
RETURNS TABLE(ville text, idv bigint, prix numeric) AS $$
BEGIN
  RETURN QUERY
  SELECT v.ville, v.idv, v.prix
  FROM village v
  WHERE v.prix = (
    SELECT MAX(v2.prix)
    FROM village v2
    WHERE v2.ville = v.ville
  )
  ORDER BY v.ville;
END;
$$ LANGUAGE plpgsql;

Explications

On utilise une sous-requête corrélée : pour chaque ville v.ville, on récupère le MAX(prix) correspondant.
RETURN QUERY renvoie plusieurs lignes.
En cas d’ex æquo, tous les villages les plus chers apparaissent (comportement acceptable pour cet exercice).

Variante (fenêtres SQL, plus élégante)

CREATE OR REPLACE FUNCTION plus_cher_par_ville_window()
RETURNS TABLE(ville text, idv bigint, prix numeric) AS $$
BEGIN
  RETURN QUERY
  SELECT ville, idv, prix
  FROM (
    SELECT ville, idv, prix,
           RANK() OVER (PARTITION BY ville ORDER BY prix DESC, idv ASC) AS rk
    FROM village
  ) t
  WHERE rk = 1;
END;
$$ LANGUAGE plpgsql;

Test

SELECT * FROM plus_cher_par_ville();

Commentaires

RANK() offre un moyen plus lisible et extensible (notamment si l’on veut gérer des égalités).
L’usage de ORDER BY prix DESC, idv ASC permet de départager proprement les égalités.

Exercice 2 — Procédure `mise_a_jour_prix(v_ville, delta)`

Objectif

Augmenter le prix journalier de tous les villages d’une ville donnée de delta €.

Solution

CREATE OR REPLACE PROCEDURE mise_a_jour_prix(
  v_ville text,
  delta numeric
)
AS $$
DECLARE
  nb_modif integer;
BEGIN
  UPDATE village
  SET prix = prix + delta
  WHERE ville ILIKE v_ville;

  GET DIAGNOSTICS nb_modif = ROW_COUNT;

  IF nb_modif = 0 THEN
    RAISE NOTICE 'Aucun village trouvé pour %', v_ville;
  ELSE
    RAISE NOTICE '% village(s) de % mis à jour (+% €)', nb_modif, v_ville, delta;
  END IF;
END;
$$ LANGUAGE plpgsql;

Test

CALL mise_a_jour_prix('Rio', 5);
CALL mise_a_jour_prix('Paris', 0); -- cas sans modification

Explications

ILIKE : insensibilité à la casse pour le nom de la ville.
GET DIAGNOSTICS nb_modif = ROW_COUNT; : permet de connaître le nombre de lignes modifiées.
RAISE NOTICE : affiche un message de confirmation.

Variante avec sécurité métier

IF delta <= 0 THEN
  RAISE EXCEPTION 'Le delta doit être strictement positif (fourni : %)', delta;
END IF;

À insérer avant le UPDATE, pour éviter des diminutions accidentelles.

Exercice 3 — Test combiné (fonction + procédure)

Bloc de test

DO $$
DECLARE
  rec record;
BEGIN
  RAISE NOTICE '=== AVANT MISE À JOUR ===';
  FOR rec IN SELECT * FROM plus_cher_par_ville() LOOP
    RAISE NOTICE '% : village % (% €)', rec.ville, rec.idv, rec.prix;
  END LOOP;

  CALL mise_a_jour_prix('Rio', 10);

  RAISE NOTICE '=== APRÈS MISE À JOUR ===';
  FOR rec IN SELECT * FROM plus_cher_par_ville() LOOP
    RAISE NOTICE '% : village % (% €)', rec.ville, rec.idv, rec.prix;
  END LOOP;
END;
$$;

Résultat attendu (exemple)

NOTICE:  === AVANT MISE À JOUR ===
NOTICE:  Rio : village 3 (95 €)
NOTICE:  Paris : village 5 (80 €)
NOTICE:  === APRÈS MISE À JOUR ===
NOTICE:  Rio : village 3 (105 €)
NOTICE:  Paris : village 5 (80 €)

Commentaires

Élément	Objectif visé	Exemple dans le code
Variables et boucles	Itérer sur des lignes de résultat	`FOR rec IN SELECT … LOOP`
Fonctions PL/pgSQL	Extraire des données complexes	`plus_cher_par_ville()`
Procédures	Effectuer des actions transactionnelles	`mise_a_jour_prix()`
Gestion des messages	Communication côté serveur	`RAISE NOTICE`
Diagnostic	Retour utilisateur	`GET DIAGNOSTICS ROW_COUNT`

Correction — Partie D : Pour aller plus loin

Exercice 1 — Gestion d’erreurs : lever une exception si le séjour à annuler n’existe pas

Objectif

Améliorer la procédure annuler_sejour() du Bloc 3 – Partie B en ajoutant une vraie gestion d’erreur métier.

Solution

CREATE OR REPLACE PROCEDURE annuler_sejour(
  p_ids   bigint,
  p_motif text
)
AS $$
DECLARE
  r record;
BEGIN
  SELECT * INTO r
  FROM sejour
  WHERE ids = p_ids;

  IF NOT FOUND THEN
    RAISE EXCEPTION
      USING MESSAGE = format('Séjour % introuvable', p_ids),
            ERRCODE = 'P0001';
  END IF;

  INSERT INTO archivesejour(ids, idc, idv, debut, fin, date_archivage)
  VALUES (r.ids, r.idc, r.idv, r.debut, r.fin, NOW());

  DELETE FROM sejour WHERE ids = p_ids;

  RAISE NOTICE 'Séjour % annulé (%). Archivé le %.',
               p_ids, p_motif, NOW();

EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'Doublon détecté dans ArchiveSejour.';
  WHEN OTHERS THEN
    RAISE NOTICE 'Erreur inattendue: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

Explications

IF NOT FOUND THEN … : capte le cas où le SELECT INTO ne renvoie rien.
RAISE EXCEPTION USING MESSAGE = …, ERRCODE = … : bonne pratique pour lever des erreurs applicatives.
Bloc EXCEPTION : capture des erreurs SQL précises (unique_violation, foreign_key_violation, etc.) et permet de traiter le cas sans interrompre toute la transaction.

Test

CALL annuler_sejour(999, 'Test erreur');

Résultat attendu :

ERROR:  Séjour 999 introuvable
SQL state: P0001

Variante

Tester avec un séjour existant pour voir l’archivage correct :

CALL annuler_sejour(1, 'Client demande annulation');
SELECT * FROM archivesejour ORDER BY date_archivage DESC LIMIT 1;

Exercice 2 — Comparaison : `LANGUAGE sql` vs `LANGUAGE plpgsql`

Objectif

Illustrer la différence entre une fonction purement SQL et une fonction PL/pgSQL, comme vu en cours.

Fonction SQL simple

CREATE OR REPLACE FUNCTION double_sql(x int)
RETURNS int AS
  'SELECT x * 2;'
LANGUAGE sql;

Fonction PL/pgSQL équivalente

CREATE OR REPLACE FUNCTION double_pg(x int)
RETURNS int AS $$
BEGIN
  RETURN x * 2;
END;
$$ LANGUAGE plpgsql;

Tests

SELECT double_sql(10);  -- 20
SELECT double_pg(10);   -- 20

Explications

Aspect	`LANGUAGE sql`	`LANGUAGE plpgsql`
Type de fonction	Purement déclarative	Procédurale
Performances	Très rapides (optimisées)	Légèrement plus lentes (interprétation)
Possibilités	Pas de variables, ni IF/ELSE	Tests, boucles, exceptions
Usage typique	Calcul simple, projection	Traitement métier, logique conditionnelle

Exemple de micro-comparaison de performance

\timing on
SELECT double_sql(1000000);
SELECT double_pg(1000000);

Sur de petits calculs, la différence est négligeable ; sur des millions d’appels, LANGUAGE sql est légèrement plus rapide.

Exercice 3 — Bloc anonyme de test et transaction

Objectif

Montrer comment une transaction explicite fonctionne dans un bloc PL/pgSQL, et comment tester la persistance.

Solution

DO $$
DECLARE
  avant int;
  apres int;
BEGIN
  SELECT COUNT(*) INTO avant FROM sejour;
  RAISE NOTICE 'Avant la purge : % séjours', avant;

  BEGIN
    CALL purge_sejours('2025-07-01');
    RAISE NOTICE 'Purge effectuée (COMMIT volontaire)';
    COMMIT;  -- valide la suppression
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE NOTICE 'Transaction annulée.';
  END;

  SELECT COUNT(*) INTO apres FROM sejour;
  RAISE NOTICE 'Après la purge : % séjours', apres;
END;
$$;

Explications

Les procédures (pas les fonctions) peuvent contenir des COMMIT et ROLLBACK.
Les blocs BEGIN … EXCEPTION … END peuvent jouer le rôle de sous-transactions locales (comme des “savepoints”).
RAISE NOTICE sert ici de trace pour suivre l’état de la base à chaque étape.

Variante : simulation d’une erreur contrôlée

DO $$
BEGIN
  CALL purge_sejours('2025-07-01');
  RAISE EXCEPTION 'Erreur simulée après suppression';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE NOTICE 'Rollback effectué — aucune suppression persistée';
END;
$$;

Exercice 4 — Analyse de performances (`\timing`)

Objectif

Comparer l’exécution d’une fonction PL/pgSQL et d’une requête SQL équivalente.

Exemple pratique : comptage des clients

-- Version SQL
CREATE OR REPLACE FUNCTION nb_clients_sql()
RETURNS int AS
  'SELECT COUNT(*) FROM client;'
LANGUAGE sql;

-- Version PL/pgSQL
CREATE OR REPLACE FUNCTION nb_clients_pg()
RETURNS int AS $$
DECLARE
  n int;
BEGIN
  SELECT COUNT(*) INTO n FROM client;
  RETURN n;
END;
$$ LANGUAGE plpgsql;

Test de performance

\timing on
SELECT nb_clients_sql();
SELECT nb_clients_pg();
\timing off

Observation :

Pour un simple COUNT, la version SQL est plus directe et donc plus rapide.
Dès qu’on ajoute des tests ou de la logique conditionnelle, PL/pgSQL devient indispensable.

Exercice 5 — Amélioration du design de `montant_sejour`

Objectif

Mettre en pratique les “bonnes pratiques” évoquées en cours : fonctions courtes, gestion d’erreurs, messages explicites.

Version améliorée

CREATE OR REPLACE FUNCTION montant_sejour(p_ids bigint)
RETURNS numeric AS $$
DECLARE
  v_duree int;
  v_prix numeric;
BEGIN
  SELECT (fin - debut), prix
  INTO v_duree, v_prix
  FROM sejour s JOIN village v USING(idv)
  WHERE s.ids = p_ids;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Séjour % inexistant', p_ids
      USING ERRCODE = 'P0001';
  END IF;

  IF v_duree <= 0 THEN
    RAISE EXCEPTION 'Dates incohérentes pour le séjour %', p_ids;
  END IF;

  RETURN ROUND(v_duree * v_prix, 2);
END;
$$ LANGUAGE plpgsql;

Test

SELECT montant_sejour(1);

Explications

On ajoute un contrôle métier (v_duree <= 0).
ROUND(..., 2) pour formater à deux décimales.
ERRCODE : pratique pour catégoriser les erreurs côté client.

Exercice 6 — Documentation et nettoyage

Objectif

Encourager la documentation systématique des fonctions/procédures.

Exemple

COMMENT ON FUNCTION montant_sejour(bigint)
IS 'Renvoie le montant total d''un séjour (durée × prix/jour)';

COMMENT ON PROCEDURE annuler_sejour(bigint, text)
IS 'Annule un séjour et archive ses données dans ArchiveSejour';

Vérification

\d+ montant_sejour

Avantage : ces commentaires apparaissent dans pgAdmin et la commande \df+ de psql.

🧭 Bilan final du Bloc 3

Compétence	Concept clé	Exemple
Encapsulation côté base	Fonctions & procédures stockées	`montant_sejour`, `annuler_sejour`
Contrôle procédural	IF, LOOP, EXCEPTION	Bloc anonyme, `annuler_sejour`
Erreurs explicites	`RAISE EXCEPTION` avec `ERRCODE`	Validation métier
Résultats multiples	`RETURN QUERY`	`plus_cher_par_ville`
Transactions	`COMMIT` / `ROLLBACK`	Purge avec annulation
Documentation	`COMMENT ON …`	Bonnes pratiques de maintenance

Correction TD3

Correction — Partie A : PL/pgSQL (sur feuille)

1) Requête côté client vs fonction côté serveur

2) Compléter un bloc DO minimal

3) Traduction d’un scénario en PL/pgSQL

a) Pseudo-code

b) Version PL/pgSQL (propre et robuste)

4) Rôle des paramètres IN, OUT, INOUT (procédures)

Correction — Partie B : sur machine (PL/pgSQL : les bases)

Préambule

Exercice 1 – Fonction de diagnostic

Objectif

Solution

Test

Commentaires

Exercice 2 – Procédure d’inscription

Objectif

Solution

Test

Commentaires

Exercice 3 – Fonction “montant du séjour”

Objectif

Solution

Test

Commentaires

Exercice 4 – Procédure “purge des séjours terminés”

Objectif

Solution

Variante robuste :

Test

Commentaires

Exercice 5 – Procédure “annuler un séjour”

Objectif

Solution

Test

Commentaires

Exercice 6 – Fonction “villages disponibles”

Objectif

Solution

Test

Commentaires

Exercice 7 – Bloc anonyme de test

Objectif

Solution

Commentaires

✅ Bilan et conseils

Correction — Partie C : Devoir maison / en autonomie

Exercice 1 — Fonction plus_cher_par_ville()

Objectif

Solution

Explications

Variante (fenêtres SQL, plus élégante)

Test

Commentaires

Exercice 2 — Procédure mise_a_jour_prix(v_ville, delta)

Objectif

Solution

Test

Explications

Variante avec sécurité métier

Exercice 3 — Test combiné (fonction + procédure)

Bloc de test

Résultat attendu (exemple)

Commentaires

Correction — Partie D : Pour aller plus loin

Exercice 1 — Gestion d’erreurs : lever une exception si le séjour à annuler n’existe pas

Objectif

Solution

Explications

Test

Variante

Exercice 2 — Comparaison : LANGUAGE sql vs LANGUAGE plpgsql

Objectif

Fonction SQL simple

Fonction PL/pgSQL équivalente

Tests

Explications

Exemple de micro-comparaison de performance

Exercice 3 — Bloc anonyme de test et transaction

Objectif

2) Compléter un bloc `DO` minimal

4) Rôle des paramètres `IN`, `OUT`, `INOUT` (procédures)

Exercice 1 — Fonction `plus_cher_par_ville()`

Exercice 2 — Procédure `mise_a_jour_prix(v_ville, delta)`

Exercice 2 — Comparaison : `LANGUAGE sql` vs `LANGUAGE plpgsql`

Exercice 4 — Analyse de performances (`\timing`)

Exercice 5 — Amélioration du design de `montant_sejour`