Database Reference
In-Depth Information
The following examples illustrate these techniques. For complete lists of available con‐
dition names, SQLSTATE values, and error codes, consult the
MySQL Reference Man‐
ual.
Detecting End-of-Data Conditions
One common use of condition handlers is to detect “no more rows” conditions. To
process a query result one row at a time, use a cursor-based fetch loop in conjunction
with a condition handler that catches the end-of-data condition. The technique has
these essential elements:
• A cursor associated with a
SELECT
statement that reads rows. Open the cursor to
start reading, and close it to stop.
• A condition handler that activates when the cursor reaches the end of the result set
and raises an end-of-data condition (
NOT
FOUND
). We used a similar handler in
Recipe 9.2
.
• A variable that indicates loop termination. Initialize the variable to
FALSE
, then set
it to
TRUE
within the condition handler when the end-of-data condition occurs.
• A loop that uses the cursor to fetch each row and exits when the loop-termination
variable becomes
TRUE
.
The following example implements a fetch loop that processes the
states
table row by
row to calculate the total US population:
CREATE
PROCEDURE
us_population
()
BEGIN
DECLARE
done
BOOLEAN
DEFAULT
FALSE
;
DECLARE
state_pop
,
total_pop
BIGINT
DEFAULT
0
;
DECLARE
cur
CURSOR
FOR
SELECT
pop
FROM
states
;
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done
=
TRUE
;
OPEN
cur
;
fetch_loop
:
LOOP
FETCH
cur
INTO
state_pop
;
IF
done
THEN
LEAVE
fetch_loop
;
END
IF
;
SET
total_pop
=
total_pop
+
state_pop
;
END
LOOP
;
CLOSE
cur
;
SELECT
total_pop
AS
'Total U.S. Population'
;
END
;
Clearly, that example is purely for illustration because in any real application you'd use
an aggregate function to calculate the total. But that also gives us an independent check
on whether the fetch loop calculates the correct value:
