LOOP

Translation reference to convert Oracle LOOP statement to Snowflake Scripting

Description

With each iteration of the basic LOOP statement, its statements run and control returns to the top of the loop. The LOOP statement ends when a statement inside the loop transfers control outside the loop or raises an exception. (Oracle PL/SQL Language Reference BASIC LOOP Statement)

Oracle BASIC LOOP Syntax

LOOP statement... END LOOP [ label ] ;

Snowflake Scripting BASIC LOOP Syntax

LOOP
  <statement>;
  [ <statement>; ... ]
END LOOP [ <label> ] ;

Oracle BASIC LOOP behavior can also be modified by using the statements:

• CONTINUE

• EXIT

• GOTO

• RAISE

Sample Source Patterns

Loop simple case

This case is functionally equivalent.

Oracle

Query

CREATE TABLE loop_testing_table
(
  iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE loop_procedure 
IS
I NUMBER := 1;
J NUMBER := 10;
BEGIN  
  LOOP
    EXIT WHEN I = J;
    INSERT INTO loop_testing_table VALUES(TO_CHAR(I));
    I := I+1;
  END LOOP;
END;

CALL loop_procedure();
SELECT * FROM loop_testing_table;

Result

ITERATOR|
--------+
1       |
2       |
3       |
4       |
5       |
6       |
7       |
8       |
9       |

Snowflake Scripting

Query

CREATE OR REPLACE TABLE PUBLIC.loop_testing_table (
iterator VARCHAR(5));

CREATE OR REPLACE PROCEDURE PUBLIC.loop_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      I NUMBER := 1;
      J NUMBER := 10;
   BEGIN
      LOOP
         IF ( I = :J) THEN
            EXIT ;
         END IF;
         INSERT INTO PUBLIC.loop_testing_table VALUES(TO_CHAR(:I));
         I := :I +1;
      END LOOP;
   END;
$$;

CALL PUBLIC.loop_procedure();

SELECT * FROM PUBLIC.loop_testing_table;

Result

ITERATOR|
--------+
1       |
2       |
3       |
4       |
5       |
6       |
7       |
8       |
9       |

Known Issues

No issues were found.

Related EWIs

No related EWIs.