MySQL

Working with Cursors

Using cursors involves several distinct steps:

  1. Before a cursor can be used it must be declared (defined). This process does not actually retrieve any data; it merely defines the SELECT statement to be used.
    2. `
  2. After it is declared, the cursor must be opened for use. This process actually retrieves the data using the previously defined SELECT statement.
  3. With the cursor populated with data, individual rows can be fetched (retrieved) as needed.
  4. When it is done, the cursor must be closed.

After a cursor is declared, it may be opened and closed as often as needed. After it is open, fetch operations can be performed as often as needed.

Creating Cursors

Cursors are created using the DECLARE statement. DECLARE names the cursor and takes a SELECT statement, complete with WHERE and other clauses if needed. For example, this statement defines a cursor named ordernumbers using a SELECT statement that retrieves all orders:

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR
   FOR
   SELECT ordernum FROM orders;
END;

This stored procedure does not do a whole lot. A DECLARE statement is used to define and name the cursorin this case ordernumbers. Nothing is done with the cursor, and as soon as the stored procedure finishes processing it will cease to exist (as it is local to the stored procedure itself).

Now that the cursor is defined, it is ready to be opened.

Opening and Closing Cursors

Cursors are opened using the OPEN CURSOR statement, like this:

OPEN ordernumbers;

When the OPEN statement is processed, the query is executed, and the retrieved data is stored for subsequent browsing and scrolling.

After cursor processing is complete, the cursor should be closed using the CLOSE statement, as follows:

CLOSE ordernumbers;

CLOSE frees up any internal memory and resources used by the cursor, and so every cursor should be closed when it is no longer needed.

After a cursor is closed, it cannot be reused without being opened again. However, a cursor does not need to be declared again to be used; an OPEN statement is sufficient.

If you do not explicitly close a cursor, MySQL will close it automatically when the END statement is reached.

Here is an updated version of the previous example:

CREATE PROCEDURE processorders()
BEGIN
   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
   -- Open the cursor
   OPEN ordernumbers;
   -- Close the cursor
   CLOSE ordernumbers;
END;

This stored procedure declares, opens, and closes a cursor. However, nothing is done with the retrieved data.

Using Cursor Data

After a cursor is opened, each row can be accessed individually using a FETCH statement. FETCH specifies what is to be retrieved (the desired columns) and where retrieved data should be stored. It also advances the internal row pointer within the cursor so the next FETCH statement will retrieve the next row (and not the same one over and over).

The first example retrieves a single row from the cursor (the first row):

CREATE PROCEDURE processorders()
BEGIN
   -- Declare local variables
   DECLARE o INT;
   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
   -- Open the cursor
   OPEN ordernumbers;
   -- Get order number
   FETCH ordernumbers INTO o;
   -- Close the cursor
   CLOSE ordernumbers;
END;

Here FETCH is used to retrieve the order_num column of the current row (it'll start at the first row automatically) into a local declared variable named o. Nothing is done with the retrieved data.

In the next example, the retrieved data is looped through from the first row to the last:

CREATE PROCEDURE processorders()
BEGIN
   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;
   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   -- Open the cursor
   OPEN ordernumbers;
   -- Loop through all rows
   REPEAT
      -- Get order number
      FETCH ordernumbers INTO o;
   -- End of loop
   UNTIL done END REPEAT;
   -- Close the cursor
   CLOSE ordernumbers;
END;

Like the previous example, this example uses FETCH to retrieve the current order_num into a declared variable named o. Unlike the previous example, the FETCH here is within a REPEAT so it is repeated over and over until done is true (as specified by UNTIL done END REPEAT;). To make this work, variable done is defined with a DEFAULT 0 (false, not done). So how does done get set to true when done? The answer is this statement:

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

This statement defines a CONTINUE HANDLER, code that will be executed when a condition occurs. Here it specifies that when SQLSTATE '02000' occurs, then SET done=1. And SQLSTATE '02000' is a not found condition and so it occurs when REPEAT cannot continue because there are no more rows to loop through.

MySQL Error Codes For a complete list of MySQL error codes used by MySQL 5, see http://dev.mysql.com/doc/mysql/en/error-handling.html.

There is specific order in which DECLARE statements, if used, must be issued. Local variables defined with DECLARE must be defined before any cursors or handlers are defined, and handlers must be defined after any cursors. Failure to follow this sequencing will generate an error message.

If you were to call this stored procedure it would define variables and a CONTINUE HANDLER, define and open a cursor, repeat through all rows, and then close the cursor.

With this functionality in place you can now place any needed processing inside the loop (after the FETCH statement and before the end of the loop).

In addition to the REPEAT statement used here, MySQL also supports a LOOP statement that can be used to repeat code until the LOOP is manually exited using a LEAVE statement. In general, the syntax of the REPEAT statement makes it better suited for looping through cursors.

To put this all together, here is one further revision of our example stored procedure with cursor, this time with some actual processing of fetched data:

CREATE PROCEDURE processorders()
BEGIN
   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;
   DECLARE t DECIMAL(8,2);
   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   -- Create a table to store the results
   CREATE TABLE IF NOT EXISTS ordertotals
      (order_num INT, total DECIMAL(8,2));
   -- Open the cursor
   OPEN ordernumbers;
   -- Loop through all rows
   REPEAT
      -- Get order number
      FETCH ordernumbers INTO o;
      -- Get the total for this order
      CALL ordertotal(o, 1, t);
      -- Insert order and total into ordertotals
      INSERT INTO ordertotals(order_num, total)
      VALUES(o, t);
   -- End of loop
   UNTIL done END REPEAT;
   -- Close the cursor
   CLOSE ordernumbers;
END;

In this example, we've added another variable named t (this will store the total for each order). The stored procedure also creates a new table on the fly (if it does not exist) named ordertotals. This table will store the results generated by the stored procedure. FETCH fetches each order_num as it did before, and then used CALL to execute another stored procedure (the one we created in the previous tutorial) to calculate the total with tax for each order (the result of which is stored in t). And then finally, INSERT is used to save the order number and total for each order.

This stored procedure returns no data, but it does create and populate another table that can then be viewed using a simple SELECT statement:

SELECT *
FROM ordertotals;


+-----------+---------+
| order_num | total   |
+-----------+---------+
|     20005 |  158.86 |
|     20006 |   58.30 |
|     20007 | 1060.00 |
|     20008 |  132.50 |
|     20009 |   40.78 |
+-----------+---------+

And then you have it, a complete working example of stored procedures, cursors, row-by-row processing, and even stored procedures calling other stored procedures.