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, and 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;
	-- 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.
You can also write this procedure without a loop by adding an ORDER BY value DESC clause to the SELECT statement. Then, only the first row of the cursor needs to be fetched.

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 )
			AS ThisValue 
		FROM customer
			INNER JOIN sales_order 
			INNER JOIN sales_order_items 
			INNER JOIN product 
		GROUP BY ThisName 
	DO 
		IF ThisValue > TopValue THEN
			SET TopCompany = ThisName;
			SET TopValue = ThisValue;
			END IF;
	END FOR CompanyLoop;
END