What is a Cursor?
First off, what is a cursor?
A cursor is a mechanism that allows us to step through the rows returned by a SQL statement.
So far, we have been learning to use SELECT statements to retrieve information from our tables. These statements return all the rows that match the stated criteria. However, there is no way for us to step through the rows one at a time.
If we want to do that, we have to use cursors.
The syntax for declaring a cursor is:
DECLARE cursor_name CURSOR FOR
SELECT statement
This cursor declaration must be done after any variable declaration.
After declaring the cursor, we also need to declare a handler that defines what the cursor should do when it reaches the last row of the results returned by the SELECT statement. Normally, we want the cursor to set a variable to a certain value. For instance, we may want it to set a variable called v_done to 1.
The syntax is:
...