What Is A Cursor In A Teradata SQL Stored Procedure?
In Teradata SQL stored procedures, Cursors are pointers to data records in the result set of an SQL query.
SELECT * FROM table_name;
Cursors iterate over a result set row by row, facilitating record-oriented data processing alongside set-oriented SQL processing. Procedural languages such as C or C++ typically handle record-by-record operations.
Here, query result sets refer to the specific instance when the Teradata spool space contains the results of a query.
Why do we need Cursors in Teradata Stored Procedures?
SQL statements that return only one data record may be issued without the need for cursors. The following serves as an illustration:
SELECT COUNT(*) INTO variable_name FROM table_name;
This SQL query returns only one data record, so a cursor is unnecessary, and the result can be assigned to a stored procedure variable. Consider the following example:
SELECT column_name INTO variable_name FROM table_name;
The second example will only function properly if table_name has a singular row. If multiple rows exist, a run-time error will occur. This is often the initial step in adding cursors to your query processing toolkit.
Cursor States and Positioning
Before use, cursors must be opened. The execution of the SQL statement necessitates the cursor to be opened, which is positioned before the first row of the result set.
The cursor’s opening and closing can be automated based on the chosen declaration type. Further information on various types of cursor declarations will be provided later.
When the stored procedure ends, cursors will be closed permanently. The spool space that contains the query result will be released once the cursor is closed.
Cursors point to result set records and can indicate:
- Before the first position of the result set (in case of a closed cursor)
- After the last status of the result set (in case you iterated over the last row of the result set)
- On any result set row. i.e., the current row
Scroll ability of Cursors
Non-scrollable cursors iterate solely from the first to the final record. Once the last record is reached or the cursor advances beyond the previous position of the result set, content retrieval is no longer feasible. Thus, a scrollable cursor must be declared for multiple utilization of cursor content. Scrollable cursors can be returned to the initial position of the result set. If such a cursor is not closed, its content can be reused infinitely.
Updateable vs. not Updateable Cursors
Only updateable cursors can modify the result set through updates and deletions. These cursors are exclusively available in ANSI transaction mode. It is impossible to generate an updateable cursor Stored Procedure through SQL assistant, which operates in Teradata transaction mode. Instead, you must compile your Stored Procedure using BTEQ in ANSI mode.
Here is the syntax for declaring a cursor in your stored procedure. To use a cursor, it must first be declared in the header section of the procedure:
DECLARE cursor_name [SCROLL|NOSCROLL] CURSOR [[WITHOUT RETURN|WITH RETURN] [ONLY]] [TO CALLER|TO CLIENT] FOR sql_select_statement [FOR [READONLY|UPDATE]];
Opening the Cursor
Once a cursor is declared, it must be opened.
OPEN cursor_name [USING parameter,…];
- The cursor status is changed to open.
- The related SQL statement is executed, and the spool created
- The cursor is located before the first record of the result set.
The USING parameter enables the passing of parameters to the executed SQL statement.
Teradata Stored Procedures – Iterating over the Cursor Data Set
FETCH [NEXT|FIRST] FROM cursor_name INTO [local_variable|parameter];
The FETCH FIRST syntax requires a scrollable cursor declaration. Without it, a compilation error will occur.
The FETCH NEXT statement moves the cursor one row ahead in the data set and assigns variables or parameters to the columns (refer to the INTO section of the statement).
When iterating over a cursor, two SQLSTATES are crucial.
No rows to fetch: ‘02000’
The cursor is closed: ‘24501’
To identify the end of the dataset, utilize the SQLSTATE’ 02000′.
Teradata Stored Procedures – Closing the Cursor
Once the result set is no longer necessary, close the cursor to release the spool space. Refer to the following syntax:
- The cursor status will be set to “CLOSED”
- The spool space is released
Teradata Stored Procedures – Cursor Example using static SQL
CREATE MULTISET TABLE TABLE_NAME (
) PRIMARY INDEX (PK);
INSERT INTO TABLE_NAME VALUES (1);
INSERT INTO TABLE_NAME VALUES (2);
INSERT INTO TABLE_NAME VALUES (3);
REPLACE PROCEDURE CURSOR_SAMPLE ( OUT m INTEGER )
DECLARE c, n INTEGER DEFAULT 0;
DECLARE cursor_name CURSOR FOR SELECT PK FROM TABLE_NAME ORDER BY 1;
FETCH cursor_name INTO c;
IF (SQLSTATE = ‘02000’) THEN
SET n = n + c;
END LOOP label1;
SET m = n;
CALL CURSOR_SAMPLE (x);
Beware of query processing getting stuck in an infinite loop if SQLSTATE’ 02000′ is not checked.
Teradata Stored Procedures – Cursor Example using dynamic SQL
The following demonstration illustrates how to attach SQL statements, contained within character string variables, to cursors:
REPLACE PROCEDURE CURSOR_SAMPLE (IN m INTEGER)
DECLARE n INTEGER DEFAULT 0;
DECLARE my_sql VARCHAR(1000);
DECLARE cursor_name NO SCROLL CURSOR FOR my_statement;
SET my_sql = ‘SELECT PK FROM TABLE_NAME WHERE PK = ‘ || m || ‘;’;
PREPARE my_statement from my_sql;
CALL CURSOR_SAMPLE (2);
The previous example showcased a dynamic SQL cursor with a new PREPARE statement feature. This statement constructs the cursor’s SQL statement from the text.
PREPARE statement FROM ‘statement_string’ || statement_variable;
The cursor declaration mentions the prepared statement but does not include static SQL in the FOR clause.
Is the cursor declaration you have in mind similar to this one:
DECLARE cursor_name FOR ‘SELECT PK FROM TABLE_NAME WHERE PK = ‘ || m || ‘;’;
Please note that this approach is not functional. The FOR clause argument must be a static SQL or prepared statement. The aforementioned example passes the text as an argument, rendering it invalid.
In addition to incorporating variable content directly into the SQL statement text string, you can also utilize the following syntax:
SET my_sql = ‘SELECT PK FROM TABLE_NAME WHERE PK = ? ;’;
PREPARE my_statement from my_sql;
OPEN cursor_name USING m;
The presence of a question mark in a query designates a variable or parameter that needs to be replaced with an existing one when the cursor is opened. If there are multiple entry points of this kind in the SQL statement, the substitution takes place from left to right, using the variables/parameters specified in the USING clause.
Prepared statements should be promptly released upon becoming unnecessary.
DEALLOCATE PREPARE statement_name;
The FOR LOOP CURSOR
Finally, we will present a simpler method for cursor utilization.
FOR loop_variable AS [cursor_name CURSOR FOR]
cursor_sql DO statement
The cursor is automatically declared, its status is changed, and it is positioned.
- The cursor is opened when entering the FOR LOOP
- The cursor is iterated over each record in the result set
- The cursor is closed after leaving the FOR LOOP
- No error handling is needed for the “END OF DATA” situation
Teradata Stored Procedures – Dynamic Result Sets
Cursors can return result sets to the SQL assistant or other callers. To enable a stored procedure to return a result set, including the statement DYNAMIC RESULT SETS n before the first compound statement.
CREATE PROCEDURE MY_PROCEDURE([<PARAMETER> <DATATYPE>][,…])
DYNAMIC RESULT SETS 1 — any number between 1 and 15
DECLARE cursor_name CURSOR WITH RETURN ONLY FOR SELECT PK FROM TABLE_NAME;
OPEN cursor_name; — Do not close, or nothing will be returned!
Please do not close the cursor using a CLOSE statement, as it will prevent the result set from being returned to the client tool. The maximum number of open cursors that can be returned to the client is 15, which will be returned in the order in which they were opened.
I am using tableau to call a stored procedure with resultset for which i need to use cursor. the example of dynamic resultset works well when there are no insert and delete statements. however, i need to make insert and delete statements like the ones listed below. I get following error when i try to compile SP in teradata.
Missing/Invalid SQL statement’E(3707):Syntax error, expected something like an ‘END’ keyword between ‘;’ and the ‘DECLARE’ keyword.’.
Referring to undefined cursor ‘cur1’.
Create PROCEDURE test()
INSERT INTO data_Log ( Desc) VALUES ( ‘insert Started’);
DELETE FROM table1 ;
INSERT INTO table1 select * from table2; — All information is similar
DECLARE cur1 CURSOR WITH RETURN ONLY FOR
select * from table1;
I think the data that has to be returned is a result set, you would need to use “DYNAMIC RESULT SETS 1” as mentioned above
WITH RETURN means it will return the answerset to the client application
WITHOUT RETURN means it will NOT return any answerset/result to client application
but just processes the statements.Generally UPDATE any system tables of TERADATA uses this kind of option.
Ex client application SQL aasistant
Can you explain the use of [[WITHOUT RETURN|WITH RETURN] [ONLY]] [TO CALLER|TO CLIENT] ?
can I get a proper example for the FOR LOOP CURSOR?
what does the loop_variable there, does it need to be declared somewhere else?