Sybase 12.4.2 manual 254

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 )

254