Working with Loops

Snowflake Scripting supports the following types of loops:

This topic explains how to use each of these types of loops.

In this Topic:

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 web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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.

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 web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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.

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 web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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.

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 web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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.

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 web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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.

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 web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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 labeled OUTER.

  • CONTINUE OUTER starts another iteration of the loop with the label OUTER.

  • BREAK OUTER terminates the inner loop and transfers control to the end of the outer loop (labeled OUTER).

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 because BREAK OUTER transfers control to the end of the outer loop.

Back to top