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)

Oracle CONTINUE Syntax

CONTINUE [ label ] [ WHEN boolean_expression ] ;

Snowflake Scripting CONTINUE Syntax

{ CONTINUE | ITERATE } [ <label> ] ;

Sample Source Patterns

1. Simple Continue

Code skips the INSERT statement by using CONTINUE.

This case is functionally equivalent.

Oracle

Query

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;

Result

ITERATOR|
--------+

Snowflake Scripting

Query

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

CREATE OR REPLACE PROCEDURE PUBLIC.continue_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;
         CONTINUE;
         INSERT INTO PUBLIC.continue_testing_table_1 VALUES(TO_CHAR(:I));
         END LOOP;
   END;
$$;

CALL PUBLIC.continue_procedure_1();

SELECT * FROM PUBLIC.continue_testing_table_1;

Result

ITERATOR|
--------+

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

Query

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;

Result

ITERATOR|
--------+
1       |
3       |
5       |
7       |
9       |
11      |
13      |
15      |
17      |
19      |
21      |

Snowflake Scripting

Query

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

CREATE OR REPLACE PROCEDURE PUBLIC.continue_procedure_2 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      I NUMBER := 0;
      J NUMBER := 20;
   BEGIN
      WHILE (:I <= :J) LOOP
         I := :I + 1;
         IF ( MOD(:I,2) = 0) THEN
            CONTINUE;
         END IF;
         INSERT INTO PUBLIC.continue_testing_table_2 VALUES(TO_CHAR(:I));
         END LOOP;
   END;
$$;

CALL PUBLIC.continue_procedure_2();

SELECT * FROM PUBLIC.continue_testing_table_2;

Result

ITERATOR|
--------+
1       |
3       |
5       |
7       |
9       |
11      |
13      |
15      |
17      |
19      |
21      |

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

Query

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;

Result

ITERATOR|
--------+
I1      |
K1      |
K2      |
K3      |
K4      |
K5      |
I2      |
I3      |
I4      |
I5      |
I6      |
I7      |
I8      |
I9      |
I10     |
I11     |

Snowflake Scripting

Query

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

CALL PUBLIC.continue_procedure_3();

SELECT * FROM PUBLIC.continue_testing_table_3;

Result

ITERATOR|
--------+
I1      |
K1      |
K2      |
K3      |
K4      |
K5      |
I2      |
I3      |
I4      |
I5      |
I6      |
I7      |
I8      |
I9      |
I10     |
I11     |

Known Issues

No issues were found.

Related EWIs

No related EWIs.