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

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

/*

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