DECLARE CURSOR (Dynamic SQL Form) | Teradata Vantage - DECLARE CURSOR (Dynamic SQL Form) - Advanced SQL Engine - Teradata Database

Purpose

Defines and assigns a name to a cursor for a prepared dynamic SQL statement.

Invocation

Nonexecutable preprocessor declaration.

Embedded SQL only.

Syntax

DECLARE cursor_name [SCROLL] CURSOR FOR statement_name

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.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Authorization

None.

Valid Prepared Dynamic SQL Statements

• 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 multistatement request, which can include any of the foregoing statements

Rules

• 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 always 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 multistatement 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 Topics

For more information about SCROLL, see FETCH (Embedded SQL Form).