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;

 

teradata stored procedure cursor

 

 

 

 

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.

 Cursor Declaration

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.

 

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.

 

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:

CLOSE cursor_name;

  • The cursor status will be set to “CLOSED”
  • The spool space is released

 

Cursor Example using static SQL

 

CREATE MULTISET TABLE TABLE_NAME (
PK INTEGER
) 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 )
BEGIN
DECLARE c, n INTEGER DEFAULT 0;

DECLARE cursor_name CURSOR FOR SELECT PK FROM TABLE_NAME  ORDER BY 1;

OPEN cursor_name;

label1:

LOOP
FETCH cursor_name INTO c;
IF (SQLSTATE = ‘02000’) THEN

  LEAVE label1;
END IF;

SET n = n + c;

END LOOP label1;
CLOSE cursor_name;
SET m = n;

END;

CALL CURSOR_SAMPLE  (x);

Please be aware that without checking for SQLSTATE ‘02000’, query processing could get lost in an endless loop !

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

 OPEN cursor_name;

END;

 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

END FOR;

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

 

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

BEGIN

DECLARE cursor_name CURSOR WITH RETURN ONLY FOR SELECT PK FROM TABLE_NAME;

OPEN cursor_name; — Do not close or nothing will be returned!

END;

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.

Our Reader Score
[Total: 30    Average: 3.9/5]
Teradata SQL Stored Procedures – Cursors written by Roland Wenzlofsky on October 25, 2014 average rating 3.9/5 - 30 user ratings

2 COMMENTS

  1. can I get an proper example for the FOR LOOP CURSOR. ???
    what does the loop_variable there, does it needs to be declared somewhere else?

LEAVE A REPLY

Please enter your comment!
Please enter your name here