Snowflake Documentation Home

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;