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:
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
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:
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.
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.
blog comments powered by Disqus