Filter
Top Products
Using cursors in procedures
254
-- 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”.
• 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 )