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
                OBJECT_CONSTRUCT('REGION_NAME', region_name) sc_cursor_record 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
            LET r1 OBJECT := r1.sc_cursor_record;
            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 CALLER
AS
$$
    BEGIN
        LET temporary_for_cursor_0 CURSOR
        FOR
            (SELECT region_name FROM
                    hr.regions
                ORDER BY region_name);
        --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
        FOR r1 IN temporary_for_cursor_0 DO
            NULL;
        END FOR;
    END;
$$;

4. Parameterized Cursor

You can use "?" In the filter condition of the cursor at the declaration section define the bind variable. While opening the cursor we can add the additional syntax “USING <bind_variable_1 >” to pass the bind variable.

Below are some examples of scenarios that can occur in the use of parameters in cursors:

4.1 Basic Cursor Parameterized Example

IN -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE parameterized_cursor_for_sample AS
    CURSOR cursor1 (low number, high IN number) IS
        SELECT region_name FROM hr.regions WHERE region_id BETWEEN low AND high;
BEGIN
    OPEN cursor1(3,5);
    CLOSE cursor1;
END;
OUT -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE parameterized_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
            WHERE region_id BETWEEN low AND high;
    BEGIN
                OPEN cursor1;
                CLOSE cursor1;
    END;
$$;

4.2 Parameterized Cursors With Multiple Sending Parameters

IN -> Oracle_05.sql
CREATE OR REPLACE PROCEDURE parameterized_cursor_for_sample AS
    CURSOR cursor1 (low number DEFAULT 2, high IN number DEFAULT 7) IS
        SELECT region_name FROM hr.regions 
        WHERE region_id BETWEEN low AND high OR low < 0;
BEGIN
    OPEN cursor1(3,5);
    OPEN cursor1(3);
    OPEN cursor1;
    OPEN cursor1(high => 15, low => 5);
    OPEN cursor1(high => 15);
    CLOSE cursor1;
END;
OUT -> Oracle_05.sql
CREATE OR REPLACE PROCEDURE parameterized_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
            WHERE region_id BETWEEN low AND high OR low < 0;
    BEGIN
                OPEN cursor1;
                OPEN cursor1;
                OPEN cursor1;
                OPEN cursor1;
                OPEN cursor1;
                CLOSE cursor1;
    END;
$$;

4.3 Parameterized Cursors With Use Of Procedure Parameters In Query

IN -> Oracle_06.sql
CREATE OR REPLACE PROCEDURE parameterized_cursor_for_sample (high_param number) AS
    CURSOR cursor1 (low number DEFAULT 2) IS
        SELECT region_name FROM hr.regions 
        WHERE region_id BETWEEN low AND high_param;
BEGIN
    OPEN cursor1(3);
    CLOSE cursor1;
END;
CALL parameterized_cursor_for_sample(5);
OUT -> Oracle_06.sql
CREATE OR REPLACE PROCEDURE parameterized_cursor_for_sample (high_param NUMBER(38, 18))
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
            WHERE region_id BETWEEN low AND ?;
    BEGIN
                OPEN cursor1 USING (high_param);
                CLOSE cursor1;
    END;
$$;

CALL parameterized_cursor_for_sample(5);

5. Using Cursors In Fetch And For Loop

Cursors can be controlled through the use of the FOR statement, allowing each and every record of a cursor to be processed while the FETCH statement puts, record by record, the values returned by the cursor into a set of variables, which may be PLSQL records

5.1 Cursors For Loop

IN -> Oracle_07.sql
CREATE OR REPLACE PROCEDURE p_cursors_for_loop AS
 datePlusOne TIMESTAMP;
 CURSOR c_product(low number, high number) IS 
    SELECT name, price, create_on FROM products WHERE price BETWEEN low AND high;
BEGIN
    FOR record_product IN c_product(3,5)
    LOOP
      datePlusOne := record_product.create_on + 1;
      INSERT INTO sold_items values(record_product.name, record_product.price, datePlusOne);
    END LOOP;
END;
OUT -> Oracle_07.sql
CREATE OR REPLACE PROCEDURE p_cursors_for_loop ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  datePlusOne TIMESTAMP(6);
  c_product CURSOR
  FOR
     SELECT
      OBJECT_CONSTRUCT('NAME', name, 'PRICE', price, 'CREATE_ON', create_on) sc_cursor_record FROM
      products
     WHERE price BETWEEN low AND high;
 BEGIN
  OPEN c_product;
  --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
  FOR record_product IN c_product DO
     LET record_product OBJECT := record_product.sc_cursor_record;
     datePlusOne :=
                    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
                    record_product.CREATE_ON + 1;
                    INSERT INTO sold_items
                    SELECT
      :record_product:NAME,
      :record_product:PRICE,
      :datePlusOne;
  END FOR;
  CLOSE c_product;
 END;
$$;

5.2 Cursors Fetch

IN -> Oracle_08.sql
CREATE OR REPLACE PROCEDURE p_cursors_fetch AS
 record_product products%rowtype;
 CURSOR c_product(low number, high number) IS 
    SELECT * FROM products WHERE price BETWEEN low AND high;
BEGIN
    OPEN c_product(3,5);
    LOOP
        FETCH c_product INTO record_product;
        EXIT WHEN c_product%notfound;
        INSERT INTO sold_items VALUES (record_product.name, record_product.price);
        INSERT INTO sold_items VALUES record_product;
    END LOOP;
    CLOSE c_product;
END;
OUT -> Oracle_08.sql
CREATE OR REPLACE PROCEDURE p_cursors_fetch ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  record_product OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
  c_product CURSOR
  FOR
     SELECT
      OBJECT_CONSTRUCT( *) sc_cursor_record FROM
      products
     WHERE price BETWEEN low AND high;
 BEGIN
  OPEN c_product;
  LOOP
     --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
     FETCH c_product INTO
      :record_product;
     IF (record_product IS NULL) THEN
      EXIT;
     END IF;
     INSERT INTO sold_items
     SELECT
      :record_product:NAME,
      :record_product:PRICE;
     INSERT INTO sold_items
     SELECT
      null /*** SSC-FDM-OR0024 - COLUMNS FROM EXPRESSION products%rowtype NOT FOUND ***/;
  END LOOP;
  CLOSE c_product;
 END;
$$;

Known Issues

1. RETURN clause is not supported in Snowflake Scripting Cursor Declaration

The Cursor Declaration for Snowflake Scripting does not include this clause. It can be removed from the Oracle Cursor definition to get functional equivalence.

2. OPEN statement cannot pass values for declared arguments

Even though arguments can be declared for a cursor, their values cannot be assigned in Snowflake Scripting. The best alternative is to use the USING clause with bind variables.

3. FETCH statement cannot use records

Snowflake Scripting does not support records. However, it is possible to migrate them using the OBJECT data type and the OBJECT_CONSTRUCT() method. For more information please see the Record Type Definition Section.

4. FETCH BULK COLLECT INTO clause is not supported in Snowflake Scripting

Snowflake Scripting does not support the BULK COLLECT INTO clause. However, it is possible to use ARRAY_AGG along with a temporal table to construct a new variable with the data corresponding to the Cursor information. For more information please see the Collection Bulk Operations Section.

5. Cursor attributes do not exist in Snowflake Scripting

Oracle cursors have different attributes that allow the user to check their status like if it is opened or the amount of fetched rows, however, these attributes regarding the cursor status do not exist in Snowflake Scripting.

6. The cursor's query does not have access to the procedure's variables and parameters

In Oracle, the query in the cursor declaration has access to procedure variables and parameters but in Snowflake Scripting, it does not. The alternative to this is to use the USING clause with bind variables. For more information check this section.

7. %NOTFOUND attribute is not supported in Snowflake Scripting Cursor

In Oracle can be used, before the first fetch from an open cursor, cursor_name%NOTFOUND returns TRUE if the last fetch failed to return a row, or FALSE if the last fetch returned a row. Snowflake Scripting does not support the use of this attribute instead it can be validated if the variable assigned to the cursor result contains values

Related EWIs

PreviousCREATE PROCEDURENextPARAMETRIZED CURSOR

Last updated