fbpx

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 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. Therefore, the result can be 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. The SQL statement is executed by opening a cursor, and the cursor is positioned before the first row of the result set.

Depending on the type of cursor declaration you choose, the cursor’s opening and closing 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 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 result set’s previous position, the cursor content can no longer be accessed. 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 repeatedly.

 Updateable vs. not Updateable Cursors

Only cursors declared as updateable can be used to change 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 must 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 essential when iterating over a cursor:

No rows to fetch:  ‘02000’
The cursor is closed: ‘24501’

You can use the SQLSTATE ‘02000’ to determine when you reached the data set’s end.

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 the spool space. Here is the syntax:

CLOSE cursor_name;

  • 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 (
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!

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)
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 the 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

Teradata Stored Procedures – Dynamic Result Sets

Cursors can return complete result sets to the caller, for example, to the SQL assistant. To prepare a stored procedure to return a result set, place the DYNAMIC RESULT SETS n statement before 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;

You mustn’t 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.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • hi Roland,

    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.

    ====ERROR============
    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()

    BEGIN

    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;
    OPEN cur1;

    END;

    • Avatar
      Priyanka V says:

      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

  • Avatar
    MOGILI TERADATA says:

    Hi Suni,

    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?

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >