Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 7 sur 12
Manipuler les curseurs
Déclarer, ouvrir, lire et fermer un curseur, le handler NOT FOUND et les curseurs FOR UPDATE.
- ≈ 120 min
- 4 leçons
- 1 vidéo
- 3 exercices
Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 7 sur 12
Déclarer, ouvrir, lire et fermer un curseur, le handler NOT FOUND et les curseurs FOR UPDATE.
Objectifs
Avec ce chapitre, tu sauras…
DECLARE, OPEN, FETCH, CLOSE ;NOT FOUND ;FOR UPDATE et connaître ses restrictions.Leçon 1
Tout curseur MySQL a ces trois propriétés :
| Propriété | Signification |
|---|---|
| Read-only (lecture seule) | on ne peut rien modifier dans la base à travers le curseur (sauf avec la clause FOR UPDATE, section 4) |
| Non-scrollable (non navigable) | une fois ouvert, on le parcourt du début à la fin, sans pouvoir revenir à la ligne précédente |
| Asensitive (insensible) | une modification de la base après l'ouverture n'est pas visible dans le curseur : il travaille sur une copie temporaire des données |
Leçon 2
Leçon 3
Vidéo · À regarder
FRBase de données : Gestion des erreurs en MySQL
Algomius · YouTube
Leçon 4
1Un curseur permet de…
2L'ordre des étapes est…
3« Non-scrollable » signifie…
4Quel handler accompagne toujours un curseur ?
5FETCH met la ligne courante…
6Les verrous d'un curseur FOR UPDATE sont libérés…
7Un curseur FOR UPDATE peut contenir un GROUP BY.
L'essentiel du chapitre
SELECT ; lecture seule, non navigable, insensible.DECLARE c CURSOR FOR SELECT …; → OPEN c; → FETCH c INTO v1, v2; (dans une boucle) → CLOSE c;.DECLARE CONTINUE HANDLER FOR NOT FOUND SET fini = 1;, et tester fini juste après le FETCH.FOR UPDATE : verrouille les lignes dès l'OPEN, jusqu'à la fin de la transaction ; interdit avec DISTINCT, GROUP BY, UNION, agrégats.OPEN : exécuter le SELECT et préparer le jeu de résultats ;FETCH : lire la ligne pointée dans des variables, et avancer le curseur à la ligne suivante ;DECLARE nom_curseur CURSOR FOR instruction_SELECT;
OPEN nom_curseur;
FETCH nom_curseur INTO liste_variables;
CLOSE nom_curseur;Le résumé dit de déclarer le curseur « avant la déclaration des variables » : c'est l'inverse. MySQL impose l'ordre variables → curseurs → handlers (sinon erreur Variable or condition declaration after cursor or handler declaration). L'exemple du résumé respecte d'ailleurs ce bon ordre.
Ce curseur construit une chaîne qui contient l'id, le nom et le prénom de chaque client.
DELIMITER $$
CREATE PROCEDURE lister_clients (INOUT resultat_txt VARCHAR(4000))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE v_id INT;
DECLARE v_nom VARCHAR(100);
DECLARE v_prenom VARCHAR(100);
DECLARE info VARCHAR(400) DEFAULT '';
DECLARE cur_info_client CURSOR FOR SELECT id, nom, prenom FROM clients;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN cur_info_client;
boucle_parcours_clients: LOOP
FETCH cur_info_client INTO v_id, v_nom, v_prenom;
IF finished = 1 THEN
LEAVE boucle_parcours_clients;
END IF;
SET info = CONCAT(v_id, '-', v_nom, '-', v_prenom);
SET resultat_txt = CONCAT(info, ';', resultat_txt);
END LOOP boucle_parcours_clients;
CLOSE cur_info_client;
END $$
DELIMITER ;
-- Appel de la procédure
SET @resultat_txt = '';
CALL lister_clients(@resultat_txt);
SELECT @resultat_txt;
-- 5-Fahmi-Khalid;4-Kamali-Ali;3-Rami-Ahmed;2-Zaki-Fatima;1-alami-said;Comment ça marche ?
finished vaut 0 ; le handler CONTINUE … FOR NOT FOUND le passera à 1 quand il n'y aura plus de ligne ;OPEN exécute le SELECT ;FETCH met la ligne courante dans v_id, v_nom, v_prenom ;FETCH suivant ne trouve rien : NOT FOUND → finished = 1 → LEAVE ;CLOSE libère le curseur.La chaîne est construite à l'envers (le nouveau client est mis devant : CONCAT(info, ';', resultat_txt)), d'où l'ordre 5, 4, 3, 2, 1.
SELECT.NOT FOUND pour gérer la fin des lignes. Sans lui, le dernier FETCH provoque l'erreur 1329 No data - zero rows fetched.IF finished = 1 se fait juste après le FETCH, avant d'utiliser les variables : sinon la dernière ligne est traitée deux fois.L'exemple contient des fautes de frappe qui empêchent sa création : VARVHAR → VARCHAR ; FETCH … INTO v_id, v_nomn v_prenom → v_id, v_nom, v_prenom ; l'appel utilise @resltat_txt puis affiche @resultat_txt (deux noms différents : le résultat est vide). Les chaînes vides écrites '''' (quatre apostrophes) donnent en réalité une apostrophe : une chaîne vide s'écrit ''.
Ce que tu vas voir (17 min) : les chapitres « Curseurs », « Parcourir un curseur ». Le formateur déclare un curseur, l'ouvre et le parcourt avec FETCH dans une boucle, en arrêtant la boucle avec un handler NOT FOUND.
Pour verrouiller les lignes lues par un curseur afin de les mettre à jour, sans qu'un autre utilisateur ne les modifie en même temps, on ajoute la clause FOR UPDATE au SELECT du curseur. Les lignes sont verrouillées dès l'ouverture du curseur. Les verrous sont libérés à la fin de la transaction (COMMIT ou ROLLBACK).
DELIMITER $$
CREATE PROCEDURE lister_clients_avec_MAJ_adresse (INOUT resultat_txt VARCHAR(4000))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE v_id INT;
DECLARE v_nom VARCHAR(100);
DECLARE v_prenom VARCHAR(100);
DECLARE v_adresse VARCHAR(100);
DECLARE info VARCHAR(400) DEFAULT '';
DECLARE cur_info_client CURSOR FOR SELECT id, nom, prenom, adresse
FROM clients FOR UPDATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN cur_info_client;
boucle_parcours_clients: LOOP
FETCH cur_info_client INTO v_id, v_nom, v_prenom, v_adresse;
IF finished = 1 THEN
LEAVE boucle_parcours_clients;
END IF;
IF UPPER(v_adresse) = 'AGADIR' THEN
UPDATE clients
SET adresse = 'GRANDE AGADIR'
WHERE id = v_id;
END IF;
SET info = CONCAT(v_id, '-', v_nom, '-', v_prenom);
SET resultat_txt = CONCAT(info, ';', resultat_txt);
END LOOP boucle_parcours_clients;
CLOSE cur_info_client;
END $$
DELIMITER ;
START TRANSACTION;
SET @r = '';
CALL lister_clients_avec_MAJ_adresse(@r);
SELECT adresse FROM clients; -- les deux clients d'Agadir sont devenus « GRANDE AGADIR »
COMMIT; -- libère les verrous (ou ROLLBACK pour annuler)SET set finished = 1 répète le mot SET ; boucle _parcours_clients contient un espace dans l'étiquette ; et la nouvelle adresse est écrite « GRANDE AGADAIR ». Ces trois fautes sont corrigées ci-dessus.
Ce traitement peut aussi s'écrire en une requête : UPDATE clients SET adresse = 'GRANDE AGADIR' WHERE UPPER(adresse) = 'AGADIR';. Elle est beaucoup plus rapide. On réserve les curseurs aux traitements ligne par ligne qu'une requête ne sait pas faire (appeler une procédure par ligne, calculs qui dépendent de la ligne précédente…).
Les restrictions
On ne peut pas déclarer un curseur FOR UPDATE si sa requête utilise :
DISTINCT ;GROUP BY ;UNION…) ;COUNT, SUM, AVG…).Dans ces cas, une ligne du résultat ne correspond plus à une ligne de la table : MySQL ne sait pas quelle ligne verrouiller.
Avec un curseur FOR UPDATE sur les produits, écris la procédure sp_augmenter_categorie() : les produits de la catégorie « Informatique » augmentent de 5 %, ceux de « Téléphonie » de 10 %. La procédure renvoie (paramètre OUT) le nombre de produits modifiés. Lance-la dans une transaction, vérifie, puis annule avec ROLLBACK.
Avec un curseur sur la table produit, écris la procédure sp_valeur_stock(OUT p_total DECIMAL(14,2)) qui additionne pu * qtstock de chaque produit. Vérifie ensuite le résultat avec une seule requête SUM.
Cette procédure affiche deux fois le dernier client. Pourquoi ? Corrige-la.
boucle: LOOP
FETCH cur INTO v_nom;
SET liste = CONCAT(liste, v_nom, ', ');
IF fini = 1 THEN LEAVE boucle; END IF;
END LOOP;