EXIT

Translation reference to convert Oracle EXIT statement to Snowflake Scripting

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

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

CREATE OR REPLACE TABLE PUBLIC.exit_testing_table_1 (
iterator VARCHAR(5));

CREATE OR REPLACE PROCEDURE PUBLIC.exit_procedure_1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      I NUMBER := 0;
      J NUMBER := 20;
   BEGIN
      WHILE (:I <= :J) LOOP
         I := :I + 1;
         EXIT;
         INSERT INTO PUBLIC.exit_testing_table_1 VALUES(TO_CHAR(:I));
         END LOOP;
   END;
$$;

CALL PUBLIC.exit_procedure_1();

SELECT * FROM PUBLIC.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

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

CREATE OR REPLACE TABLE PUBLIC.exit_testing_table_2 (
iterator VARCHAR(5));

CREATE OR REPLACE PROCEDURE PUBLIC.exit_procedure_2 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      I NUMBER := 0;
      J NUMBER := 20;
   BEGIN
      WHILE (:I <= :J) LOOP
         IF (:I > 5) THEN
            EXIT ;
         END IF;
         I := :I + 1;
         INSERT INTO PUBLIC.exit_testing_table_2 VALUES(TO_CHAR(:I));
         END LOOP;
   END;
$$;

CALL PUBLIC.exit_procedure_2();

SELECT * FROM PUBLIC.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

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

CREATE OR REPLACE TABLE PUBLIC.exit_testing_table_3 (
iterator VARCHAR(5));

CREATE OR REPLACE PROCEDURE PUBLIC.exit_procedure_3 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      I NUMBER := 0;
      J NUMBER := 10;
      K NUMBER := 0;
   BEGIN
      /*** MSC-WARNING - MSCEWI3107 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      /*<<out_loop>>*/
      WHILE (:I <= :J) LOOP
         I := :I + 1;
         INSERT INTO PUBLIC.exit_testing_table_3 VALUES('I' || NVL( TO_CHAR(:I) :: STRING, ''));
         /*** MSC-WARNING - MSCEWI3107 - 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 PUBLIC.exit_testing_table_3 VALUES('K' || NVL( TO_CHAR(:K) :: STRING, ''));
         END LOOP in_loop;
         K := 0;
      END LOOP out_loop;
   END;
$$;

CALL PUBLIC.exit_procedure_3();

SELECT * FROM PUBLIC.exit_testing_table_3;

Result

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

Known Issues

No issues were found.

Related EWIs

No related EWIs.