Bases de données 2

Pourquoi utiliser des curseurs?

• En PL/pgSQL, la récupération d'une seule ligne peut se faire simplement avec:

• DO $$
  DECLARE
    x int;
    y text;
  BEGIN
    SELECT a, b INTO x, y
    FROM t
    WHERE cle = 123;
    
    RAISE NOTICE 'Valeurs : % %', x, y;
  END;
  $$;

  • Cette requête récupère la première ligne correspondant au critère.
  • Les valeurs sont placées dans les variables x et y.
• Problème: si la requête retourne plusieurs lignes, il faut utiliser un curseur pour parcourir toutes les lignes du résultat.

Qu'est-ce qu'un curseur en PL/pgSQL?

• Un curseur est une zone mémoire nommée gérée par le serveur PostgreSQL, qui contient les résultats d'une requête SQL.
• Caractéristiques d'un curseur:
  • Il est associé à une requête spécifique.
  • Il peut stocker 0, 1 ou plusieurs lignes selon le résultat de la requête.
  • Sa taille est déterminée à l'exécution, en fonction du nombre de lignes retournées.
• Objectif: permettre un traitement ligne par ligne d'un jeu de résultats SQL au sein d'un bloc PL/pgSQL.

graph LR
    A[Requête SQL] -->|OPEN| B[Curseur
en mémoire]
    B -->|FETCH| C[Traitement
ligne par ligne]
    C --> D[Variables]
    B -.->|CLOSE| E[Libération]
    
    style B fill:#e1f5ff,stroke:#0066cc,stroke-width:3px
    style C fill:#fff4e1,stroke:#ff9800,stroke-width:2px
    style E fill:#ffebee,stroke:#c62828,stroke-width:2px

Les étapes de fonctionnement d'un curseur

1. Déclaration du curseur: définition de la requête SQL qu'il parcourra.
2. Ouverture du curseur avec OPEN: exécution de la requête et mise en mémoire du jeu de résultats.
3. Lecture des lignes une à une avec FETCH (ou automatiquement via une boucle FOR).
4. Fermeture du curseur avec CLOSE: libération de la mémoire serveur.

Attributs et comportement d'un curseur

→ Très utile pour construire des boucles WHILE robustes.

⚠️ Important : %FOUND et %NOTFOUND sont indéfinis (NULL) après OPEN. Testez-les uniquement APRÈS un FETCH.

Exemple 1: Récupérer le premier article

• Table: article(refart, nom, prixht)
• Objectif: afficher la référence du premier article par ordre alphabétique.
• Hypothèse: la table contient au moins un article.
• Rappel: SELECT ... INTO ne peut récupérer qu'une seule ligne. Si la requête renvoie plusieurs lignes, une erreur se produit.
• Solution: utiliser ORDER BY ... LIMIT 1 ou un curseur pour parcourir les résultats.

Exemple 2: Vérifier s'il existe un deuxième article

• Objectif: afficher la référence du premier article par ordre alphabétique, et du deuxième s'il existe.
• Hypothèse: la table contient au moins un article.

DO $$
DECLARE
  c2 CURSOR FOR SELECT refart FROM article ORDER BY nom;
  art article.refart%TYPE;
BEGIN
  OPEN c2;
  FETCH c2 INTO art;
  RAISE NOTICE 'Premier article : %', art;
  
  FETCH c2 INTO art;
  IF c2%FOUND THEN
    RAISE NOTICE 'Deuxième article : %', art;
  ELSE
    RAISE NOTICE 'Pas de deuxième article';
  END IF;
  
  CLOSE c2;
END;
$$;

Exemple : Parcours complet avec LOOP

DO $$
DECLARE
  c3 CURSOR FOR
    SELECT refart FROM article ORDER BY nom;
  art article.refart%TYPE;
BEGIN
  OPEN c3;
  
  LOOP
    FETCH c3 INTO art;
    EXIT WHEN NOT FOUND;  -- ou c3%NOTFOUND
    RAISE NOTICE 'Article : %', art;
  END LOOP;

  CLOSE c3;
END;
$$;

Variante : boucle WHILE avec curseur

Il existe une autre syntaxe avec WHILE, moins courante :

DO $$
DECLARE
  c3 CURSOR FOR SELECT refart FROM article ORDER BY nom;
  art article.refart%TYPE;
BEGIN
  OPEN c3;
  FETCH c3 INTO art;  -- Premier FETCH avant la boucle
  
  WHILE c3%FOUND LOOP
    RAISE NOTICE 'Article : %', art;
    FETCH c3 INTO art;  -- FETCH à la fin de la boucle
  END LOOP;
  
  CLOSE c3;
END;
$$;

⚠️ Attention : Cette syntaxe nécessite un FETCH avant et dans la boucle (duplication). Le pattern LOOP + EXIT WHEN est généralement préféré.

Exemple : Curseur avec %ROWTYPE

DO $$
DECLARE
  c4 CURSOR FOR
    SELECT refart, prixht
    FROM article;
  art c4%ROWTYPE;  -- type dérivé (curseur)
BEGIN
  OPEN c4;
  
  LOOP
    FETCH c4 INTO art;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE '% %', art.refart, art.prixht;
  END LOOP;

  CLOSE c4;
END;
$$;

💡 Avantage de %ROWTYPE :

• Une seule variable pour toute la ligne (au lieu de refart et prixht séparés)
• Accès aux champs : art.refart, art.prixht
• S'adapte automatiquement si la requête du curseur change

Quand éviter les curseurs?

• Si le traitement peut être fait directement en SQL, il est préférable d'utiliser une requête.
• Exemple: au lieu de parcourir les articles un par un:

  SELECT refart, prixht
  FROM article
  WHERE prixht > (SELECT AVG(prixht) FROM article);

• Les curseurs sont utiles quand:
  • il faut un traitement conditionnel ou calculé ligne à ligne,
  • ou interagir avec plusieurs tables/étapes séquentielles.

Curseurs et boucles: meilleure approche

-- ✅ Boucle FOR implicite (recommandée)
FOR x IN
  SELECT a, b FROM ma_table
LOOP
  RAISE NOTICE '%, %', x.a, x.b;
END LOOP;

-- ⚙️ Équivalent explicite avec FETCH
DECLARE
  c CURSOR FOR SELECT a, b FROM ma_table;
  x RECORD;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO x;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE '%, %', x.a, x.b;
  END LOOP;
  CLOSE c;
END;

Conclusion: la boucle FOR ... IN SELECT est plus simple et évite OPEN/FETCH/CLOSE.

Comparatif: boucle explicite vs boucle implicite

→ Toujours privilégier la boucle FOR rec IN SELECT quand c'est possible.