Cursors - MySQL

Stored procedures using SELECT INTO are somewhat limited in that they can’t be used to retrieve more than one row from a table in a single query, because you can’t store more than one value in a single variable.

A cursor can be thought of as an entity that points a SELECT statement at a particular row of a table. In MySQL 5.0, cursors are fairly simple. They’re read-only, forward-only, meaning that they can be used only for the following:

• To read data from a table row, but not update it or insert new rows into the table.
  
• To move through the rows of a table in the order returned by MySQL; they can’t be programmed to skip rows, iterate through rows in reverse order, and so on.

Cursors are expected to be enhanced in MySQL 5.1 and subsequent versions to include the capabilities excluded here (and possibly others). However, even with the limited implementation available in MySQL 5.0, they make it possible using a stored procedure to fetch query results into another table where they can be processed further.

Cursor Statements: Syntax

In order to create and use cursors in MySQL 5.0, we need to make use of the commands DECLARE , OPEN , FETCH , and CLOSE . Let’s look at each of these in turn, and then in the next section, we’ll put them together in a working example.

First, we must declare a cursor in a manner similar to that in which we declare a local variable for a stored procedure, using the DECLARE CURSOR command, whose syntax is

DECLARE name CURSOR FOR statement;

In this command, name stands for the name of the cursor and statement for a SELECT statement. For instance, one of the cursor declaration statements you’ll see in the next section is

DECLARE prodrecord CURSOR FOR SELECT name, price FROM products;

In ordinary language, this statement says, “The cursor named prodrecord will be used to work with rows returned by the query SELECT name, price FROM products; .” A cursor can be declared only once, and used with only one query in any given procedure. It’s possible and frequently desirable to declare and use multiple cursors in a single procedure, but each cursor must be uniquely named. The cursor name must follow the rules for MySQL identifiers (see Chapter 1), and all DECLARE CURSOR statements must come at the beginning of the body of a stored procedure, just as with DECLARE statements used to create local variables.

Before we can actually begin using the cursor, it must be opened. The OPEN command tells MySQL to prepare the cursor for use with the result set of the query to which it is bound by the DECLARE CURSOR statement. The syntax for the OPEN command is simply

OPEN cursor-name;

where cursor-name is the name of the cursor. The cursor must previously have been declared before it can be opened.

In order to point this cursor to successive rows in the result set, we use the FETCH command, shown here:

FETCH cursor-name INTO var1[, var2[, var3[, ...]]];

This command tells MySQL to store the column values for the next row of the result set into one or more variables for subsequent use in the stored procedure. There must be as many variables as there are columns in the result set, and the variables must previously have been declared using DECLARE . FETCH is generally only useful if used in some sort of a loop; and we’ll see in the next section how this is done, and how to tell when there are no more rows left to retrieve.

Finally, when you’re finished with the cursor, you need to close it using the CLOSE command as shown here. Once again, cursor-name is the name of the cursor:

CLOSE cursor-name;

MySQL will automatically close the cursor upon completion of the stored procedure in which the cursor was declared, but it’s always good practice to do so explicitly, and free up the resources used by the cursor as soon as they’re no longer needed.

---

NOTE  A cursor is actually a special SQL datatype that corresponds to a row from a result set.

---

There’s one other matter we need to take care of before we can proceed to a working example. When processing a SELECT query that was entered from the command line, MySQL just gives us the entire result set in one go, and, as you saw in Chapter 7, when you’re writing MySQL applications in a language such as PHP or Python, you’re provided with a means to tell when you’ve reached the end of the result set. For example, in PHP 5 using ext/mysqli , you’ll use a while loop with one of the fetch_*() methods of the mysqli class, which conveniently returns a Boolean FALSE when you’ve reached the end, like so:

<?php
    while($row = $result->fetch_object())
    {
      // display the rows
      printf("<tr>\n <td align=\"center\">%s</td>\n
               <td>%s</td>\n  <td>%std>\n</tr>",
               $row->empid, $row->firstname, $row->lastname);
    }
?>

As with a programming language’s MySQL API, we obtain a result set by retrieving successive rows of it, one at a time, inside a loop (such as WHILE ... DO or REPEAT ... UNTIL ). However, when you’re working with cursors inside a stored procedure and you reach the end, you’re likely to see something like this:

Unlike the case with a high-level API such as PHP 5’s ext/mysqli or Python’s MySQLdb , there’s no handy built-in function or method that behaves in the desired fashion. Instead, you must do a bit of error handling. This is accomplished by declaring an error handler in the procedure using the DECLARE HANDLER command. Here’s a simplified version of its syntax:

DECLARE [CONTINUE | EXIT] HANDLER FOR condition statement;

This statement tells MySQL what to do in the event that an error or warning is generated while executing a stored procedure or stored function. In MySQL 5.0,

you have two choices: CONTINUE or EXIT (which is the default). By using CONTINUE , you can tell MySQL to execute the statement that follows, which can be any state ment that’s valid inside a stored procedure. Using EXIT will cause MySQL to terminate execution of the stored procedure at the point where the error is encountered.

---

NOTE In future versions of MySQL beyond 5.0, an UNDO option is also expected to be supported in addition to CONTINUE and EXIT . This will allow you to undo the last action performed in the stored procedure or stored function, that is, to undo the action that gave rise to the error or warning.

---

The condition to be handled can be expressed in a number of ways including MySQL error codes and SQLSTATE values for error conditions. (When signaling an error, MySQL 5.0 and above will provide both sorts of error codes, with the MySQL error code first, followed by the SQLSTATE error code in parentheses.) It is possible to set a single handler for multiple conditions in one DECLARE HANDLER statement by separating them with commas.

---

TIP  You can find an up-to-date listing of MySQL and SQLSTATE error codes supported by MySQL in the online version of the MySQL Manual at http://dev.mysql.com/doc/mysql/en/Error-handling.html .

---

In the next section, we’ll put all of this together in a simple working example.