MySQL Stored Procedure Programming

Creating and Using Cursors

To handle a SELECT statement that returns more than one row, we must create and then manipulate a cursor. A cursor is an object that provides programmatic access to the result set returned by your SELECT statement. Use a cursor to iterate through the rows in the result set and take action for each row individually.

Currently, MySQL only allows us to fetch each row in the result set from first to last as determined by the SELECT statement. We cannot fetch from the last to first row, and cannot jump directly to a specific row in the result set.

Defining a Cursor

Define a cursor with the DECLARE statement, which has the following syntax:

    DECLARE cursor_name CURSOR FOR SELECT_statement;

As we mentioned in Chapter 3, cursor declarations must occur after all of our variable declarations. Declaring a cursor before declaring our variables generates error 1337, as shown in Example 5-3.

Example 5-3. Declaring a cursor before a variable generates a 1337 error

mysql> CREATE PROCEDURE bad_cursor(  )
BEGIN
        DECLARE c CURSOR FOR SELECT * from departments;
        DECLARE i INT;
END;

ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration

A cursor is always associated with a SELECT statement; Example 5-4 shows a simple cursor declaration that retrieves certain columns from the customers table.

Example 5-4. Simple cursor declaration

DECLARE cursor1 CURSOR FOR
        SELECT customer_name, contact_surname,contact_firstname
            FROM customers;

A cursor can reference ...