Utiliser des curseurs et des boucles dans MySQL

Considérez les boucles dans la programmation générale. Ils vous aident à exécuter une séquence spécifique d’instructions à plusieurs reprises jusqu’à ce qu’une condition particulière interrompe la boucle. MySQL fournit également un moyen d’exécuter des instructions sur des lignes individuelles en utilisant curseurs. Les curseurs dans MySQL exécuteront un ensemble d’instructions sur les lignes renvoyées par les requêtes SQL.

Propriétés des curseurs MySQL

• Non défilant : vous ne pouvez parcourir les lignes que dans une seule direction. Vous ne pouvez pas sauter une ligne ; vous ne pouvez pas sauter à une ligne ; vous ne pouvez pas revenir à une rangée.
• Lecture seule : vous ne pouvez pas mettre à jour ou supprimer des lignes à l’aide de curseurs.
• Asensible : les curseurs MySQL pointent vers les données sous-jacentes. Il s’exécute plus rapidement qu’un curseur insensible. Les curseurs insensibles pointent vers un instantané des données sous-jacentes, ce qui les rend plus lents que les curseurs sensibles.

Créer un curseur MySQL

Pour créer un curseur MySQL, vous devrez travailler avec le DECLARE, OPEN, FETCH, et CLOSE déclarations.

La déclaration de déclaration

Les DECLARE L’instruction peut déclarer des variables, des curseurs et des gestionnaires. Il y a une séquence de déclarations à respecter :

• Variables
• Curseurs
• Gestionnaires

Vous devez d’abord déclarer au moins une variable à utiliser plus tard avec le FETCH déclaration plus tard.

DECLARE <variable_name>  <variable_type>

Lors de la déclaration du ou des curseurs, vous devez joindre un SELECT déclaration. Tout valide SELECT déclaration fonctionnera. Vous devez également déclarer au moins un curseur.

DECLARE <cursor_name> CURSOR FOR <select_statement>

Vous devez également déclarer un NOT FOUND gestionnaire. Lorsque le curseur itère et atteint la dernière ligne, il lève une condition qui sera gérée avec le NOT FOUND gestionnaire. Vous pouvez également déclarer d’autres gestionnaires en fonction de vos besoins. Par exemple:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

La déclaration ouverte

Les OPEN L’instruction initialise le résultat de la DECLARE instruction du curseur.

OPEN <cursor_name>

La déclaration Fetch

Les FETCH L’instruction fonctionne comme un itérateur. Il récupère la ligne suivante à partir des lignes associées au SELECT dans la déclaration du curseur.

FETCH <cursor_name> INTO <variable_list>

Les <variable_list> est une ou plusieurs variables parmi les variables déclarées précédemment.

FETCH <cursor_name> INTO a, b, c

Si la ligne suivante existe, les variables la stockent ; sinon, une condition Aucune donnée avec SQLSTATE de ‘02000’ se produit. Vous pouvez utiliser le gestionnaire NOT FOUND pour gérer ce SQLSTATE.

La déclaration de fermeture

Cette instruction ferme le curseur ouvert dans l’instruction `OPEN`.

CLOSE <cursor_name>

Utiliser les curseurs MySQL

Prêt à commencer à utiliser les curseurs MySQL ? Tout d’abord, vous devez créer une base de données et une table. Dans cette démo, nous allons remplir un tableau avec les données de ce fichier CSV.

Nous allons créer un curseur qui effectue les opérations suivantes :

• Boucle à travers le baby-foot.
• Calcule le nombre moyen de buts marqués par une équipe à domicile qui a remporté un match à la mi-temps.

Voici à quoi devrait ressembler la procédure MySQL pour accomplir cela.

Procédure MySQL

DELIMITER $$ CREATE PROCEDURE cursordemo(INOUT average_goals FLOAT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE matches int DEFAULT(0);
DECLARE goals int DEFAULT(0);
DECLARE half_time_goals INT;
DECLARE team_cursor CURSOR FOR
SELECT
    HTHG
FROM
    epl.football
WHERE
    (FTR = "H");
    
DECLARE
    CONTINUE HANDLER FOR NOT FOUND
SET
    done = TRUE;

OPEN team_cursor;
teams_loop:
LOOP
    FETCH team_cursor INTO half_time_goals;
IF done THEN LEAVE teams_loop;
END IF;
SET
    goals = goals + half_time_goals;
SET
    matches = matches + 1;
END
LOOP
    teams_loop;
SET
    average_goals = goals / matches;
CLOSE team_cursor;

END $$ DELIMITER;

Une procédure dans MySQL est comme un conteneur contenant une séquence d’instructions. Les procédures sont écrites dans MySQL et stockées dans la base de données. Nous définissons le curseur à l’intérieur d’une procédure car les procédures sont réutilisables. L’exécution de la procédure SQL ci-dessus stockera la procédure dans la base de données. Nous pouvons appeler la procédure en utilisant son nom, comme indiqué ci-dessous :

SET @average_goals = 0.0; 
CALL cursordemo(@average_goals); 
SELECT @average_goals;

Le résultat de cette opération est :

1.080954670906067

Avertissements concernant les curseurs MySQL

Un bon examen de l’exemple montre qu’une requête SQL comme SELECT AVG(HTHG) FROM epl.football WHERE (FTR="H"); obtiendra le même résultat. Il serait préférable que vous n’utilisiez des curseurs que lorsque vous traitez une ligne à la fois. Les exemples sont les contrôles d’intégrité, les reconstructions d’index. Notez que chaque fois qu’un curseur récupère une ligne, il en résulte des allers-retours sur le réseau. Ainsi, cela peut finir par ralentir votre serveur MySQL en fonction de l’ampleur de l’opération.

Conclusion

Dans cet article, nous avons vu comment utiliser les curseurs MySQL et les boucles for, qui fonctionnent comme des boucles itératives en programmation générale. Nous avons également couvert la déclaration des variables, du curseur et du gestionnaire.

L’utilisation de curseurs peut être coûteuse en temps de calcul. Il serait préférable de ne les utiliser que lorsque MySQL ne fournit aucun autre moyen d’obtenir le même résultat en utilisant des requêtes standard ou des fonctions définies par l’utilisateur. Mais, si c’est le cas, ce sont des outils très puissants.