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.

CONTINUE [ label ] [ WHEN boolean_expression ] ;
{ CONTINUE | ITERATE } [ <label> ] ;

Sample Source Patterns

1. Simple Continue

Code skips the INSERT statement by using CONTINUE.

This case is functionally equivalent.

Oracle

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;

Snowflake Scripting

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;

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

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;

Snowflake Scripting

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;

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

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;

Snowflake Scripting

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;

Known Issues

No issues were found.

Related EWIs

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

PreviousCOMPOUND STATEMENTSNextCREATE PROCEDURE

Last updated