Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 274 / 536

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 )