<

Care while using cursor in mysql

Published on
9,261 Points
3,261 Views
Last Modified:
Approved
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
Employee tableSecond is the employee communication table as shown below
EmployeeCommunicationInfo tableand 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 ;

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
logTableWhat 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 ;

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.

logTable
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!
0
Comment
Author:SANDY_SK
0 Comments

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Join & Write a Comment

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month