The following procedure uses a cursor on a SELECT statement. Based on the same query used in the ListCustomerValue procedure described in Returning result sets from procedures, it illustrates several features of the stored procedure language.
CREATE PROCEDURE TopCustomerValue( OUT TopCompany CHAR(36), OUT TopValue INT ) BEGIN -- 1. Declare the "row 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 CompanyName, CAST( sum( SalesOrderItems.Quantity * Products.UnitPrice ) AS INTEGER ) AS value FROM Customers INNER JOIN SalesOrders INNER JOIN SalesOrderItems INNER JOIN Products GROUP BY CompanyName; -- 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; -- 7. Close the cursor CLOSE ThisCompany; END; |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |