CURSOR
Translation reference to convert Teradata CURSOR statement to Snowflake Scripting
Description
A cursor is a data structure that is used by stored procedures at runtime to point to a resultset returned by an SQL query. For more information check here.
DECLARE cursor_name [ SCROLL | NO SCROLL ] CURSOR
[
WITHOUT RETURN
|
WITH RETURN [ ONLY ] [ TO [ CALLER | CLIENT ] ]
]
FOR
cursor_specification [ FOR [ READ ONLY | UPDATE ] ]
|
statement_name
;
FETCH [ [ NEXT | FIRST ] FROM ] cursor_name INTO
[ variable_name | parameter_name ] [ ,...n ]
;
OPEN cursor_name
[ USING [ SQL_identifier | SQL_paramenter ] [ ,...n ] ]
;
CLOSE cursor_name ;
Sample Source Patterns
Setup Data
The following code is necessary to execute the sample patterns present in this section.
CREATE TABLE vEmployee(
PersonID INT,
LastName VARCHAR(255),
FirstName VARCHAR(255)
);
CREATE TABLE ResTable(
Column1 VARCHAR(255)
);
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (1, 'Smith', 'Christian');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (2, 'Johnson', 'Jhon');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (3, 'Brown', 'William');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (4, 'Williams', 'Gracey');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (5, 'Garcia', 'Julia');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (6, 'Miller', 'Peter');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (7, 'Davis', 'Jannys');
CREATE TABLE TEST_TABLE (
ColumnA NUMBER,
ColumnB VARCHAR(8),
ColumnC VARCHAR(8));
SELECT * FROM TEST_TABLE;
INSERT INTO TEST_TABLE VALUES (1, '1', '1');
INSERT INTO TEST_TABLE VALUES (2, '2', '2');
Basic Cursor
Teradata
REPLACE PROCEDURE CursorsTest()
BEGIN
DECLARE val1 VARCHAR(255);
DECLARE empcursor CURSOR FOR
SELECT LastName
FROM vEmployee
ORDER BY PersonID;
OPEN empcursor;
FETCH NEXT FROM empcursor INTO val1;
FETCH NEXT FROM empcursor INTO val1;
INSERT INTO ResTable(Column1) VALUES (val1);
CLOSE empcursor;
END;
CALL CursorsTest();
SELECT * FROM ResTable;
Snowflake Scripting
CREATE OR REPLACE PROCEDURE PUBLIC.CursorsTest ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
LET val1 VARCHAR(255);
LET empcursor CURSOR FOR SELECT
LastName
FROM PUBLIC.vEmployee ORDER BY PersonID;
OPEN empcursor;
FETCH NEXT FROM empcursor INTO val1;
FETCH NEXT FROM empcursor INTO val1;
INSERT INTO PUBLIC.ResTable (Column1) VALUES (:val1);
CLOSE empcursor;
END;
$$;
CALL CursorsTest();
SELECT * FROM PUBLIC.ResTable;
Single Returnable Cursor
The following procedure is intended to return one result set since it has the DYNAMIC RESULT SETS 1
property in the header, the cursor has the WITH RETURN
property and is being opened in the body.
Teradata
REPLACE PROCEDURE spSimple ()
DYNAMIC RESULT SETS 1
BEGIN
DECLARE result_set CURSOR WITH RETURN ONLY FOR
SELECT *
FROM vEmployee;
OPEN result_set;
END;
CALL spSimple();
Snowflake Scripting
CREATE OR REPLACE PROCEDURE spSimple ()
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
LET result_set CURSOR FOR SELECT
*
FROM vEmployee;
OPEN result_set;
RETURN TABLE(resultset_from_cursor(result_set));
END;
$$;
CALL spSimple();
Multiple Returnable Cursors
The following procedure is intended to return multiple results when DYNAMIC RESULT SETS
property in the header is greater than 1, the procedure has multiple cursors with the WITH RETURN
property and these same cursors are being opened in the body.
Teradata
REPLACE PROCEDURE spTwoOrMore()
DYNAMIC RESULT SETS 2
BEGIN
DECLARE result_set CURSOR WITH RETURN ONLY FOR
SELECT * FROM SampleTable2;
DECLARE result_set2 CURSOR WITH RETURN ONLY FOR
SELECT Column11 FROM SampleTable1;
OPEN result_set2;
OPEN result_set;
END;
CALL spTwoOrMore();
Snowflake Scripting
CREATE OR REPLACE PROCEDURE PUBLIC.spTwoOrMore ()
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
tbl_result_set VARCHAR;
tbl_result_set2 VARCHAR;
return_arr ARRAY := array_construct();
BEGIN
tbl_result_set := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set) AS
SELECT * FROM PUBLIC.vEmployee;
LET result_set CURSOR FOR
SELECT * FROM IDENTIFIER(?);
tbl_result_set2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set2) AS
SELECT * FROM PUBLIC.TEST_TABLE;
LET result_set2 CURSOR FOR
SELECT * FROM IDENTIFIER(?);
OPEN result_set2 USING (tbl_result_set2);
return_arr := array_append(return_arr, :tbl_result_set2);
OPEN result_set USING (tbl_result_set);
return_arr := array_append(return_arr, :tbl_result_set);
/*** MSC-WARNING - MSCEWI1100 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES ***/
RETURN return_arr;
END;
$$;
CALL spTwoOrMore();
SELECT * FROM "RESULTSET_B5B0005D_1602_48B7_9EE4_62E1A28B000C";
SELECT * FROM "RESULTSET_1371794D_7B77_4DA9_B42E_7981F35CEA9C";
Cursos With Binding Variables
The following cursor uses binding variables as the were condition to perform the query.
Teradata
REPLACE PROCEDURE TestProcedure (IN param1 NUMBER, param2 VARCHAR(8), param3 VARCHAR(8))
DYNAMIC RESULT SETS 1
BEGIN
DECLARE cursorExample CURSOR WITH RETURN ONLY FOR
SELECT * FROM TEST_TABLE
WHERE ColumnA = param1 AND ColumnB LIKE param2 and ColumnC LIKE param3;
OPEN cursorExample;
END;
CALL TestProcedure(2, '2', '2');
Snowflake Scripting
CREATE OR REPLACE PROCEDURE PUBLIC.TestProcedure (PARAM1 FLOAT, PARAM2 STRING, PARAM3 STRING)
RETURNS TABLE(
)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
LET cursorExample CURSOR
FOR
SELECT
* FROM
PUBLIC.TEST_TABLE
WHERE ColumnA = ?
AND ColumnB LIKE ?
and ColumnC LIKE ?;
OPEN cursorExample USING (param1, param2, param3);
RETURN TABLE(resultset_from_cursor(cursorExample));
END;
$$;
CALL PUBLIC.TestProcedure(2, '2', '2');
Cursor For Loop
It is a type of loop that uses a cursor to fetch rows from a SELECT statement and then performs some processing on each row.
Teradata
REPLACE PROCEDURE TestProcedure ()
DYNAMIC RESULT SETS 1
BEGIN
FOR fUsgClass AS cUsgClass CURSOR FOR
SELECT columnA FROM TEST_TABLE
DO
INSERT INTO ResTable(Column1) VALUES (fUsgClass.columnA);
END FOR;
END;
CALL TestProcedure();
SELECT * FROM ResTable;
Snowflake Scripting
CREATE OR REPLACE PROCEDURE TestProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
LET cUsgClass CURSOR
FOR
SELECT
columnA FROM
TEST_TABLE;
Â
FOR fUsgClass IN cUsgClass DO
INSERT INTO ResTable (Column1)
VALUES (:temp_fUsgClass_columnA);
END FOR;
END;
$$;
CALL PUBLIC.TestProcedure();
SELECT * FROM ResTable;
Cursor Fetch inside a Loop
It allows one to retrieve rows from a result set one at a time and perform some processing on each row.
Teradata
REPLACE PROCEDURE teradata_fetch_inside_loop()
DYNAMIC RESULT SETS 1
BEGIN
DECLARE col_name VARCHAR(255);
DECLARE col_int INTEGER DEFAULT 1;
DECLARE cursor_var CURSOR FOR SELECT columnA FROM TEST_TABLE;
WHILE (col_int <> 0) DO
FETCH cursor_var INTO col_name;
INSERT INTO ResTable(Column1) VALUES (cursor_var.columnA);
SET col_int = 0;
END WHILE;
END;
CALL teradata_fetch_inside_loop();
SELECT * FROM ResTable;
Snowflake Scripting
CREATE OR REPLACE PROCEDURE teradata_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
LET col_name VARCHAR(255);
LET col_int INTEGER DEFAULT 1;
LET cursor_var CURSOR
FOR
SELECT
columnA FROM
TEST_TABLE;
WHILE (col_int <> 0) LOOP
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
FETCH cursor_var INTO col_name;
INSERT INTO ResTable (Column1)
VALUES (cursor_var.columnA);
col_int := 0;
END LOOP;
END;
$$;
CALL teradata_fetch_inside_loop();
SELECT * FROM ResTable;
Known Issues
The following parameters are not applicable in Snowflake Scripting.
1. Declare
[ SCROLL/NO SCROLL ] Snowflake Scripting only supports FETCH NEXT.
[ READ-ONLY ] This is the default in Snowflake Scripting.
[ UPDATE ].
2. Fetch
[ NEXT ] This is the default behavior in Snowflake Scripting.
[ FIRST ].
Related EWIs
MSCEWI1081: Snowflake Scripting procedures cannot return more than one result set
MSCCP0003: This statement has usages of cursor for loop
MSCCP0006: Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance
Last updated
Was this helpful?