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.

Oracle Cursor Syntax

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.

Snowflake Scripting Cursor Syntax

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

Oracle 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;

Snowflake Scripting Cursor Example

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

Oracle Explicit Cursor For Loop Example

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;

Snowflake Scripting Explicit Cursor For Loop Example

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