Mistakes To Avoid When Using Cursors in Teradata Stored Procedures
What is A Cursor In A Teradata SQL Stored Procedure?
When we are talking about Cursors in Teradata SQL stored procedures, we refer to a pointer to a data record in the result set of an SQL query:
SELECT * FROM table_name;
Cursors are used to iterate over a result set from one row to the next. They are a means to perform record oriented data processing in the context of set-oriented data processing implemented with SQL. Record-to record activity is usually the realm of a procedural language like C or C++.
When we talk about query result sets here, we refer to Teradata spool space holding the result set of a query at one particular point in time.
Why do we need Cursors in Teradata Stored Procedures?
Without the usage of cursors, only SQL statements returning exactly one data record can be issued. Here is such an example:
SELECT COUNT(*) INTO variable_name FROM table_name;
The above SQL returns exactly one data record. The result, therefore, can be easily assigned to a stored procedure variable, and no cursor is needed. Now let us take a look at another example:
SELECT column_name INTO variable_name FROM table_name;
The second example only will work if table_name contains exactly one row. Otherwise, a run-time error is thrown. Typically a point of departure for the introduction of cursors to your query processing arsenal.
Cursor States and Positioning
Cursors have to be opened before they can be used. By opening a cursor, the SQL statement is executed and the cursor positioned before the first row of the result set.
Depending on the type of cursor declaration you choose, opening and closing of the cursor may be done automatically. We will get into the details about different types of cursor declarations later.
Cursors are always closed when the stored procedure terminates. As soon as a cursor is closed, the attached spool space holding the query result set will be released.
Cursors are pointers to the result set records and can be pointing to:
- 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
Not scrollable cursors can only be iterated forward from the first to the last record. As soon as you reach the last record, or moved after the last position of the result set, the cursor content cannot be accessed anymore. Therefore, whenever you need to reuse cursor content, you have to declare it scrollable. Scrollable cursors can be moved back to the first position of the result set. As long as you don't close such a cursor, its content can be reused over and over again.
Updateable vs. not Updateable Cursors
Only cursors declared as updateable can be used to change data records of the result set (update and delete). Updateable cursors can only be reported in ANSI transaction mode. You cannot create a Stored Procedure with an updateable cursor from your SQL assistant as SQL assistant runs in Teradata transaction mode. You will have to compile your stored procedure from BTEQ in ANSI mode.
Below you can find the syntax you have to use to declare a cursor in your stored procedure. Before a cursor can be utilized, it has to be stated in the header section of the stored 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
After having declared a cursor, it has to 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 allows you to pass parameters into 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 will only work if a scrollable cursor is declared. Otherwise, you will receive a compilation error.
FETCH NEXT moves the cursor one position forward through the data set and assigns the columns to variables or parameters (see the INTO part of the statement).
There are two SQLSTATES which are important when iterating over a cursor:
No rows to fetch: ‘02000’
Cursor is closed: ‘24501’
You can use the SQLSTATE ‘02000' to determine when you reached the end of the data set.
Teradata Stored Procedures – Closing the Cursor
As soon as you don't need the result set anymore, the cursor should be closed to free up spool space. Here is the 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);
Please be aware that without checking for SQLSTATE ‘02000', query processing could get lost in an endless loop !
Teradata Stored Procedures – Cursor Example using dynamic SQL
This example will show you how SQL statements stored in character string variables can be attached 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);
In the dynamic SQL cursor example above we introduced a new functionality: the PREPARE statement, which builds the cursor SQL statement from a text:
PREPARE statement FROM ‘statement_string' || statement_variable;
The cursor declaration does not contain static SQL in the FOR clause but mentions the prepared statement.
Do you have a cursor declaration like this one below in mind?
DECLARE cursor_name FOR ‘SELECT PK FROM TABLE_NAME WHERE PK = ‘ || m || ‘;';
Note that this will not work. The argument of the FOR clause needs to be a static SQL statement or a prepared statement. The example above would pass text as an argument and is invalid.
Apart from mixing variable content directly into the SQL statement text string, you could as well use this kind of syntax:
SET my_sql = ‘SELECT PK FROM TABLE_NAME WHERE PK = ? ;';
PREPARE my_statement from my_sql;
OPEN cursor_name USING m;
As question mark in the query indicates the place for a variable or parameter. When opening the cursor, you have to substitute it with an existing variable or parameter. In case you use several such entry points in the SQL statement text, variables are replaced from left to right by the variables/parameters passed in the USING clause.
Prepared statements should be released as soon as not needed anymore:
DEALLOCATE PREPARE statement_name;
The FOR LOOP CURSOR
Last but not least, we will introduce a much easier way of using cursors:
FOR loop_variable AS [cursor_name CURSOR FOR]
cursor_sql DO statement
Cursor declaration, status change, and positioning are handled automatically:
- 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 needed for the “END OF DATA” situation
Teradata Stored Procedures – Dynamic Result Sets
Cursors can be used to return complete result sets to the caller, for example to the SQL assistant. To prepare a stored procedure to return a result set must put the DYNAMIC RESULT SETS n statement before the BEGIN of 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!
It is important that you do not close the cursor with a CLOSE statement. Otherwise, the result set will not be returned to the client tool. You can return up to 15 open cursors to the client. Cursors will be returned in the same order they have been opened.