Care while using cursor in mysql

This is one of the most important aspects that i got to know regarding mysql cursors. Well when I encountered this problem, it took me really long to figure out what was going wrong, hence I thought I will jot out my experience here so that it may help somebody else in solving a similar issue faster. This article will also help you if you are looking to write a basic cursor in mysql.

 I agree cursors are slow and should be avoided but if there comes a case where you have to use, please be aware of the following.

Please Note : All what I have written and executed here in this article are done on MySQL version 5.0.27 and the default setting that comes when you install. People are welcome to comment and suggest if any of these can be overcome by any configuration or settings. Also if any of this has changed in the newer versions, please feel free to comment.

I will be using 3 tables for my examples.
First is the employee table as shown below
Second is the employee communication table as shown below
and the third is a simple log table with one column called comment

Below is a typical code of a stored procedure with a cursor to loop on the list of employees and check if it has a phone number, before and after this check I am inserting a comment into a logTable (I agree the stored procedure is not doing much here, it is just as an example to explain what I want to convey).

DELIMITER $$
                      CREATE PROCEDURE processEmployees()
                      BEGIN
                        DECLARE done INT DEFAULT FALSE;
                        DECLARE nameVal VARCHAR(50);
                        DECLARE phoneNumberVal VARCHAR(50);
                        DECLARE idVal INT;
                        DECLARE cur1 CURSOR FOR SELECT id,firstName FROM employee;
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
                      
                        -- opening the cursor
                        OPEN cur1;
                      
                        loopEmp: LOOP
                          FETCH cur1 INTO idVal ,nameVal  ;  -- fetching the values into the local variables
                          
                          -- If the value of done  is set to true then leave the loop
                          IF done THEN
                            LEAVE loopEmp;
                          END IF;
                          
                          INSERT INTO logTable SELECT CONCAT('For id=',idVal ,' , value of done before inner select = ',done) ;
                          
                          
                          SELECT PhoneNumber INTO phoneNumberVal FROM employeecommunicationinfo  WHERE employeeId = idVal ;
                          
                          
                          INSERT INTO logTable SELECT CONCAT('For id=',idVal ,' , value of done after inner select = ',done) ;
                         
                        END LOOP;
                      
                        CLOSE cur1;
                      END$$
                      
                      DELIMITER ;

Select all Open in new window

One executing this stored procedure, you expect to see 6 rows in the log table, 2 each for the 3 rows that are available in employee table.

But this is where you are in for a supprise, see below the content of the log table on execution of the stored procedure, it contains only 4 rows
What is happening here is that at line 22 in the above code, since the select command returns no records from employeecommunicationinfo for employee with id =2 , it sets the CONTINUE HANDLER (done) to 1

That condition (ie done) will actually make the procedure exit the loop and not going to the third row which is present in the employee table. This is not only when it is a select statement, it happens even if you are calling a function which has a select and does not return any rows.

So one way to solve this problem will be that before executing any select or any other function call inside a cursor loop, assign the original value of the continue handler to a temp variable and then assign it back to the actual variable after the query or function call, as shown below

DELIMITER $$
                      CREATE PROCEDURE processEmployees()
                      BEGIN
                        DECLARE done INT DEFAULT FALSE;
                        DECLARE actualDone INT DEFAULT FALSE;
                        DECLARE nameVal VARCHAR(50);
                        DECLARE phoneNumberVal VARCHAR(50);
                        DECLARE idVal INT;
                        DECLARE cur1 CURSOR FOR SELECT id,firstName FROM employee;
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
                      
                        -- opening the cursor
                        OPEN cur1;
                      
                        loopEmp: LOOP
                          FETCH cur1 INTO idVal ,nameVal  ;  -- fetching the values into the local variables
                          
                          -- If the value of done  is set to true then leave the loop
                          IF done THEN
                            LEAVE loopEmp;
                          END IF;
                          
                          INSERT INTO logTable SELECT CONCAT('For id=',idVal ,' , value of done before inner select = ',done) ;
                          
                          -- Assigning the original value of done to a temp variable
                          set actualDone = done;
                          
                          SELECT PhoneNumber INTO phoneNumberVal FROM employeecommunicationinfo  WHERE employeeId = idVal ;
                          
                          -- Assigning back the value of done
                          set done = actualDone;
                          
                          INSERT INTO logTable SELECT CONCAT('For id=',idVal ,' , value of done after inner select = ',done) ;
                         
                        END LOOP;
                      
                        CLOSE cur1;
                      END$$
                      
                      DELIMITER ;

Select all Open in new window

Here I have declared another variable called actualDone and I am setting the original value of the variable done before the select and soon after the select statement, I am putting the original value back into done. Now if you execute the stored procedure, you will see that there are 6 rows in the log file.


Another way is if the selects are in a different function call, you could define a separate continue handler inside the respective functions to avoid the conflict.

So when ever you have any select or any other function call within the loop of a cursor, make sure you are handling it properly.

Thanks!