ASA SQL User's Guide
  Using Procedures, Triggers, and Batches
    Using cursors in procedures and triggers

Using cursors on SELECT statements in procedures

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

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 signals, later in the procedure, when a loop over the results of a query completes.

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

• 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 closes at the end of the procedure.

• You can also write this procedure without a loop by adding an ORDER BY value DESC clause to the SELECT statement. Then, only the first row of the cursor needs to be fetched.

The LOOP construct in the TopCompanyValue procedure is a standard form, exiting after the last row processes. 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
   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;
END