Cursors and Stored Procedures | VantageCloud Lake - Cursors and Stored Procedures - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

General Rules

No more than 15 cursors can be open at any one time in a stored procedure.

Construct cursor names from the following list of valid characters:
  • Uppercase letters
  • Lowercase letters
  • $
  • @
  • #
  • Digits
  • Underscores
The following statements with open cursors are not allowed in a stored procedure:
  • POSITION
  • REWIND
  • SQL transaction statements

DECLARE CURSOR Statement and FOR Statement Cursors

  • You can declare cursors in a DECLARE CURSOR statement or FOR loop control statement.
  • The SELECT statement you specify in the FOR statement or DECLARE CURSOR (stored procedures form) statement is called the cursor specification.
  • You can use both positioned and non-positioned cursors in a stored procedure.
  • Cursors declared in a FOR statement and in a DECLARE CURSOR statement differ in the following ways.
FOR Loop Cursor DECLARE CURSOR Cursor
The scope of the cursor is confined to the defining FOR statement.

The scope of column name or its correlation name in a FOR loop cursor is restricted to the body of the FOR statement.

In a nested FOR statement, you can reference the cursor name you specify in an outer FOR statement in statements inside any inner FOR statements.

 The scope of the cursor is the declaring BEGIN END compound statement.

The scope of a cursor is the compound statement and its nested compound statements, if any.

In nested compound statements, the scope of a cursor you specify in an outer compound statement includes all the inner compound statements.
A positioned DELETE or UPDATE statement referencing the cursor makes the cursor updatable. The FOR UPDATE option makes the cursor updatable.
OPEN, FETCH and CLOSE take place implicitly as part of the FOR loop execution.

Each iteration of the FOR statement fetches the next row, if any, for an open cursor.

 You must explicitly specify OPEN, FETCH or CLOSE.

If you specify CLOSE for a result set cursor, the result set is not returned.
You can label FOR statements in which cursors are declared. You cannot label DECLARE CURSOR statements.
The FOR cursor_name statement implicitly opens a cursor for the SELECT statement you specify as the cursor specification. The OPEN cursor_name statement opens a cursor for the SELECT statement you specify as the cursor specification.

Cursor Support

Support is different depending on whether a cursor is opened by a FOR loop statement or by a cursor declared by a DECLARE CURSOR statement.

FOR Loop Cursor Support

For a FOR loop statement, the following dummy iteration statement opens a cursor for the specified cursor.

FOR  for_loop_variable  AS [cursor_name  CURSOR FOR]          cursor_specification  DO  statement 
END FOR;

where cursor_specification  is a single SELECT statement and statement can be one or more SQL control or DML statements.

The FOR statement runs as follows:
  1. Fetches one row of data from the result set into the for_loop_variable on each iteration.
  2. Increments the cursor on each iteration, fetching the next row of data, if any.
    The WHERE CURRENT OF forms of DELETE and UPDATE perform as follows:
    • DELETE ... WHERE CURRENT OF cursor_name deletes the fetched row from its base table.
    • UPDATE … WHERE CURRENT OF cursor_name updates the fetched row in its base table.

DECLARE CURSOR Cursor Support

For cursors defined by a DECLARE CURSOR statement, you must submit explicit OPEN cursor_name and FETCH cursor_name statements.

For a result set cursor, if you specify CLOSE, the result set is not returned.

Related Information

For more details on the use of cursors in stored procedures, see DECLARE CURSOR (Stored Procedures Form), Cursors and FOR.