MySQL cursor: Loop over a recordset

If you use MySQL stored procedures a lot, you probably needed to loop over a recordset to process a dataset. This can be done through a MySQL Cursor.

Declaration of a MySQL cursor

To declare a cursor, you must use the DECLARE CURSOR statement with the SELECT allowing to have the data set in the declaration zone of the procedure (so after the BEGIN and before the HANDLERS).
Let’s take the example of a cursor that loops over all the prices of an item base:

 DECLARE monCurseur CURSOR FOR SELECT prix_ht, taux_tva, prix_ttc FROM article_tarif;

Looping over MySQL cursor results

To loop on the results of a cursor, you will have to open the cursor and the loop, assign the fields of the line to variables, do the processing you want, and finally close the loop and the cursor:

DECLARE var_prix_ht DECIMAL(18,2);
DECLARE var_taux_tva DECIMAL(18,2);
DECLARE var_prix_ttc DECIMAL(18,2);
...

OPEN monCurseur;
tarif_loop: LOOP
  FETCH monCurseur INTO var_prix_ht, var_taux_tva, var_prix_ttc;
  ...
END LOOP;
CLOSE monCurseur;

Once your variables are fed by the FETCH, you can do the various appropriate processing. If you ever have null values in your variables after a FETCH and you don’t understand why, you can take a look at this site article .

Set a stop condition at the loop

As it stands, with the code above you will get a runtime error when it goes through the cursor and gets to the end of the recordset. To avoid this, and also to have an exit condition at the loop (and avoid a possible infinite loop), you will be able to define a dedicated HANDLER.

DECLARE done INT DEFAULT 0;
...
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
...
/* dans la boucle */
IF done = 1 THEN
LEAVE tarif_loop;
END IF;

Another way to exit also, you can set your variables to NULL at the end of the turn, and test if a variable which should not be null is null in the loop, and do the LEAVE at this moment

/* dans la boucle */
IF var_prix_ht IS NULL THEN
LEAVE tarif_loop;
END IF;
...
SET var_prix_ht = NULL;

In summary, the complete code for our example would be as follows:

DECLARE var_prix_ht DECIMAL(18,2);
DECLARE var_taux_tva DECIMAL(18,2);
DECLARE var_prix_ttc DECIMAL(18,2);
DECLARE done INT DEFAULT 0;
DECLARE monCurseur CURSOR FOR
SELECT prix_ht, taux_tva, prix_ttc FROM article_tarif;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN monCurseur;
tarif_loop: LOOP
FETCH monCurseur INTO var_prix_ht, var_taux_tva, var_prix_ttc;

IF done = 1 THEN
LEAVE tarif_loop;
END IF;

/* mettre ici le traitement à effectuer */

END LOOP;
CLOSE monCurseur;