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)

Oracle EXIT Syntax

EXIT [ label ] [ WHEN boolean_expression ] ;

Snowflake Scripting EXIT Syntax

{ 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

Query

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;

Result

ITERATOR|
--------+

Snowflake Scripting

Query

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;

Result

ITERATOR|
--------+

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

Query

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;

Result

ITERATOR|
--------+
1       |
2       |
3       |
4       |
5       |
6       |

Snowflake Scripting

Query

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
        WHILE (:I <= :J) LOOP
            IF (:I > 5) THEN
                EXIT;
            END IF;
            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;

Result

ITERATOR|
--------+
1       |
2       |
3       |
4       |
5       |
6       |

3. Exit with label and condition

Code breaks both loops by using the EXIT statement pointing to the outer loop.

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 TABLE exit_testing_table_3 (
    iterator VARCHAR2(5)
);

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

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

        K := 0;
    END LOOP out_loop; 
END;

CALL exit_procedure_3();
SELECT * FROM exit_testing_table_3;

Result

ITERATOR|
--------+
I1      |
K1      |
K2      |
K3      |
K4      |
K5      |

Snowflake Scripting

Query

OUT -> Oracle_03.sql

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

CREATE OR REPLACE PROCEDURE exit_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 exit_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
                    EXIT out_loop;
                END IF;
                       INSERT INTO exit_testing_table_3
                VALUES('K' || NVL(TO_CHAR(:K) :: STRING, ''));
                   END LOOP in_loop;
            K := 0;
               END LOOP out_loop;
    END;
$$;

CALL exit_procedure_3();

SELECT * FROM
    exit_testing_table_3;

Result

ITERATOR|
--------+
I1      |
K1      |
K2      |
K3      |
K4      |
K5      |

Known Issues

No issues were found.

Related EWIs

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