Expansion of Cursor-name

The expanded parameters of cursor-name represent a cursor.
idmscu19
The expanded parameters of cursor-name represent a cursor.
This article describes the following information:
2
2
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.
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
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
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.