Filter
Top Products
CHAPTER 6 Using Procedures and Batches
253
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;