Sybase 12.4.2 User Manual

Using cursors in procedures

 

-- 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 )