Loops in stored procedures are quite useful and sometimes inevitable for achieving certain tasks in MySQL database. Instead of retrieving all records from the database and looping through all the data for subsequent manipulations using like PHP, which can be more expensive compared to running a loop on the database side in a stored procedure, we can put this job on our DB and handle it all at once in a single procedure.
For this looping and performing subsequent operations we will use cursors and loops in our procedures.
Part 1 – Declare variables, cursor and continue handler for not found
Before using our cursor and loop we need to DECLARE these after the BEGIN statement.
DECLARE no_more_rows BOOLEAN;
DECLARE myVar INT;
DECLARE curr CURSOR FOR SELECT myTableVar FROM myTable;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
Here no_more_rows is a boolean variable to be set when our loop reaches the end of data.
myVar will be the variable to be populated each time we read a row in our loop.
curr is the cursor. As you can see it is defined for a query (SELECT myTableVar FROM myTable). So, our cursor uses this query to fetch data.
In the last row we declare a continue handler. This is where we set no_more_rows to true and use it to terminate the loop (See the if statement in the loop below).
Part 2 – Open the cursor and start the loop
FETCH curr INTO myVar;
IF no_more_rows THEN /*check if end of data*/
insert into someothertable (otherVar) values (myVar); /*if not end of data, perform this query*/
END LOOP my_loop;
It is pretty straight forward.
We open the cursor, so OPEN is a command you need here,
We FETCH curr into myVar meaning read the current variable from myTable and put it in myVar,
Check if it is the end of records (rows),
If so, close the cursor and leave the loop,
If not, perform the task (run the query),
And go back to the beginning of the loop and do it all over.
Now you enclose the code in part 1 and part 2 in a BEGIN-END block and you have yourself a mini procedure utilizing MySQL cursors and loops.
Hope this helps.
Let’s leave our children a clear world,