Cursor
A cursor is a pointer to a record in a view created by a select query.
Usage Flow
- Declare a cursor.
- Open the cursor to create the view. The pointer is set to before the first record.
-
Fetch the data of the referring record into variables.
You can use the While In Statement to fetch all records in loop.
- Close the cursor to discard the view.
- Dispose the cursor to discard the cursor definition as necessary.
Cursors are not affected by transactions and does not detect any update operations. The view refered by a cursor is retrieved when the cursor is opened, and it will be held until the cursor is closed. If you update any records in the tables that refered in any cursors, you may need to close and reopen the cursors.
Cursor Operation
Declare Cursor
DECLARE cursor_name CURSOR FOR select_query;
DECLARE cursor_name CURSOR FOR statement_name;
- cursor_name
- identifier
- select_query
- Select Query
- statement_name
- Prepared Statement
Open Cursor
OPEN cursor_name;
OPEN cursor_name USING replace_value [, replace_value ...];
- cursor_name
- identifier
- replace_value
- replace value for Prepared Statement
Close Cursor
CLOSE cursor_name;
- cursor_name
- identifier
Dispose Cursor
DISPOSE CURSOR cursor_name;
- cursor_name
- identifier
Fetch Cursor
FETCH [position] cursor_name INTO variable [, variable ...];
position
: {NEXT|PRIOR|FIRST|LAST|ABSOLUTE number|RELATIVE number}
- cursor_name
- identifier
- variable
- Variable
- number
- integer
Position
A Position keyword in a fetch cursor statement specifies a record to set the pointer. If specified record does not exist, the fetch cursor statement is set nulls to the variables.
If any position keyword is not specified, then the NEXT keyword is used to fetch.
- NEXT
- The pointer is set to the next record and return the record.
- PRIOR
- The pointer is set to the previous record and return the record.
- FIRST
- The pointer is set to the first record and return the record.
- LAST
- The pointer is set to the last record and return the record.
- ABSOLUTE number
- The pointer is set to the number-th record from the first record and return the record. “ABSOLUTE 0” represents the first record.
- RELATIVE number
- The pointer is set to the number-th record from the current record and return the record. “RELATIVE 0” represents the current record.
Cursor Status
You can get some cursor status by using following expressions.
Cursor Is Open
CURSOR cursor_name IS [NOT] OPEN
- cursor_name
- identifier
- return
- ternary
Return a ternary value indicating whether the cursor is open.
Cursor Is In Range
CURSOR cursor_name IS [NOT] IN RANGE
- cursor_name
- identifier
- return
- ternary
Return a ternary value indicating whether the cursor pointer is set to any existing record. If the cursor is closed, then an error is occurred. Before the first fetch, return UNKNOWN.
Cursor Count
CURSOR cursor_name COUNT
- cursor_name
- identifier
- return
- integer
Return the number of rows in the view that the cursor is referring.