EXIT

Translation reference to convert Oracle EXIT statement to Snowflake Scripting

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

Description

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

EXIT [ label ] [ WHEN boolean_expression ] ;
{ BREAK | EXIT } [ <label> ] ;

Sample Source Patterns

Note that you can change EXITwith BREAKand everything will work the same.

1. Simple Exit

Code skips the INSERT statement by using EXIT.

This case is functionally equivalent.

Oracle

IN -> Oracle_01.sql
CREATE TABLE exit_testing_table_1 (
    iterator VARCHAR2(5)
);

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

CALL exit_procedure_1();
SELECT * FROM exit_testing_table_1;

Snowflake Scripting

OUT -> Oracle_01.sql
CREATE OR REPLACE TABLE exit_testing_table_1 (
       iterator VARCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE exit_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;
            EXIT;
            INSERT INTO exit_testing_table_1
            VALUES(TO_CHAR(:I));
        END LOOP;
    END;
$$;

CALL exit_procedure_1();

SELECT * FROM
    exit_testing_table_1;

2. Exit with condition

Code exits the loop when the iterator is greater than 5.

This case is functionally equivalent by turning the condition into an IF statement.

Oracle

IN -> Oracle_02.sql
CREATE TABLE exit_testing_table_2 (
    iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE exit_procedure_2
IS
I NUMBER := 0;
J NUMBER := 20;
BEGIN
    WHILE I <= J LOOP
        EXIT WHEN I > 5;
        I := I + 1;
        INSERT INTO exit_testing_table_2 VALUES(TO_CHAR(I)); 
    END LOOP;  
END;

CALL exit_procedure_2();
SELECT * FROM exit_testing_table_2;

Snowflake Scripting

OUT -> Oracle_02.sql
CREATE OR REPLACE TABLE exit_testing_table_2 (
       iterator VARCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE exit_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