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
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
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
/* Do your work */
CLOSE cur; //Closing the cursor
Using Cursor is a better way to loop through a result set as it needs only one database transaction.