FOR
FOR statements are used in SQL Script to loop through a query or cursor. FOR statements have two formats.
Syntax1
Used to loop across a query expression.
[<label>:]
FOR <loopVariable> AS [<cursorName> CURSOR FOR]
<queryExpression> DO
<statements>
END FOR [<label>]
Syntax2
Used to loop across a cursor. For details, see
DECLARE CURSOR of Type Variable.
[<label>:]
FOR <loopVariable> AS <cursorVariable> DO
<statements>
END FOR [<label>]
Remarks
• The <label> is an optional identifier to name the block. This is for use with the LEAVE and ITERATE statements. See
LEAVE and
ITERATE.
• If a beginning label is present, the end label is not required. If no beginning label is present, it is illegal to have an end label. If both the beginning and end labels are present, both must have the same identifier.
• There can be zero or more statements in the <statements> area.
• The FOR statement declares the loop variable to be of the proper type to match the query expression (a ROW). You do not have to declare that variable elsewhere. The variable is only legal within the loop block. This variable can have the same name as another variable in the current scope (or a parent scope), but it cannot have the same name as a parameter to the procedure. If it does have the same name, the same rules apply as for declaring variables in a compound statement. See
Compound Statements for details.
• If a cursor variable is provided in the first format (Syntax 1), it is also declared at this point. You do not declare it separately. This variable is set to be a cursor for the provided query expression.
• The cursor is opened when it starts. You do not have to open the cursor. It then fetches rows (use FETCH) one at a time and assigns the row into the loop variable. This makes it possible to operate on each row one at a time. The cursor is closed automatically when the loop ends. See
FETCH.
If you open the cursor (and even fetch a few rows), the FOR loop picks up where the cursor is. If you do not open the cursor, the FOR statement opens it for you.
The FOR loop closes the cursor no matter how the loop exits (even with a LEAVE statement).
• When a FOR loop is passed a cursor, it opens the cursor if it is not already open.
• After the FOR loop, the cursor is closed. Even if you try to LEAVE the FOR loop, the cursor is closed. If you try to close a cursor that was used by a FOR loop, an error occurs.
Example
--Returns the average of all scores
PROCEDURE avr_score(OUT result INTEGER)
BEGIN
DECLARE crs CURSOR FOR
SELECT name, score FROM /shared/U ORDER BY score DESC;
DECLARE total INTEGER DEFAULT 0;
DECLARE cnt INTEGER DEFAULT 0;
OPEN crs;
FOR r AS crs DO
SET total = total + r.score;
SET cnt = cnt + 1;
END FOR;
SET result = total/cnt;
END