Sybase 12.4.2 manual Using cursors on Select statements in procedures, 253

CHAPTER 6 Using Procedures and Batches

Using cursors on SELECT statements in procedures

The following procedure uses a cursor on a SELECT statement. It illustrates several features of the stored procedure language. It is based on the same query used in the ListCustomerValue procedure described in “Returning result sets from procedures”.

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;

253