All about Programming

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 ;

CALL mysql_cursor_example()