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.

5.3.1. 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

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

A cursor can reference stored program variables within the WHERE clause or (less frequently) the column list. In Example 5-5, the cursor includes a reference to a stored procedure parameter, both in the WHERE clause and in the SELECT list. When the cursor is opened, it will use the value of the parameter variable to determine which rows to return.

Example 5-5. Cursor definition including a stored procedure variable

5.3.2. Cursor Statements

The MySQL stored program language supports three statements for performing operations on cursors :

OPEN

Initializes the result set for the cursor. We must open a cursor before fetching any rows from that cursor. The syntax for the OPEN statement is very simple:

OPEN cursor_name;

FETCH

Retrieves the next row from the cursor and moves the cursor "pointer" to the following row in the result set. It has the following syntax:

FETCH cursor_name INTO variable list;

The variable list must contain one variable of a compatible data type for each column returned by the SELECT statement contained in the cursor declaration. We'll discuss FETCH in more detail later in this chapter.

CLOSE

Deactivates the cursor and releases the memory associated with that cursor. The syntax for this statement is:

CLOSE cursor_name ;

We should close a cursor when we have finished fetching from it, or when we need to open that cursor again after changing a variable that affects the cursor's result set.

In the following sections, we will see many examples of these statements in action.

5.3.3. Fetching a Single Row from a Cursor

This is the most basic use of a cursor: we open a cursor, fetch a single row, and then close the result set, as shown in Example 5-6 (opening the cursor defined in Example 5-4). This is logically equivalent to a simple SELECT with an INTO clause.

Example 5-6. Fetching a single row from a cursor

5.3.4. Fetching an Entire Result Set

The most common way that cursors are processed is to fetch each row identified by the cursor's SELECT statement, perform one or more operations on the data retrieved, and then close the cursor after the last row has been retrieved.

Example 5-7 shows how we can declare and open a cursor, then fetch rows from the cursor in a loop, and finally close the cursor.

Example 5-7. Simple (flawed) cursor loop

While this code might seem sensible and complete, there is a problem: if we attempt to fetch a row after the last row in the cursor has been fetched, MySQL will raise the "no data to fetch" error (MySQL error 1329; SQLSTATE 02000). So the code in Example 5-7 will abort as shown here:

mysql> call simple_cursor_loop( ); ERROR 1329 (02000): No data to FETCH

To avoid this error, we declare an error handler that will catch "no data to fetch" and set a flag (implemented as a local variable). We then interrogate that variable to determine if the last row has been fetched. Using this technique, we can terminate our loop and close the cursor with intuitive, easy-to-understand code.

We discuss error handlers in detail in Chapter 6. However, in this situation, we will add the following statement to our code:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;

This handler instructs MySQL to do two things when the "no data to fetch" scenario occurs:

1. Set the value of the "last row variable" (l_last_row_fetched) to 1.
2. Allow the program to continue executing.

Our program can now check the value of l_last_row_fetched. If it is set to 1, then we know that the last row has been fetched, and we can terminate the loop and close the cursor.

It is very important that we reset the "end of result set" indicator after the cursor has been closed. Otherwise, the next time we try to fetch from this cursor, the program will immediately terminate the loop, thinking that we are done.

Example 5-8 shows all of these steps: declare the CONTINUE handler, loop through the rows of the result set, leave the loop if the variable has been set, and then clean up.

Example 5-8. Simple cursor loop

Note that we don't have to process all the rows in the result set; we can issue the LEAVE statement at any time to terminate the cursor loop if we have processed all the data we need.

5.3.5. Types of Cursor Loops

We can use any of the three looping constructs (simple loop, WHILE loop, and REPEAT UNTIL loop) to iterate through the rows returned by a cursor. In each case, we need to construct the loop so that the loop will terminate when the "last row variable" is set by the NOT FOUND handler.

Consider the cursor and the NOT FOUND handler shown in Example 5-9.

Example 5-9. Cursor declaration with associated handler

The simplest construct is the LOOP-LEAVE-END LOOP sequence. In this case, our cursor loop would look like that shown in Example 5-10.

Example 5-10. A LOOP-LEAVE-END LOOP cursor loop

The logic of Example 5-10 is simple: we open the cursor and then iteratively fetch the rows. If we try to fetch beyond the end of the result set, the handler sets no_more_departments to 1 and we call the LEAVE statement to terminate the loop. Finally, we close the cursor and reset the no_more_departments variable.

The WHILE loop is very familiar to programmers and might therefore seem like a natural choice for constructing a cursor loop. In fact, however, you will very likely find that the REPEAT UNTIL loop is a more appropriate construct for a cursor loop. The REPEAT always executes its body at least once before evaluating the continuation expression. In the context of cursor processing, we usually will want to fetch at least once before checking to see if we are done processing the cursor's result set. Hence, using the REPEAT UNTIL loop can produce more readable code, as shown in Example 5-11.

Example 5-11. Cursor loop with REPEAT UNTIL loop

However, this loop only works because we did nothing with each row fetched by the cursor. Fetching rows from a cursor just for the heck of it is very unusualit is far more common to do something with the rows returned. For instance, in our first LOOP-LEAVE-END LOOP example, we at least counted the rows returned by the cursor. However, since the final fetch returns no rows, we need a way to avoid processing after that final fetch. So in fact, even if we use the REPEAT UNTIL loop, we still need a LEAVE statement to avoid processing the nonexistent row returned (or rather, not returned) by the final fetch. Thus, if we want to count the number of rows returned by the cursor (or do anything else with the results) we will need to include loop labels and a LEAVE statement, as in the amended version of our previous example, shown in Example 5-12.

Example 5-12. Most REPEAT UNTIL loops also need a LEAVE statement

The necessity of including a LEAVE statement in almost every REPEAT UNTIL loop makes the presence of the UNTIL clause redundantalthough it arguably improves readability and protects you against the possibility of an infinite loop if your LEAVE statement fails to execute (perhaps you miscoded the IF clause). In the end, valid cursor loops can be established in either fashion, and there is no compelling case to recommend one style over the other. All we can say is that your code as a whole will be more readable if you use a consistent style for all of your cursor loops.

An alternative to a LEAVE statement would be an IF statement that executes whatever post-processing occurs once we determine that the FETCH has reached the end of the result set. Example 5-13 shows how we could construct this loop for our example. In this case, an IF statement is added that performs row processing only if the no_more_departments variable has not been set.

Example 5-13. Using an IF block as an alternative to a LEAVE statement in a REPEAT UNTIL cursor loop

The third style of cursor loop involves the WHILE-END WHILE loop. WHILE evaluates its condition before the first execution of the loop, so it is a less logical choice than REPEAT-UNTIL or LOOP-END LOOP, since logically we cannot know if we have reached the end of the cursor until we have fetched at least one row. On the other hand, WHILE is probably the looping construct used in the widest variety of other programming languages, so it might confer a clearer understanding of the program's intentions to those who are not familiar with the MySQL stored program language.

In any case, the WHILE loop also requires a LEAVE statement if there is any processing of the cursor results attempted within the loop, so the code in Example 5-14 looks very similar to our previous examples.

Example 5-14. A cursor WHILE loop

5.3.6. Nested Cursor Loops

It is not uncommon to nest cursor loops. For instance, one loop might retrieve a list of interesting customers, while an inner loop retrieves all the orders for those customers. The most significant issue relating to this sort of nesting is that the NOT FOUND handler variable will be set whenever either cursor completesso you are going to need to be very careful to ensure that a NOT FOUND condition does not cause both cursors to be closed.

For instance, consider the nested cursor loops shown in Example 5-15.

Example 5-15. A (flawed) nested cursor loop

This stored procedure contains a subtle bug. When the first "inner" loop through the emp_csr cursor completes, the value of l_done is set to 1. Consequently, at the next iteration through the "outer" loop through the dept_csr, the value of l_done is still set to 1 and the outer loop is inadvertently terminated. As a result, we only ever process a single department. There are two possible solutions to this problem: the easier of the two is simply to reset the "not found" variable at the end of each loop, as in Example 5-16.

Example 5-16. A correct nested cursor example

It is always good practice to reset the value of a "not found" variable once it has been used so that subsequent cursor iterations are not affected.

A slightly more complexbut arguably more robust solutionis to give each cursor its own handler. Because you can only have one NOT FOUND handler active within any particular block, this can only be done by enclosing each cursor in its own block. For instance, we could place the sales cursor in its own block with its own NOT FOUND handler, as in Example 5-17.

Example 5-17. Nested cursors using nested blocks

Note that we now have a separate "not found" variable for each cursor, and we have eliminated any possibility that the closing of one cursor could affect the status of another. However, also note that we still reset the "not found" variables after we completed each cursor loopthis remains highly recommended since you may still wish to reopen a cursor within the same block.

5.3.7. Exiting the Cursor Loop Prematurely

Don't assume that you can only exit the cursor loop when the last row has been retrievedyou can issue a LEAVE statement at any time that you think that your processing has been completed. You may be looking for only one or a limited number of candidate records in the result set, or you may have detected some other condition suggesting that further processing is unnecessary.

5.3.8. Cursor Error Conditions

Cursor statements must occur in the sequence OPEN-FETCH-CLOSE. Any variation on this sequence will result in runtime errors.

For instance, if you try to CLOSE or FETCH from a cursor that is not open, you will encounter a Cursor is not open error, as shown in Example 5-18.

Example 5-18. Cursor is not open error

Attempting to open a cursor that is already open results in a Cursor is already open error, as shown in Example 5-19.

Example 5-19. Cursor is already open error