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.

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

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;

CREATE OR REPLACE PROCEDURE basic_cursor_sample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    var1 STRING;
    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

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;

CREATE OR REPLACE PROCEDURE explicit_cursor_for_sample ()
RETURNS VARCHAR
LANGUAGE SQL
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;
    /*** MSC-WARNING - MSCCP0003 - 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;

    FOR r1 IN cursor1 DO
      NULL;
    END FOR;
  END;
$$;

3. Implicit Cursor For Loop

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;

CREATE OR REPLACE PROCEDURE implicit_cursor_for_sample  ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    LET temporary_for_cursor_0 CURSOR FOR (SELECT region_name FROM hr.regions ORDER BY region_name);
    /*** MSC-WARNING - MSCCP0003 - 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

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;

CREATE OR REPLACE PROCEDURE parameterized_cursor_for_sample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    cursor1 CURSOR FOR 
      SELECT region_name FROM hr.regions WHERE region_id BETWEEN ? AND ?;
  BEGIN
    OPEN cursor1 USING (3, 5);
    CLOSE cursor1;
  END;
$$;

4.2 Parameterized Cursors With Multiple Sending Parameters

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;

CREATE OR REPLACE PROCEDURE parameterized_cursor_for_sample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    cursor1 CURSOR FOR
      SELECT region_name FROM hr.regions 
      WHERE region_id BETWEEN ? AND ? OR ? < 0;
  BEGIN
    OPEN cursor1 USING (3, 5, 3);
    OPEN cursor1 USING (3, 7, 3);
    OPEN cursor1 USING (2, 7, 2);
    OPEN cursor1 USING (5, 15, 5);
    OPEN cursor1 USING (2, 15, 2);
    CLOSE cursor1;
  END;
$$;

4.3 Parameterized Cursors With Use Of Procedure Parameters In Query

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

CREATE OR REPLACE PROCEDURE parameterized_cursor_for_sample (high_param NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    cursor1 CURSOR FOR 
      SELECT region_name FROM hr.regions WHERE region_id BETWEEN ? AND ?;
  BEGIN
    OPEN cursor1 USING (3, high_param);
    CLOSE cursor1;
  END;
$$;
CALL PUBLIC.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

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;

CREATE OR REPLACE PROCEDURE PUBLIC.p_cursors_for_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    datePlusOne TIMESTAMP;
    c_product CURSOR FOR 
      SELECT OBJECT_CONSTRUCT('NAME', name, 'PRICE', price, 'CREATE_ON', create_on::DATE) sc_cursor_record 
      FROM PUBLIC.products WHERE price BETWEEN ? AND ?;
  BEGIN
    OPEN c_product USING (3, 5);
    /*** MSC-WARNING - MSCCP0003 - 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 := :record_product:CREATE_ON::DATE + 1;
      INSERT INTO PUBLIC.sold_items
        SELECT
          :record_product:NAME,
          :record_product:PRICE,
          :datePlusOne;
    END FOR;
    CLOSE c_product;
  END;
$$;

5.2 Cursors Fetch

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;

CREATE OR REPLACE PROCEDURE PUBLIC.p_cursors_fetch ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    c_product CURSOR FOR 
      SELECT OBJECT_CONSTRUCT(*) sc_cursor_record 
      FROM PUBLIC.products WHERE price BETWEEN ? AND ?;
  BEGIN
    OPEN c_product USING (3, 5);
    LOOP
     --** MSC-WARNING - MSCCP0006 - 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 PUBLIC.sold_items
        SELECT
         :record_product:NAME,
         :record_product:PRICE;
      INSERT INTO PUBLIC.sold_items
        SELECT
         :record_product:NAME,
         :record_product:PRICE;
    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

MSCEWI3106: Parametrized Cursor is not supported by Snowflake Scripting.
: For Loop and Fetch into a variable cannot be used on the same cursor.
MSCCP0003: This statement has usages of cursor for loop.
MSCCP0006: Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance.

PreviousCREATE PROCEDURE NextWorkaround for cursors using parameters or procedure variables

Last updated 1 year ago

Description

Cursor Definition

Cursor Open

Cursor Fetch

Cursor Close

Cursor Attributes

Cursor FOR Loop

Cursor Declaration

Cursor Open

Cursor Fetch

Cursor Close

Cursor FOR Loop

Sample Source Patterns

1. Basic cursor example

2. Explicit Cursor For Loop

3. Implicit Cursor For Loop

4. Parameterized Cursor

4.1 Basic Cursor Parameterized Example

4.2 Parameterized Cursors With Multiple Sending Parameters

4.3 Parameterized Cursors With Use Of Procedure Parameters In Query

5. Using Cursors In Fetch And For Loop

5.1 Cursors For Loop

5.2 Cursors Fetch

Known Issues

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

2. OPEN statement cannot pass values for declared arguments

3. FETCH statement cannot use records

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

5. Cursor attributes do not exist in Snowflake Scripting

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

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

Related EWIs