Using a mysql cursor in a stored procedure

By | July 3, 2012

Example using a mysql cursor in a stored procedure.

Mysql Cursor example

DELIMITER $$
 
DROP PROCEDURE IF EXISTS  mysql_cursor_example $$
CREATE PROCEDURE mysql_cursor_example ( IN in_name VARCHAR(255) )
BEGIN
  -- First we declare all the variables we will need
  DECLARE l_name    VARCHAR(255);
  -- flag which will be set to true, when cursor reaches end of table
  DECLARE exit_loop BOOLEAN;         
 
  -- Declare the sql for the cursor
  DECLARE example_cursor CURSOR FOR
    SELECT name status_update
    FROM employees
    WHERE name = name_in;
 
  -- Let mysql set exit_loop to true, if there are no more rows to iterate
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
 
  -- open the cursor
  OPEN example_cursor;
 
  -- marks the beginning of the loop
  example_loop: LOOP
 
    -- read the name from next row into the variable l_name
    FETCH  example_cursor INTO   l_name;
 
    -- check if the exit_loop flag has been set by mysql, 
    -- if it has been set we close the cursor and exit 
    -- the loop
    IF exit_loop THEN
        CLOSE example_cursor;
        LEAVE example_loop;
    END IF;
 
  END LOOP example_loop;
END $$
 
DELIMITER ;

Calling it

To call a stored procedure you only have to exec “call procedurename”:

CALL mysql_cursor_example()

More info about the topic

Here are some links to get more information about mysql stored procedures and cursors in MySql.

MySql manual, cursors
MySql manual, stored procedures

[amazon_carousel widget_type="SearchAndAdd" width="600" height="200" title="Books" market_place="" shuffle_products="False" show_border="False" keywords="MySQL" browse_node="" search_index="Books" /]

 

Leave a Reply