User's Guide
   PART 1. Working with Databases
     CHAPTER 10. Using Procedures, Triggers, and Batches       

Using cursors in procedures and triggers

Cursors are used to retrieve rows one at a time from a query or stored procedure that has multiple rows in its result set. A cursor is a handle or an identifier for the query or procedure, and for a current position within the result set.

  Cursor management overview

Managing a cursor is similar to managing a file in a programming language. The following steps are used to manage cursors:

1. Declare a cursor for a particular SELECT statement or procedure using the DECLARE statement.

2. Open the cursor using the OPEN statement.

3. Use the FETCH statement to retrieve results one row at a time from the cursor.

4. Records are usually fetched until the warning Row Not Found is returned, signaling the end of the result set.

5. Close the cursor using the CLOSE statement.

By default, cursors are automatically closed at the end of a transaction (on COMMIT or ROLLBACK statements). Cursors that are opened using the WITH HOLD clause will be kept open for subsequent transactions until they are explicitly closed.

Cursor positioning 

A cursor can be positioned at one of three places:

• On a row

• Before the first row

• After the last row

When a cursor is opened, it is positioned before the first row. The cursor position can be moved using the FETCH command (see FETCH statement). It can be positioned to an absolute position from the start or the end of the query results (using FETCH ABSOLUTE, FETCH FIRST, or FETCH LAST). It can also be moved relative to the current cursor position (using FETCH RELATIVE, FETCH PRIOR, or FETCH NEXT). The NEXT keyword is the default qualifier for the FETCH statement.

There are special positioned versions of the UPDATE and DELETE statements that can be used to update or delete the row at the current position of the cursor. If the cursor is positioned before the first row or after the last row, a No current row of cursor error will be returned.

  Using cursors on SELECT statements in procedures

The following procedure uses a cursor on a SELECT statement. It illustrates several features of the stored procedure language. It is based on the same query used in the ListCustomerValue procedure described in Returning result sets from procedures.

CREATE PROCEDURE TopCustomerValue 
   (   OUT TopCompany CHAR(36),
      OUT TopValue INT )
BEGIN
   -- 1. Declare the "error not found" exception
   DECLARE err_notfound 
      EXCEPTION FOR SQLSTATE '02000';
   -- 2.   Declare variables to hold 
   --      each company name and its value
   DECLARE ThisName CHAR(36);
   DECLARE ThisValue INT;
   -- 3.   Declare the cursor ThisCompany 
   --      for the query
   DECLARE ThisCompany CURSOR FOR 
   SELECT company_name, 
         CAST( sum( sales_order_items.quantity * 
               product.unit_price ) AS INTEGER ) 
         AS value
   FROM customer
      INNER JOIN sales_order 
      INNER JOIN sales_order_items 
      INNER JOIN product 
   GROUP BY company_name;
   -- 4. Initialize the values of TopValue
   SET TopValue = 0;
   -- 5. Open the cursor
   OPEN ThisCompany;
   -- 6. Loop over the rows of the query
   CompanyLoop:
   LOOP
      FETCH NEXT ThisCompany
         INTO ThisName, ThisValue;
      IF SQLSTATE = err_notfound THEN
         LEAVE CompanyLoop;
      END IF;
      IF ThisValue > TopValue THEN
         SET TopCompany = ThisName;
         SET TopValue = ThisValue;
      END IF;
   END LOOP CompanyLoop;
   -- 7. Close the cursor
   CLOSE ThisCompany;
END

Notes 

The TopCustomerValue procedure has the following notable features:

• The "error not found" exception is declared. This exception is used later in the procedure to signal when a loop over the results of a query has completed.

       For more information about exceptions, see Errors and warnings in procedures and triggers.

• Two local variables ThisName and ThisValue are declared to hold the results from each row of the query.

• The cursor ThisCompany is declared. The SELECT statement produces a list of company names and the total value of the orders placed by that company.

• The value of TopValue is set to an initial value of 0, for later use in the loop.

• The ThisCompany cursor is opened.

• The LOOP statement loops over each row of the query, placing each company name in turn into the variables ThisName and ThisValue. If ThisValue is greater than the current top value, TopCompany and TopValue are reset to ThisName and ThisValue.

• The cursor is closed at the end of the procedure.

The LOOP construct in the TopCompanyValue procedure is a standard form, exiting after the last row is processed. You can rewrite this procedure in a more compact form using a FOR loop. The FOR statement combines several aspects of the above procedure into a single statement.

CREATE PROCEDURE TopCustomerValue2(
      OUT TopCompany CHAR(36),
      OUT TopValue INT )
BEGIN
   -- Initialize the TopValue variable
   SET TopValue = 0;
   -- Do the For Loop 
   CompanyLoop:
   FOR CompanyFor AS ThisCompany 
      CURSOR FOR 
      SELECT company_name AS ThisName , 
         CAST( sum( sales_order_items.quantity * 
               product.unit_price ) AS INTEGER )
         AS ThisValue 
      FROM customer
         INNER JOIN sales_order 
         INNER JOIN sales_order_items 
         INNER JOIN product 
      GROUP BY ThisName 
   DO 
      IF ThisValue > TopValue THEN
         SET TopCompany = ThisName;
         SET TopValue = ThisValue;
         END IF;
   END FOR CompanyLoop;
END