Database Reference
In-Depth Information
▼
Input
CREATE PROCEDURE processorders()
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- Open the cursor
OPEN ordernumbers;
-- Loop through all rows
REPEAT
-- Get order number
FETCH ordernumbers INTO o;
-- End of loop
UNTIL done END REPEAT;
-- Close the cursor
CLOSE ordernumbers;
END;
▼
Analysis
Like the previous example, this example uses
FETCH
to retrieve the current
order_num
into a declared variable named
o
. Unlike the previous example,
the
FETCH
here is within a
REPEAT
, so it is repeated over and over until
done
is true (as specified by
UNTIL done END REPEAT;
). To make this work,
variable
done
is defined with a
DEFAULT 0
(false, not done). So how does
done
get set to true when done? The answer is this statement:
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
This statement defines a
CONTINUE HANDLER
, code that will be executed
when a condition occurs. Here it specifies that when
SQLSTATE '02000'
occurs, then
SET done=1
. And
SQLSTATE '02000'
is a
not found
condition
and so it occurs when
REPEAT
cannot continue because there are no more
rows to loop through.
