LOOP
Translation reference to convert Oracle LOOP statement to Snowflake Scripting
Description
With each iteration of the basic
LOOP
statement, its statements run and control returns to the top of the loop. TheLOOP
statement ends when a statement inside the loop transfers control outside the loop or raises an exception. (Oracle PL/SQL Language Reference BASIC LOOP Statement)
LOOP statement... END LOOP [ label ] ;
LOOP
<statement>;
[ <statement>; ... ]
END LOOP [ <label> ] ;
Oracle BASIC LOOP
behavior can also be modified by using the statements:
GOTO
Sample Source Patterns
Loop simple case
This case is functionally equivalent.
Oracle
CREATE TABLE loop_testing_table
(
iterator VARCHAR2(5)
);
CREATE OR REPLACE PROCEDURE loop_procedure
IS
I NUMBER := 1;
J NUMBER := 10;
BEGIN
LOOP
EXIT WHEN I = J;
INSERT INTO loop_testing_table VALUES(TO_CHAR(I));
I := I+1;
END LOOP;
END;
CALL loop_procedure();
SELECT * FROM loop_testing_table;
Snowflake Scripting
CREATE OR REPLACE TABLE loop_testing_table
(
iterator VARCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE PROCEDURE loop_procedure ()
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) := 1;
J NUMBER(38, 18) := 10;
BEGIN
LOOP
IF (:I = :J) THEN
EXIT;
END IF;
INSERT INTO loop_testing_table
VALUES(TO_CHAR(:I));
I := :I +1;
END LOOP;
END;
$$;
CALL loop_procedure();
SELECT * FROM
loop_testing_table;
Known Issues
No issues were found.
Related EWIs
No related EWIs.
Last updated