Understanding MySQL Cursor

In this tutorial, you will learn how to use MySQL cursor in stored procedures to iterate through a result set returned by a SELECT statement.

What is Cursor?

Cursor in MySQL is almost same as cursor in other database. As per Wikipedia following is cursor definition. "In computer science and technology, a database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator."

Introduction to MySQL cursor

To handle a result set inside a stored procedure, you use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row accordingly. MySQL cursor is read only, non-scrollable and asensitive. Read only: you cannot update data in the underlying table through the cursor. Non-scrollable: you can only fetch rows in the order determined by the SELECT statement. You cannot fetch rows in the reversed order. In addition, you cannot skip rows or jump to a specific row in the result set. Asensitive: there are two kinds of cursors: asensitive cursor and insensitive cursor. An asensitive cursor points to the actual data, whereas an insensitive cursor uses a temporary copy of the data. An asensitive cursor performs faster than an insensitive cursor because it does not have to make a temporary copy of data. However, any change that made to the data from other connections will affect the data that is being used by an asensitive cursor, therefore it is safer if you don’t update the data that is being used by an asensitive cursor. MySQL cursor is asensitive. You can use MySQL cursors in stored procedures, stored functions and triggers.

Advantage of using MySQL Cursor

Following are some of the advantage of using MySQL cursor: - No need to write business logic after fetching data from your code logic. - Gives you better flexibility for operating even on the single column of the row. Manipulation become always easy. - Saves you from the simplex join structure. - Easy to maintain the business logic of your application at one place.

Disadvantage of using MySQL Cursor

Following are some of the disadvantages of using cursor in MySQL: - Slow down your stored procedure or function performance in cause of large record set in cursor. - Debugging of your business logic become tough. - Hard to manage. - Need extra care in cause of the implementation of replication using binary log. - Need to consider locking of the database.

Working with MySQL cursor

First, you have to declare a cursor by using the DECLARE statement:

DECLARE cursor_name CURSOR FOR SELECT_statement;

The cursor declaration must be after any variable declaration. If you declare a cursor before variables declaration, MySQL will issue an error. A cursor must always be associated with a SELECT statement. Next, you open the cursor by using the OPEN statement. The OPEN statement initializes the result set for the cursor therefore you must call the OPEN statement before fetching rows from the result set.

OPEN cursor_name;

Then, you use the FETCH statement to retrieve the next row pointed by the cursor and move the cursor to the next row in the result set.

FETCH cursor_name INTO variables list;

After that, you can check to see if there is any row available before fetching it. Finally, you call the CLOSE statement to deactivate the cursor and release the memory associated with it as follows:

CLOSE cursor_name;

When the cursor is no longer used, you should close it. When working with MySQL cursor, you must also declare a NOT FOUND handler to handle the situation when the cursor could not find any row. Because each time you call the FETCH statement, the cursor attempts to read the next row in the result set. When the cursor reaches the end of the result set, it will not be able to get the data, and a condition is raised. The handler is used to handle this condition. To declare a NOT FOUND handler, you use the following syntax:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Where finished is a variable to indicate that the cursor has reached the end of the result set. Notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures. The following diagram illustrates how MySQL cursor works.

Example1

DELIMITER //
USE `test`//
DROP PROCEDURE IF EXISTS `test`//
CREATE DEFINER=`cpses_shssGqSq0J`@`localhost` PROCEDURE `test`()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE i INTEGER;
DECLARE n TEXT;
DECLARE curs1 CURSOR FOR SELECT `id`, `name` FROM my_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN curs1;
read_loop: LOOP
FETCH curs1 INTO i,n;
IF done THEN
LEAVE read_loop;
END IF;
SELECT i,n;
END LOOP;
CLOSE curs1;
END//
DELIMITER ;

Example2

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;