Working with Loops¶
Snowflake Scripting supports the following types of loops:
This topic explains how to use each of these types of loops.
FOR Loop¶
A FOR loop repeats a sequence of steps for a specified number of times or for each
row in a result set. Snowflake Scripting supports the following types of FOR
loops:
The next sections explain how to use these types of FOR loops.
Counter-Based FOR Loops¶
A counter-based FOR
loop executes a specified number of times.
The syntax for a counter-based FOR loop is
FOR <counter_variable> IN [ REVERSE ] <start> TO <end> { DO | LOOP } <statement>; [ <statement>; ... ] END { FOR | LOOP } [ <label> ] ;
For example, the following FOR loop executes 5 times:
DECLARE counter INTEGER DEFAULT 0; maximum_count INTEGER default 5; BEGIN FOR i IN 1 TO maximum_count DO counter := counter + 1; END FOR; RETURN counter; END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
EXECUTE IMMEDIATE $$ DECLARE counter INTEGER DEFAULT 0; maximum_count INTEGER default 5; BEGIN FOR i IN 1 TO maximum_count DO counter := counter + 1; END FOR; RETURN counter; END; $$ ;
For the full syntax and details about FOR statements, see FOR (Snowflake Scripting).
Cursor-Based FOR Loops¶
A cursor-based FOR
loop iterates over a result set. The number of iterations is determined by the number of
rows in the cursor.
The syntax for a cursor-based FOR loop is:
FOR <row_variable> IN <cursor_name> DO <statement>; [ <statement>; ... ] END FOR [ <label> ] ;
The following example uses a FOR loop iterate over the rows in a cursor for the invoices
table:
CREATE OR REPLACE TABLE invoices (price NUMBER(12, 2)); INSERT INTO invoices (price) VALUES (11.11), (22.22); DECLARE total_price FLOAT; c1 CURSOR FOR select price from invoices; BEGIN total_price := 0.0; FOR record IN c1 DO total_price := total_price + record.price; END FOR; RETURN total_price; END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
CREATE OR REPLACE TABLE invoices (price NUMBER(12, 2)); INSERT INTO invoices (price) VALUES (11.11), (22.22); EXECUTE IMMEDIATE $$ DECLARE total_price FLOAT; c1 CURSOR FOR select price from invoices; BEGIN total_price := 0.0; FOR record IN c1 DO total_price := total_price + record.price; END FOR; RETURN total_price; END; $$ ;
For the full syntax and details about FOR statements, see FOR (Snowflake Scripting).
WHILE Loop¶
A WHILE loop iterates while a condition is true. In a WHILE
loop, the condition is tested immediately before executing the body of the loop. If the condition is false before the first
iteration, then the body of the loop does not execute even once.
The syntax for a WHILE
loop is:
WHILE ( <condition> ) { DO | LOOP } <statement>; [ <statement>; ... ] END { WHILE | LOOP } [ <label> ] ;
For example:
BEGIN LET counter := 0; WHILE (counter < 5) DO counter := counter + 1; END WHILE; RETURN counter; END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
EXECUTE IMMEDIATE $$ BEGIN LET counter := 0; WHILE (counter < 5) DO counter := counter + 1; END WHILE; RETURN counter; END; $$ ;
For the full syntax and details about WHILE
statements, see WHILE (Snowflake Scripting).
REPEAT Loop¶
A REPEAT loop iterates until a condition is true. In a REPEAT
loop, the condition is tested immediately after executing the body of the loop. As a result, the body of the loop always executes
at least once.
The syntax for a REPEAT
loop is:
REPEAT <statement>; [ <statement>; ... ] UNTIL ( <condition> ) END REPEAT [ <label> ] ;
For example:
BEGIN LET counter := 5; LET number_of_iterations := 0; REPEAT counter := counter - 1; number_of_iterations := number_of_iterations + 1; UNTIL (counter = 0) END REPEAT; RETURN number_of_iterations; END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
EXECUTE IMMEDIATE $$ BEGIN LET counter := 5; LET number_of_iterations := 0; REPEAT counter := counter - 1; number_of_iterations := number_of_iterations + 1; UNTIL (counter = 0) END REPEAT; RETURN number_of_iterations; END; $$ ;
For the full syntax and details about REPEAT
statements, see REPEAT (Snowflake Scripting).
LOOP Loop¶
A LOOP loop executes until a BREAK
command is executed. Such a BREAK
command is normally embedded inside branching logic
(e.g. IF Statements or CASE Statements).
The syntax for a LOOP statement is:
LOOP <statement>; [ <statement>; ... ] END LOOP [ <label> ] ;
For example:
BEGIN LET counter := 5; LOOP IF (counter = 0) THEN BREAK; END IF; counter := counter - 1; END LOOP; RETURN counter; END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
EXECUTE IMMEDIATE $$ BEGIN LET counter := 5; LOOP IF (counter = 0) THEN BREAK; END IF; counter := counter - 1; END LOOP; RETURN counter; END; $$ ;
For the full syntax and details about LOOP
statements, see LOOP (Snowflake Scripting).
Terminating a Loop or Iteration¶
In a loop construct, you can specify when the loop or an iteration of the loop should terminate early. The next sections explain this in more detail:
Terminating a Loop¶
You can explicitly terminate a loop early by executing the BREAK command.
BREAK
(and its synonym EXIT
) immediately stops the current iteration, and skips any remaining iterations.
You can think of BREAK
as jumping to the first executable statement after the end of the loop.
BREAK
is required in a LOOP
but is not necessary in WHILE
, FOR
, and REPEAT
. In most cases,
if you have statements that you want to skip, you can use the standard branching constructs (IF Statements and
CASE Statements) to control which statements inside a loop are executed.
A BREAK
command itself is usually inside an IF
(or CASE
).
Terminating an Iteration Without Terminating the Loop¶
You can use the CONTINUE
(or ITERATE
) command to jump to the end of an iteration of a loop, skipping the
remaining statements in the loop. The loop continues at the start of the next iteration.
Such jumps are rarely necessary. In most cases, if you have statements that you want to skip, you can use the standard branching constructs (IF Statements and CASE Statements) to control which statements inside a loop are executed.
A CONTINUE
or ITERATE
command itself is usually inside an IF
(or CASE
).
Specifying Where Execution Should Continue After Termination¶
In a BREAK
or CONTINUE
command, if you need to continue execution at a specific point in the code (e.g. the outer
loop in a nested loop), specify a label that identifies the point at which execution should continue.
The following example demonstrates this in a nested loop:
BEGIN LET inner_counter := 0; LET outer_counter := 0; LOOP LOOP IF (inner_counter < 5) THEN inner_counter := inner_counter + 1; CONTINUE OUTER; ELSE BREAK OUTER; END IF; END LOOP INNER; outer_counter := outer_counter + 1; BREAK; END LOOP OUTER; RETURN array_construct(outer_counter, inner_counter); END;Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
EXECUTE IMMEDIATE $$ BEGIN LET inner_counter := 0; LET outer_counter := 0; LOOP LOOP IF (inner_counter < 5) THEN inner_counter := inner_counter + 1; CONTINUE OUTER; ELSE BREAK OUTER; END IF; END LOOP INNER; outer_counter := outer_counter + 1; BREAK; END LOOP OUTER; RETURN array_construct(outer_counter, inner_counter); END; $$;
In this example:
There is a loop labeled
INNER
that is nested in a loop labeledOUTER
.CONTINUE OUTER
starts another iteration of the loop with the labelOUTER
.BREAK OUTER
terminates the inner loop and transfers control to the end of the outer loop (labeledOUTER
).
The output of this command is:
+-----------------+ | anonymous block | |-----------------| | [ | | 0, | | 5 | | ] | +-----------------+
As shown in the output:
inner_counter
is incremented up to 5.CONTINUE OUTER
starts a new iteration of the outer loop, which starts a new iteration of the inner loop, which increments this counter up to 5.outer_counter
is never incremented. The statement that increments this counter is never reached becauseBREAK OUTER
transfers control to the end of the outer loop.