Sometimes we get situations to perform a set of tasks for a result set. There are 2 ways to do it.

1. We can fetch the result set in server side(like php) and loop through the data in server side and execute the stored procedure one by one.

2. We can use cursor to do the task in the stored procedure in a single call.

To loop through an array usually we need length of the array. As there is no concept of array in stored procedure we need to handle the execution in a different way using CURSOR. Lets see how CURSOR works with syntax.

First we need to declare a CURSOR  for a result set.
DECLARE cur CURSOR FOR
    SELECT `firstName`
    FROM `users`;

We need to declare a variable with default 0 to determine weather to continue the loop or exit the loop.

DECLARE done INT DEFAULT 0;

Set this variable to 1 if no record found.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Now start the loop

OPEN cur;

read_loop: LOOP
 
    FETCH cur INTO userID;  //Fetch one record from CURSOR and set to some variable(If not found then done will be set to 1 by continue handler)
    IF done THEN
        LEAVE read_loop;  //If done set to 1 then exit the loop else continue
    END IF;
 
    /* Do your work */
 
END LOOP;
CLOSE cur; //Closing the cursor

Using Cursor is a better way to loop through a result set as it needs only one database transaction.