Cursors and Stored Procedures | Teradata Vantage - Cursors and Stored Procedures - Advanced SQL Engine

Cursors and Stored Procedures | Teradata Vantage - Cursors and Stored Procedures - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product

Advanced SQL Engine

Teradata Database

Release Number

17.05

17.00

Published

June 2020

Language

English (United States)

Last Update

2021-01-23

dita:mapPath

xqq1557098602407.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1148

lifecycle

Product Category

Teradata Vantage™

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:

The following statements with open cursors are not allowed in a stored procedure:

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 FOR statement in which it is defined. The scope of column name or its correlation name in a FOR loop cursor is restricted to the body of the FOR statement. In the case of nested FOR statements, you can reference the cursor name you specify in an outer FOR statement in statements inside the inner FOR statement(s).	The scope of the cursor is the BEGIN … END compound statement in which it is declared. 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 it 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 it exists, for an open cursor.	You must explicitly specify OPEN, FETCH or CLOSE. If you specify CLOSE for a result set cursor, the result set will not be 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.

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

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 executes as follows:

Fetches one row of data from the result set into the for_loop_variable on each iteration.
Increments the cursor on each iteration, fetching the next row of data, if it exists.
The WHERE CURRENT OF forms of DELETE and UPDATE perform as follows:
- DELETE … WHERE CURRENT OF cursor_name deletes the currently fetched row from its base table.
- UPDATE … WHERE CURRENT OF cursor_name updates the currently fetched row in its base table.

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

Note that for a result set cursor, if you specify CLOSE, the result set will not be returned.