DECLARE CURSOR (Dynamic SQL Form) | VantageCloud Lake - DECLARE CURSOR (Dynamic SQL Form) - Teradata VantageCloud Lake

The dynamic SQL form of DECLARE CURSOR associates a cursor with a dynamic SQL statement.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Syntax

DECLARE cursor_name [SCROLL] CURSOR FOR statement_name

Syntax Elements

cursor_name

Any valid SQL identifier.

SCROLL

The declared cursor can fetch the row in the response set based on the FETCH orientation declared.

If you do not specify SCROLL, the cursor can only scroll forward to the next row in the response set.

Use SCROLL only when the dynamic SQL is a SELECT statement.

statement_name

The name associated with a previously prepared statement.

Usage Notes

• Valid prepared dynamic SQL statements are:
  • A single, non-data-returning, non-macro statement
  • A single SELECT statement (which you must specify without an INTO clause)
  • A single EXEC macro_name statement
  • A multiple-statement request, which can include any of the foregoing statements
• DECLARE CURSOR Usage Notes (All Forms)
• You must PREPARE the statement specified by statement_name before you OPEN the dynamic cursor within the same transaction.
• You can declare only one dynamic cursor for a given statement_name.
• You cannot specify a DELETE or UPDATE embedded SQL statement on a SELECT AND CONSUME cursor.

  A cursor for a queue table is read-only in PP2 ANSI mode. Therefore, a positioned DELETE or UPDATE (that is, deleting or updating the most current fetched cursor row) is not allowed for a queue table cursor in PP2 ANSI mode.

• A scrollable cursor is not allowed for multiple-statement requests in PP2 ANSI mode.

Example: Using Dynamic DECLARE CURSOR Statements

Dynamic DECLARE CURSOR statements take the following form:

DECLARE Ex CURSOR FOR DynStmt7

Related Information

• The dynamic SQL form of DECLARE CURSOR, see DECLARE CURSOR (Dynamic SQL Form)
• SCROLL, see FETCH (Embedded SQL Form).