FOR LOOP
Description
With each iteration of the
FOR
LOOP
statement, its statements run, its index is either incremented or decremented, and control returns to the top of the loop. (Oracle PL/SQL Language Reference FOR LOOP Statement).
FOR
pls_identifier [ MUTABLE | IMMUTABLE ] [ constrained_type ]
[ , iterand_decl ]
IN
[ REVERSE ] iteration_control pred_clause_seq
[, qual_iteration_ctl]...
LOOP
statement...
END LOOP [ label ] ;
FOR <counter_variable> IN [ REVERSE ] <start> TO <end> { DO | LOOP }
statement;
[ statement; ... ]
END { FOR | LOOP } [ <label> ] ;
Snowflake Scripting supports FOR LOOP
that loops a specified number of times. The upper and lower bounds must be INTEGER
. Check more information in the Snowflake Scripting documentation.
Oracle FOR LOOP
behavior can also be modified by using the statements:
GOTO
Sample Source Patterns
1. FOR LOOP
This case is functionally equivalent.
CREATE OR REPLACE PROCEDURE P1
AS
BEGIN
FOR i IN 1..10
LOOP
NULL;
END LOOP;
FOR i IN VAR1..VAR2
LOOP
NULL;
END LOOP;
FOR i IN REVERSE 1+2..10+5
LOOP
NULL;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE PUBLIC.P1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
FOR i IN 1 TO 10
LOOP
NULL;
END LOOP;
FOR i IN VAR1 TO VAR2
LOOP
NULL;
END LOOP;
FOR i IN REVERSE 1+2 TO 10+5
LOOP
NULL;
END LOOP;
END;
$$;
2. FOR LOOP with additional clauses
CREATE OR REPLACE PROCEDURE P2
AS
BEGIN
FOR i IN 1..10 WHILE i <= 5 LOOP
NULL;
END LOOP;
FOR i IN 5..15 BY 5 LOOP
NULL;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE PUBLIC.P2 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
/*** MSC-WARNING - MSCEWI3101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
FOR i IN 1 TO 10 LOOP
NULL;
END LOOP;
/*** MSC-WARNING - MSCEWI3101 - FOR LOOP WITH "BY" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
FOR i IN 5 TO 15 LOOP
NULL;
END LOOP;
END;
$$;
3. FOR LOOP with multiple conditions
CREATE OR REPLACE PROCEDURE P3
AS
BEGIN
FOR i IN REVERSE 1..3,
REVERSE i+5..i+7
LOOP
NULL;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE PUBLIC.P3 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
/*** MSC-WARNING - MSCEWI3100 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
FOR i IN REVERSE 1 TO 3
LOOP
NULL;
END LOOP;
END;
$$;
4. FOR LOOP with unsupported format
CREATE OR REPLACE PROCEDURE P3
AS
TYPE values_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
l_employee_values values_aat;
BEGIN
FOR power IN REPEAT power*2 WHILE power <= 64 LOOP
NULL;
END LOOP;
FOR i IN VALUES OF l_employee_values LOOP
NULL;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE PUBLIC.P3 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
l_employee_values VARIANT /*** MSC-WARNING - MSCEWI1055 - REFERENCED CUSTOM TYPE 'values_aat' NOT FOUND ***/;
BEGIN
-- ** MSC-ERROR - MSCEWI3103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
-- /*** MSC-WARNING - MSCEWI3101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
-- FOR power IN REPEAT power*2 WHILE power <= 64 LOOP
-- NULL;
-- END LOOP
;
-- ** MSC-ERROR - MSCEWI3103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
-- FOR i IN VALUES OF l_employee_values LOOP
-- NULL;
-- END LOOP
;
END;
$$;
Transformation for custom types is currently not supported for Snowflake Scripting.
Known Issues
1. For With Multiple Conditions
Oracle allows multiple conditions in a single FOR LOOP
however, Snowflake Scripting only allows one condition per FOR LOOP
. Only the first condition is migrated and the others are ignored during transformation. Check MSCEWI3100.
FOR i IN REVERSE 1..3,
REVERSE i+5..i+7
LOOP
NULL;
END LOOP;
/*** MSC-WARNING - MSCEWI3100 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
FOR i IN REVERSE 1 TO 3
LOOP
NULL;
END LOOP;
2. Mutable vs Inmutable Counter Variable
Oracle allows modifying the value of the FOR LOOP
variable inside the loop. The current documentation includes this functionality but Snowflake recommends avoiding this. Modifying the value of this variable may not behave correctly in Snowflake Scripting.
3. Integer vs Float number for Upper or Lower Bound
Snowflake Scripting only allows an INTEGER
or an expression that evaluates to an INTEGER
as a bound for the FOR LOOP
condition. Floating numbers will be rounded up or down and alter the original bound. Check MSCEWI3102.
4. Oracle Unsupported Clauses
Oracle allows additional clauses to the FOR LOOP
condition. Like the BY clause for a stepped increment in the condition. And the WHILE and WHEN clause for boolean expressions. These additional clauses are not supported in Snowflake Scripting and are ignored during transformation. Check MSCEWI3101.
FOR i IN 5..15 BY 5 LOOP
NULL;
END LOOP;
/*** MSC-WARNING - MSCEWI3101 - FOR LOOP WITH "BY" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
FOR i IN 5 TO 15 LOOP
NULL;
END LOOP;
5. Unsupported Formats
Oracle allows different types of conditions for a FOR LOOP
. It supports boolean expressions, collections, records... However, Snowflake scripting only supports FOR LOOP
with defined integers as bounds. All other formats are marked as not supported and require additional manual effort to be transformed. Check MSCEWI3103.
Related EWIs
MSCEWI3100: For Loop With Multiple Conditions Is Currently Not Supported By Snowflake Scripting.
MSCEWI3101: Specific For Loop Clause Is Currently Not Supported By Snowflake Scripting.
MSCEWI3102: For Loop With Float Number As Bound May Not Behave Correctly In Snowflake Scripting.
MSCEWI3103: For Loop Format Is Currently Not Supported By Snowflake Scripting.
Last updated
Was this helpful?