(Save this to Del.icio.us!)

What is a cursor and when should you use one?

A ‘cursor’ in MySQL is essentially just the result set that’s returned from a query. Using a cursor allows you to iterate, or step through, the results of a query and perform certain operations on each row that’s returned.

Think of them like holders for any data you  might process in a loop. As you’ll see below, the example here runs a query then loops through the results.

Cursors have some drawbacks and you don’t want to use them everywhere. An appropriate use for a cursor might be when you need to step through the results of a query and then perform operations on multiple tables for each row.

Another candidate for a cursor is where some steps of the processing are optional and only need to be performed for certain rows. The cursor allows you to iterate through the result set and then perform the additional processing only on the rows that require it.

When should cursors be avoided?

Cursors should be avoided generally whenever you can write a SQL statement that processes all rows in a query at once — and you’ll be surprised at how often this is. If you try, many procedures with cursors in them can be refactored to eliminate the cursor by using a bit more complex SQL.

This is an advantage usually for a couple reasons. First, by processing all rows at once instead of looping through them one at a time, it’s generally faster. Second, cursors can make some issues more complex to debug

MySQL Cursor Sample Code

Here’s an example of how to create a MySQL stored procedure that uses a cursor.

I ‘over-commented’ the procedure a bit (if there is such a thing!), but I wanted the code to be relatively self-explanatory for people who just cut/paste it.

The SQL required to setup the tables and data follows the example. Everything runs and is tested. It requires MySQL 5.0 or above since before that there was no support for stored procedures.

First, here’s the sample code. I’ll follow after with some explanation.

/*
 *      Procedure Name  :  usp_cursor_example
 *      Database/Schema :  foo
 *
 *      Description:
 *          An example of a MySQL stored procedure that uses a cursor
 *
 *
 *      Tables Impacted :
 *         foo.friend_status - read-only
 *         DDL? - None
 *         DML? - Only Selects
 *
 *      Params:
 *         name_in - the name of the friend to search for
 *
 *      Revision History:
 *
 *         Date:          Id:        Comment:
 *         2009/03/01     kbedell    Original
 *
 *    Copyright (c) 2009 Kevin Bedell
 *    Can be resused under terms of the 'MIT license'.
 *
 *    To test:
 *      - Multiple records: call foo.usp_cursor_example('John');
 *      - One record:       call foo.usp_cursor_example('Julie');
 *      - Zero records:     call foo.usp_cursor_example('Waldo');
 *
 */
DROP PROCEDURE IF EXISTS  `foo`.`usp_cursor_example`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`.`usp_cursor_example`(
  IN name_in VARCHAR(255)
)
READS SQL DATA
BEGIN

  /*
    All 'DECLARE' statements must come first
  */

  -- Declare '_val' variables to read in each record from the cursor
  DECLARE name_val VARCHAR(255);
  DECLARE status_update_val VARCHAR(255);

  -- Declare variables used just for cursor and loop control
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;

  -- Declare the cursor
  DECLARE friends_cur CURSOR FOR
    SELECT
        name
      , status_update
    FROM foo.friend_status
    WHERE name = name_in;

  -- Declare 'handlers' for exceptions
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  /*
    Now the programming logic
  */

  -- 'open' the cursor and capture the number of rows returned
  -- (the 'select' gets invoked when the cursor is 'opened')
  OPEN friends_cur;
  select FOUND_ROWS() into num_rows;

  the_loop: LOOP

    FETCH  friends_cur
    INTO   name_val
    ,      status_update_val;

    -- break out of the loop if
      -- 1) there were no records, or
      -- 2) we've processed them all
    IF no_more_rows THEN
        CLOSE friends_cur;
        LEAVE the_loop;
    END IF;

    -- the equivalent of a 'print statement' in a stored procedure
    -- it simply displays output for each loop
    select name_val, status_update_val;

    -- count the number of times looped
    SET loop_cntr = loop_cntr + 1;

  END LOOP the_loop;

  -- 'print' the output so we can see they are the same
  select num_rows, loop_cntr;

END
DELIMITER ;

Discussion and Explanation of the MySQL Cursor Example Code

The ‘Declaration’ section

Before any actual programming logic starts, you need to first list all ‘declarations’. This is a requirement of MySQL.

Here’s the declaration section:

  /*
    All 'DECLARE' statements must come first
  */

  -- Declare '_val' variables to read in each record from the cursor
  DECLARE name_val VARCHAR(255);
  DECLARE status_update_val VARCHAR(255);

  -- Declare variables used just for cursor and loop control
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;

  -- Declare the cursor
  DECLARE friends_cur CURSOR FOR
    SELECT
        name
      , status_update
    FROM foo.friend_status
    WHERE name = name_in;

  -- Declare 'handlers' for exceptions
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

There are 4 sections to the declaration section:

• Declaration of one variable to hold the ‘value’ of each column in the result of the query.

Here, I recommend simply naming each variable by adding ‘_val’ to the end of the actual returned column name. Also, make sure the types match!

• Declaration of variables that are used for counting rows and controlling looping.

Generally, every stored procedure that uses a cursor can always simply have the same set.

In this example we have three variables — a boolean that gets set if we try to read from the cursor when no data is left, and two counters for the number of rows processed (we have two here because the example demonstrates two different methods of counting rows processed — use either of them).

• Declaration of the actual query that will generate the result set to be held by the cursor

The query is not actually invoked until later in the procedure where the cursor is ‘opened’. Also, in this example you can see that the cursor uses a variable in its ‘where clause’ that is passed in as an argument to the procedure.

• Declaration of ‘handlers’ to control execution. These are usually always the same.

This is a standard declaration that sets up processing for when the cursor contains no (or no more) rows.

Opening the cursor and finding the number of rows returned

The next step is to ‘open’ the cursor. This is where the actual query in the cursor is run - and one of the places where cursor programming can get tricky.

  -- 'open' the cursor and capture the number of rows returned
  -- (the 'select' gets invoked when the cursor is 'opened')
  OPEN friends_cur;
  select FOUND_ROWS() into num_rows;

Notice that we select FOUND_ROWS() into our num_rows variable directly after we open the cursor. The FOUND_ROWS() ‘information function’ contains the number of rows found in the last select statement run.

If the query fails or errors for some reason, the ‘open’ call will fail — and it can fail ’silently’. This is one of the issues with cursors — you need to make sure that your queries work and are solid.

You also need to make sure that they are tolerant to data changing over time. Make sure that if the underlying data in the table changes over time, your query will still work.

Looping through the cursor result set

There are multiple methods for looping in MySQL — I use this method and recommend that you just cut/paste this code for every cursor you write. This method works and you won’t have to think about it.

  the_loop: LOOP

    FETCH  friends_cur
    INTO   name_val
    ,      status_update_val;

    -- break out of the loop if
      -- 1) there were no records, or
      -- 2) we've processed them all
    IF no_more_rows THEN
        CLOSE friends_cur;
        LEAVE the_loop;
    END IF;

    -- the equivalent of a 'print statement' in a stored procedure
    -- it simply displays output for each loop
    select name_val, status_update_val;

    -- count the number of times looped
    SET loop_cntr = loop_cntr + 1;

  END LOOP the_loop;

There are a couple things to point out here.

First, note that the no_more_rows variable gets set automatically when you ‘FETCH’ from the cursor. For this reason you should always test for that condition immediately after you try to read a row.

Note that we have a counter in the loop here as well. This is just another way to count the rows processed. Use what works for you.

Conclusion

In this post, we gave some sample code that can be used/reused to create MySQL Stored Procedures containing a cursor. Best of luck with the code and feel free to post comments below if you find any problems or if you have any suggestions!

Setup code for the sample MySQL Stored Procedure with a Cursor

Here is the setup SQL I used to create the tables/data you need to test the stored procedure and cursor.

/*
 *      Procedure/File Name  :  ex1_setup.sql
 *      Database/Schema      :  foo
 *
 *      Description:
 *          Setup file to support simple cursor example
 *
 *      Tables Impacted :
 *         foo.friend_status - drop/re-create table and populate.
 *
 *      Params:
 *         None.
 *
 *      Revision History:
 *
 *         Date:          Id:        Comment:
 *         2009/03/01     kbedell    Original
 *
 *    Copyright (c) 2009 Kevin Bedell
 *    Can be resused under terms of the 'MIT license'.
 *
 *    To test:
 *      - Run script then: select * from foo.friend_status;
 *
 */
drop table if exists foo.friend_status; 

CREATE TABLE IF NOT EXISTS `foo`.`friend_status` (
    `id`            INTEGER(10) unsigned NOT NULL auto_increment,
    `name`          VARCHAR(255) NOT NULL,
    `status_update` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
);

insert into foo.friend_status
    (name, status_update)
  values
      ('John',  'Woke up. Guiness for Brkfst.')
    , ('Fred',  'is thinking about joining the circus')
    , ('Erin',  "Getting ready for a job interview")
    , ('Amy',   'at work and dreaming of kittens')
    , ('John',  'Watching Scooby Doo reruns. Guiness for Lunch.')
    , ('Amy',   'dreaming of fuzzy slippers and wedding dresses')
    , ('Julie', 'is hating working two jobs')
    , ('John',  'Out of the shower finally. Guiness for Dinner.')
    , ('Erin',  "if I don't get this job, I'll be asking 'Paper or Plastic?'")
    , ('Amy',   'dreaming of Meeting Mr. Right!')
    , ('Erin',  'Nailed the job interview -- calling John to celebrate!')
    , ('Amy',   'John called -- meeting him at the pub!')
    , ('John',  'Heading out to meet friends for some Guiness!')
;

Related Content

Subscribe to the post comments feeds or Leave a trackback