A Simple Example of a MySQL Stored Procedure that uses a cursor

(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!')
;
This entry was posted in mysql and tagged , . Bookmark the permalink.
  • http://homedecoration.linkablez.info/2009/03/02/kevin-bedell-on-internet-tech-a-simple-example-of-a-mysql-stored-bb/ Topics about Home Decoration » Kevin Bedell on Internet Tech – A Simple Example of a MySQL Stored b…/b

    [...] Kevin created an interesting post today on Kevin Bedell on Internet Tech – A Simple Example of a MySQL Stored b…/bHere’s a short outlineProcedure Name : usp_cursor_example * Database/Schema : foo * * bDescription/b: * An example of a MySQL stored procedure that uses a cursor * * * Tables Impacted : * foo.friend_status – read-only * DDL? b…./b Anand Rajaram, bArchitect/b, jPeople “ Kevin is smart, talented, and highly motivated. He keeps up with the latest trends in technology and is always looking to learn more. I’d recommend him to anyone looking to add some technical depth to their existing team or organization b…/b [...]

    Current score: 0
  • http://homedecoration.linkablez.info/2009/03/02/kevin-bedell-on-internet-tech-a-simple-example-of-a-mysql-stored-bb-2/ Topics about Home Decoration » Kevin Bedell on Internet Tech – A Simple Example of a MySQL Stored b…/b

    [...] Kevin put an intriguing blog post on Kevin Bedell on Internet Tech – A Simple Example of a MySQL Stored b…/bHere’s a quick excerptProcedure Name : usp_cursor_example * Database/Schema : foo * * bDescription/b: * An example of a MySQL stored procedure that uses a cursor * * * Tables Impacted : * foo.friend_status – read-only * DDL? b…./b Anand Rajaram, bArchitect/b, jPeople “ Kevin is smart, talented, and highly motivated. He keeps up with the latest trends in technology and is always looking to learn more. I’d recommend him to anyone looking to add some technical depth to their existing team or organization b…/b [...]

    Current score: 0
  • http://www.youtube.com/watch?v=XZYb4m1nY4g cayseruhrichcyg

    IndyCar Series Driver Danica Patrick talks about modeling for the Sports Illustrated Swimsuit Edition.
    Danica Patrick 20Q Playboy Interview or just follow the link http://www.youtube.com/watch?v=XZYb4m1nY4g
    You MUST SEE it, this girl really HOT!!!

    Current score: 0
  • Peter Stone

    Just want to say an HUGE THANKS! for the tutorial on using cursors in MySQL…. In case you (or anyone else) reads this, I suspect that I’m not alone in “migrating” from MS-SQL. I suspect that this would not be everyone’s experience, but I’m surprised (and relieved) at how similar things are between the 2 “dialects.” … Oh yes, one “reservation” about the tutorial – more a huh? – the Selects that you use(d) to mark progress did not work as I expected; maybe, there’s some option that can/should be altered from the default.

    Current score: 0
  • http://www.asktoby.com Toby Newman

    Excellent, thank you. This was a real help.

    Current score: 0
  • Moon Wolf

    Thanks for code. It’s very usefull.

    Current score: 0
  • http://bohblog.blogspot.com beauraines

    This was really helpful. I like your clear documentation and your proc can be used as a template for my next procs.

    Thanks for walking through it so clearly.

    btw, why did you only explicitly close the cursor when you LEAVE the loop but not at the end? Is it just because cursors automagically close and its not “necessary”?

    Current score: 0
  • http://www.kbedell.com/2009/03/02/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor/ Subodhqn

    It’s really helpfull code for me..

    thanks.

    Current score: 0
  • http://www.kbedell.com/2009/03/02/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor/ Subodhqn

    It’s Really Helpfull Code for me

    Thanks
    Subodhan

    Current score: 0
  • Adelar da Silva Queiróz

    Very usefull your code. Thank’s. :)

    Current score: 0
  • gggg

    :) :)

    Current score: 0
  • No

    you forgot $$ after the last END ine the exemple

    Current score: 0
  • Bren

    good article, just what i needed. Thanks!

    Current score: 0
  • Pradeep

    This Article is very good to practice,it works very good
    Thx a lot to the author

    Current score: 0
  • Solwinapp

    Thanks
    Nice Article..
    i got good help to learn SP with cursor

    Current score: 0
  • Jiykoth

    Thanks for sharing this; was extremely helpful!

    Current score: 0
  • Nmudiraj003

    it helps me a lot.Thanks

    Current score: 0
  • Alma

    not a long time ago post. helped me a lot.. thanks!

    Current score: 0
  • http://jnrbsn.com Jonathan Robson

    Here’s a couple more good examples of using SQL as a full-fledged scripting language: http://jnrbsn.com/2011/08/using-sql-as-a-full-fledged-procedural-scripting-language

    Current score: 0
  • http://morgenyu.wordpress.com/2011/08/26/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor-ref/ A Simple Example of a MySQL Stored Procedure that uses a cursor [ref] « Morgen Free's Blog
  • Benito Cámela.

    Me gustó y fundamentalmente me sirvió mucho.
    Felicitaciones y gracias!

    Current score: 0
  • Sudhakar Reddy

    Good one. Very much helpful for me

    Current score: 0
  • Vicky Minu

    create procedue Get_Search_Results(@ghplid NVARCHAR(500)=NULL,@name Nvarchar(500)=NULL,@hospital Nvarchar(500)=NULL,@claimid NVARCHAR(500)=NULL)as
    begin
    declare @query varchar(4000)=’select * from claim c join enroll e on c.policyno=e.policyno where 1=1 ‘
      if @ghplid is not null
        begin
          set @query=@query+’and PatientUHID=”’+@ghplid+””
        end
      IF @name IS NOT NULL
      BEGIN
        SET @query=@query+’ AND PatientName=”’+@name+””
      END
      IF @hospital IS NOT NULL
      BEGIN
        SET @query=@query+’ AND Hospital=”’+@hospital+””
      END
      IF @claimid IS NOT NULL
      BEGIN
        SET @query=@query+’ AND Claimid=”’+@claimid+””
      END
      –PRINT @QUERY
      EXEC (@query)
    end

    can some one help me how to wright this procedure in mysql

    Current score: 0
  • Alexander Arendar

    Thanks, very nice and elaborated article.

    Current score: 0
  • Kaymary

    thanks very much!!!

    Current score: 0
  • Mm Cc

    really nice

    Current score: 0
  • Miguel Angel

    Gracias este tutorial me sirvio mucho, quisiera saber si no tienen uno para particionar las tablas. atte matrix_alfa@hotmail.com

    Current score: 0
  • Xcvxz

    vcvcxxcvx

    Current score: 0
  • M@X

    Add $$ to last end (row before DELEIMITER ; )

    Current score: 0
  • Adiat Kiya

    thans man, 

    Current score: 0
  • Mahagaonkarabhi

    Thanks for the article….

    Current score: 0
  • Miguel Cubias Caceres

    Thanks! i really appreciate your effort for making this tutorial :D

    Current score: 0
  • Anonymous

    Really good article! I had not idea of working with stored procedures and cursors within a SQL script and your detailled explanation has made learning it really easy.

    Nice work and thanks!

    Current score: 0
  • http://kirtu.wordpress.com/2013/01/28/my-mysql-cursor-needs-help/ My (MySQL) cursor needs help! | Kirt Undercoffer's Tech Notes Blog

    [...] adapted a MySQL cursor example from Kevin Bedell’s MySQL cursor example. He has a cursor running in a stored procedure with the cursor results inserted into an output [...]

    Current score: 0
  • Felipe

    To me the loop section is returning only one row. Any one knows what is going on? Thanks for the very explanatory article.

    – Multiple records: call foo.usp_cursor_example(‘John’); //is returning only one record

    Current score: 0
  • james

    nice blog!

    stringpool.com

    Current score: 0
  • Guest

    Can some one gelp me how to this procedure in mysql?

    DELIMITER $$
    DROP PROCEDURE IF EXISTS EXEMP_CURSOR;
    $$
    DELIMITER ;

    DELIMITER $$
    CREATE PROCEDURE EXEMP_CURSOR (
    IN SQL_TEST TEXT)
    BEGIN
    DECLARE filtro_piloto VARCHAR(1000);

    DECLARE no_more_rows BOOLEAN;
    DECLARE loop_cntr INT DEFAULT 0;
    DECLARE num_rows INT DEFAULT 0;

    DECLARE friends_cur CURSOR FOR SQL_TEST

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

    IF (piloto = ’0′) THEN
    SET filtro_piloto = ‘ ‘ ;
    ELSE
    SET filtro_piloto = ‘ AND CO.CODIGO_PILOTO = ‘||””||piloto||””;
    END IF ;

    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;

    END $$
    DELIMITER ;

    Thanks

    Current score: 0
  • Alessandro Yamasaki

    Can some one gelp me how to this procedure in mysql?

    DELIMITER $$
    CREATE PROCEDURE EXEMP_CURSOR (
    IN SQL_TEST TEXT)
    BEGIN
    DECLARE no_more_rows BOOLEAN;
    DECLARE loop_cntr INT DEFAULT 0;
    DECLARE num_rows INT DEFAULT 0;

    DECLARE friends_cur CURSOR FOR SQL_TEST;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

    OPEN friends_cur;
    SELECT FOUND_ROWS() INTO num_rows;

    the_loop: LOOP

    FETCH friends_cur

    INTO name_val, status_update_val;

    IF no_more_rows THEN
    CLOSE friends_cur;
    LEAVE the_loop;
    END IF;

    select name_val, status_update_val;

    — count the number of times looped
    SET loop_cntr = loop_cntr + 1;
    END LOOP the_loop;

    END $$
    DELIMITER ;

    Error Code: 1064. You have an error in your SQL syntax IN ”
    DECLARE friends_cur CURSOR FOR SQL_TEST;”

    Thanks

    Current score: 0
  • Arindam Biswas

    Either of variable loop_cont or num_rows, I get the same output null.
    mysql>CALL usp_cursor_example(‘John’);
    mysql> SELECT @name_val, @status_update_val;
    output > NULL. Please clarify.

    Current score: 0
  • Arindam Biswas

    Reference to my previous statement regarding MySQL cursor stored procedure (CALL usp_cursor_example(‘John’); kindly note that I make little bit mistake while i write codes.I rectify my mistake. Now, everything is OK. But, still now one point , I would like to clarify.
    Even neither use of variables name_val nor loop_cont, I am getting the same output.

    Current score: 0