Expansion of Cursor-name

The expanded parameters of cursor-name represent a cursor.

Syntax

Expansion of cursor-name

►►──┬─ static-cursor-name ───┬───────────────────────────────────────────────►◄
    └─ extended-cursor-name ─┘

Expansion of static-cursor-name

►►── cursor-name ────────────────────────────────────────────────────────────►◄

Expansion of extended-cursor-name

►►──┬─────────────┬───┬─ 'cursor-name' ────┬─────────────────────────────────►◄
    ├─── LOCAL ◄ ─┤   ├─ :host-variable ───┤
    └─── GLOBAL ──┘   ├─ local-variable ───┤
                      └─ routine-parameter ┘

Parameters

cursor-name

Specifies the name of the cursor as an identifier.

'cursor-name'

Specifies the name of the cursor as a literal whose value must conform to the rules for an identifier.

:host-variable

Specifies the name of the cursor as a host-variable whose value must conform to the rules for an identifier.

local-variable

Specifies the name of the cursor as a local-variable whose value must conform to the rules for an identifier.

routine-parameter

Specifies the name of the cursor as a routine-parameter whose value must conform to the rules for an identifier.

LOCAL/GLOBAL

Specifies the scope of the associated cursor name:

LOCAL indicates that the cursor can be referenced only from within the program where it is defined.
GLOBAL indicates that the cursor can be referenced from any program executing within the same SQL transaction.

Default: LOCAL

Usage

Static Versus Extended Cursor Names

A static cursor name is one coded as a simple identifier. The following DECLARE CURSOR statement assigns the static name "cursor1" to the cursor being defined:

DECLARE cursor1 CURSOR FOR select1

Cursors defined by a DECLARE CURSOR statement always have static names. Such cursors may either be dynamic or static, depending on whether the DECLARE CURSOR statement references a dynamically prepared SQL statement, as in the example above, or directly includes a cursor-specification.

An extended cursor name is one coded either as a literal, a host variable, a local-variable, or a routine-parameter. The following ALLOCATE CURSOR statement assigns the extended name "cursor1" to the cursor being defined:

MOVE 'cursor1' to cursor-nam
ALLOCATE :cursor-nam CURSOR FOR :statement-nam

Cursors created by an ALLOCATE CURSOR statement always have extended names and are always dynamic.

If a cursor is defined using a static name, it must be referenced using a static name. If it is defined using an extended name, it must be referenced using an extended name that has the same scope option as specified on the definition.

An exception to this rule occurs when identifying a cursor within a dynamically prepared UPDATE or DELETE statement.

Note: For more information about the UPDATE and DELETE statements, see UPDATE and DELETE.

Uniqueness of Cursor Names

Static and extended cursor names do not have to be unique with respect to each other. If two cursors are assigned the same value for a name, they are considered two separate cursors provided that either:

One of the names is static while the other is extended
Both of the names are extended, but they have different scopes, as indicated by their LOCAL/GLOBAL parameter.

Example

Example of Cursor-name

The following DECLARE CURSOR statement defines a dynamic cursor using a static cursor name of C1. It is referenced within the subsequent OPEN statement:

EXEC SQL
  DECLARE C1 CURSOR FOR S1
END-EXEC
EXEC SQL
  OPEN C1
END-EXEC

Example of Extended-cursor-name

The following ALLOCATE CURSOR statement defines a local cursor using an extended cursor name of C1. It is then referenced in the subsequent OPEN statement:

EXEC SQL
  ALLOCATE 'C1' CURSOR FOR 'S1'
END-EXEC
EXEC SQL
  OPEN 'C1'
END-EXEC

Note: Even though C1 is used as the cursor name in both of the above examples, two separate cursors are created: one with a static name of C1 and one with an extended name of C1.

Global Extended-cursor-name

The following ALLOCATE CURSOR statement defines a global cursor using an extended cursor name whose value is not known until runtime. In this case, the value 'C2' is moved to the host variable before the statement is executed and will be the name of the cursor created:

  move 'C2' to :cname
EXEC SQL
  ALLOCATE GLOBAL :CNAME CURSOR FOR :SNAME
END-EXEC

Since this is a global cursor, it can be referenced in a different program than the one where the ALLOCATE CURSOR statement appears. For example, the following OPEN statement might be contained in a different program:

EXEC SQL
  OPEN GLOBAL 'C2'
END-EXEC

Note: It does not matter that in one case the name of the cursor is supplied through a host-variable and in the other it is specified as a literal. They both refer to the global cursor C2.

More information:

Defining and Using Functions

Identifying Entities in Schemas