CONTINUE

Translation reference to convert Oracle CONTINUE statement to Snowflake Scripting

Description

The CONTINUE statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the next iteration of either the current loop or an enclosing labeled loop. (Oracle PL/SQL Language Reference CONTINUE Statement)

Some parts in the output code are omitted for clarity reasons.

Oracle CONTINUE Syntax

CONTINUE [ label ] [ WHEN boolean_expression ] ;

Snowflake Scripting CONTINUE Syntax

{ CONTINUE | ITERATE } [ <label> ] ;

Sample Source Patterns

1. Simple Continue

Code skips the INSERT statement by using CONTINUE.

This case is functionally equivalent.

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE continue_testing_table_1 (iterator VARCHAR2(5));

CREATE OR REPLACE PROCEDURE continue_procedure_1 
IS
I NUMBER := 0;
J NUMBER := 20;
BEGIN
    WHILE I <= J LOOP 
        I := I + 1;
        CONTINUE;
        INSERT INTO continue_testing_table_1
        VALUES (TO_CHAR(I));
    END LOOP;
END;

CALL continue_procedure_1();
SELECT * FROM continue_testing_table_1;

Result

ITERATOR|
--------+

Snowflake Scripting

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE continue_testing_table_1 (iterator VARCHAR(5))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE continue_procedure_1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I NUMBER(38, 18) := 0;
        J NUMBER(38, 18) := 20;
    BEGIN
        WHILE (:I <= :J) LOOP
            I := :I + 1;
            CONTINUE;
            INSERT INTO continue_testing_table_1
            VALUES (TO_CHAR(:I));
        END LOOP;
    END;
$$;

CALL continue_procedure_1();

SELECT * FROM
    continue_testing_table_1;

Result

ITERATOR|
--------+

2. Continue with condition

Code skips inserting even numbers by using CONTINUE.

This case is not functionally equivalent, but, you can turn the condition into an IF statement.

Oracle

Query

IN -> Oracle_02.sql

CREATE TABLE continue_testing_table_2 (iterator VARCHAR2(5));

CREATE OR REPLACE PROCEDURE continue_procedure_2
IS
I NUMBER := 0;
J NUMBER := 20;
BEGIN
    WHILE I <= J LOOP
        I := I + 1;
        CONTINUE WHEN MOD(I,2) = 0;
        INSERT INTO continue_testing_table_2 VALUES(TO_CHAR(I));
    END LOOP;  
END;

CALL continue_procedure_2();
SELECT * FROM continue_testing_table_2;

Result

ITERATOR|
--------+
1       |
3       |
5       |
7       |
9       |
11      |
13      |
15      |
17      |
19      |
21      |

Snowflake Scripting

Query

OUT -> Oracle_02.sql

CREATE OR REPLACE TABLE continue_testing_table_2 (iterator VARCHAR(5))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE continue_procedure_2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I NUMBER(38, 18) := 0;
        J NUMBER(38, 18) := 20;
    BEGIN
        WHILE (:I <= :J) LOOP
            I := :I + 1;
            IF (MOD(:I,2) = 0) THEN
                CONTINUE;
            END IF;
            INSERT INTO continue_testing_table_2
            VALUES(TO_CHAR(:I));
        END LOOP;
    END;
$$;

CALL continue_procedure_2();

SELECT * FROM
    continue_testing_table_2;

Result

ITERATOR|
--------+
1       |
3       |
5       |
7       |
9       |
11      |
13      |
15      |
17      |
19      |
21      |

3. Continue with label and condition

Code skips line 19, and the inner loop is only executed once because the CONTINUE is always jumping to the outer loop using the label.

This case is functionally equivalent applying the same process as the previous sample.

Note that labels are going to be commented out.

Oracle

Query

IN -> Oracle_03.sql

CREATE OR REPLACE PROCEDURE continue_procedure_3
IS
I NUMBER := 0;
J NUMBER := 10;
K NUMBER := 0;
BEGIN
    <<out_loop>>
    WHILE I <= J LOOP
        I := I + 1;
        INSERT INTO continue_testing_table_3 VALUES('I' || TO_CHAR(I));

        <<in_loop>>
        WHILE K <= J * 2 LOOP
            K := K + 1;
            CONTINUE out_loop WHEN K > J / 2;
            INSERT INTO continue_testing_table_3 VALUES('K' || TO_CHAR(K));
        END LOOP in_loop;

        K := 0;
    END LOOP out_loop; 
END;

CALL continue_procedure_3();
SELECT * FROM continue_testing_table_3;

Result

ITERATOR|
--------+
I1      |
K1      |
K2      |
K3      |
K4      |
K5      |
I2      |
I3      |
I4      |
I5      |
I6      |
I7      |
I8      |
I9      |
I10     |
I11     |

Snowflake Scripting

Query

OUT -> Oracle_03.sql

CREATE OR REPLACE PROCEDURE continue_procedure_3 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I NUMBER(38, 18) := 0;
        J NUMBER(38, 18) := 10;
        K NUMBER(38, 18) := 0;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<out_loop>> ***/!!!
        WHILE (:I <= :J) LOOP
            I := :I + 1;
                   INSERT INTO continue_testing_table_3
            VALUES('I' || NVL(TO_CHAR(:I) :: STRING, ''));
            !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<in_loop>> ***/!!!
            WHILE (:K <= :J * 2) LOOP
                K := :K + 1;
                IF (:K > :J / 2) THEN
                    CONTINUE out_loop;
                END IF;
                       INSERT INTO continue_testing_table_3
                VALUES('K' || NVL(TO_CHAR(:K) :: STRING, ''));
                   END LOOP in_loop;
            K := 0;
               END LOOP out_loop;
    END;
$$;

CALL continue_procedure_3();

SELECT * FROM
    continue_testing_table_3;

Result

ITERATOR|
--------+
I1      |
K1      |
K2      |
K3      |
K4      |
K5      |
I2      |
I3      |
I4      |
I5      |
I6      |
I7      |
I8      |
I9      |
I10     |
I11     |

Known Issues

No issues were found.

Related EWIs

1. SSC-EWI-0094: Label declaration not supported.