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
CREATEOR 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
CREATEOR REPLACE PROCEDURE basic_cursor_sample ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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 LOOPNULL; END LOOP;END;
OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE explicit_cursor_for_sample ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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 DONULL; END FOR; CLOSE cursor1; END;$$;
3. Implicit Cursor For Loop
IN -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE implicit_cursor_for_sample ASBEGIN FOR r1 IN (SELECT region_name FROM hr.regions ORDER BY region_name) LOOPNULL; END LOOP;END;
OUT -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE implicit_cursor_for_sample ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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 DONULL; 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 INnumber) 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 VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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
IN -> Oracle_05.sql
CREATEOR 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 VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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
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 VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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 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 VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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 ? AND ?; BEGIN OPEN c_product USING (3, 5);--** 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 VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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 ? AND ?; BEGIN OPEN c_product USING (3, 5); 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 SELECTnull !!!RESOLVE EWI!!! /*** SSC-EWI-OR0002 - 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
SSC-EWI-0036: Data type converted to another data type.