CURSOR

Description

For more information regarding the Cursor declaration, check here.

This section covers the Translation Reference for Oracle Explicit Cursor. For Oracle Cursor Variables there is no equivalent in Snowflake Scripting.

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

Cursors are pointers that allow users to iterate through query results. For more information on Oracle Cursors check here.

Cursor Definition

CURSOR cursor
 [ ( cursor_parameter_dec [, cursor_parameter_dec ]... )]
   [ RETURN rowtype] IS select_statement ;

Cursor Open

OPEN cursor [ ( cursor_parameter [ [,] actual_cursor_parameter ]... ) ] ;

Cursor Fetch

FETCH { cursor | cursor_variable | :host_cursor_variable }
  { into_clause | bulk_collect_into_clause [ LIMIT numeric_expression ] } ;

Cursor Close

CLOSE { cursor | cursor_variable | :host_cursor_variable } ;

Cursor Attributes

named_cursor%{ ISOPEN | FOUND | NOTFOUND | ROWCOUNT }

Cursor FOR Loop

[ FOR record IN
  { cursor [ ( cursor_parameter_dec
               [ [,] cursor_parameter_dec ]... )]
  | ( select_statement )
  }
    LOOP statement... END LOOP [label] ;

Snowflake Scripting has support for cursors, however, they have fewer functionalities compared to Oracle. To check more information regarding these cursors, check here.

Cursor Declaration

<cursor_name> CURSOR FOR <query>

Cursor Open

OPEN <cursor_name> [ USING (bind_variable_1 [, bind_variable_2 ...] ) ] ;

Cursor Fetch

FETCH <cursor_name> INTO <variable> [, <variable> ... ] ;

Cursor Close

CLOSE <cursor_name> ;

Cursor FOR Loop

FOR <row_variable> IN <cursor_name> DO
    statement;
    [ statement; ... ]
END FOR [ <label> ] ;

Sample Source Patterns

1. Basic cursor example

IN -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE basic_cursor_sample AS
    var1 VARCHAR(20);
    CURSOR cursor1 IS SELECT region_name FROM hr.regions ORDER BY region_name;
BEGIN
    OPEN cursor1;
    FETCH cursor1 INTO var1;
    CLOSE cursor1;
END;
OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE basic_cursor_sample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 VARCHAR(20);
        cursor1 CURSOR
        FOR
            SELECT region_name FROM
                hr.regions
            ORDER BY region_name;
    BEGIN
        OPEN cursor1;
        FETCH cursor1 INTO
            :var1;
    CLOSE cursor1;
    END;
$$;

2. Explicit Cursor For Loop

IN -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE explicit_cursor_for_sample AS
    CURSOR cursor1 IS SELECT region_name FROM hr.regions ORDER BY region_name;
BEGIN
    FOR r1 IN cursor1 LOOP
        NULL;
    END LOOP;
END;
OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE explicit_cursor_for_sample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        cursor1 CURSOR
        FOR
            SELECT region_name FROM
                hr.regions
            ORDER BY region_name;
    BEGIN
                OPEN cursor1;
                --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
                FOR r1 IN cursor1 DO
            NULL;
                END FOR;
                CLOSE cursor1;
    END;
$$;

3. Implicit Cursor For Loop

IN -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE implicit_cursor_for_sample AS
BEGIN
    FOR r1 IN (SELECT region_name FROM hr.regions ORDER BY region_name) LOOP
        NULL;
    END LOOP;
END;
OUT -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE implicit_cursor_for_sample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS