WHILE

Translation reference to convert Oracle WHILE statement to Snowflake Scripting

Description

The WHILE LOOP statement runs one or more statements while a condition is TRUE. (Oracle PL/SQL Language Reference WHILE Statement)

Oracle WHILE Syntax

WHILE boolean_expression
  LOOP statement... END LOOP [ label ] ;

Snowflake Scripting WHILE Syntax

WHILE ( <condition> ) { DO | LOOP }
  <statement>;
  [ <statement>; ... ]
END { WHILE | LOOP } [ <label> ] ;

Oracle WHILE behavior can also be modified by using the statements:

• CONTINUE

• EXIT

• GOTO

• RAISE

Sample Source Patterns

While simple case

This case is functionally equivalent.

Oracle

Query

CREATE TABLE while_testing_table
(
  iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE while_procedure 
IS
I NUMBER := 1;
J NUMBER := 10;
BEGIN  
  WHILE I <> J LOOP
    INSERT INTO while_testing_table VALUES(TO_CHAR(I));
    I := I+1;    
  END LOOP;
END;

CALL while_procedure();
SELECT * FROM while_testing_table;

Result

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

Snowflake Scripting

Query

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

CREATE OR REPLACE PROCEDURE PUBLIC.while_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      I NUMBER := 1;
      J NUMBER := 10;
   BEGIN
      WHILE (:I <> :J) LOOP
         INSERT INTO PUBLIC.while_testing_table VALUES(TO_CHAR(:I));
         I := :I +1;
      END LOOP;
   END;
$$;

CALL PUBLIC.while_procedure();

SELECT * FROM PUBLIC.while_testing_table;

Result

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

Known Issues

No issues were found.

Related EWIs

No related EWIs.