Cursors

Cursor
----------
To iterate through a result set returned by a SELECT statement. To handle a result set inside a stored procedure, you use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row accordingly.

4 stages:
------------
DECLARE
 - DECLARE cursor_name CURSOR FOR SELECT_statement;
 - The cursor declaration must be after any variable declaration. If you declare a cursor before variables declaration, MySQL will issue an error.
 - A cursor must always be associated with a SELECT statement.

OPEN
 - OPEN cursor_name;
 - The OPEN statement initializes the result set for the cursor, therefore, you must call the OPEN statement before fetching rows from the result set.

FETCH
 - FETCH cursor_name INTO variables list;
 - FETCH statement to retrieve the next row pointed by the cursor and move the cursor to the next row in the result set.
 - Can check to see if there is any row available before fetching it.

CLOSE
 - CLOSE cursor_name;
 - call the CLOSE statement to deactivate the cursor and release the memory associated with it as follows.

Note: When working with MySQL cursor, you must also declare a NOT FOUND handler to handle the situation when the cursor could not find any row. Because each time you call the FETCH statement, the cursor attempts to read the next row in the result set. When the cursor reaches the end of the result set, it will not be able to get the data, and a condition is raised. The handler is used to handle this condition.

 - DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Where finished is a variable to indicate that the cursor has reached the end of the result set.

Note:
 - That the handler declaration must appear after variable and cursor declaration inside the stored procedures.
 - Can use MySQL cursors in stored procedures, stored functions, and triggers.

References:
http://www.mysqltutorial.org/mysql-cursor/