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;

teradata stored procedure cursor

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.

 Cursor Declaration

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:

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

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

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

END FOR;

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

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;

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.

  • 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

    >