Specifies the cursor is not scrollable. If scrolling is not specified, the default is NO SCROLL. With NO SCROLL, the FETCH statement can only return the next row.
Note: Rows are sensitive to changes if a temporary table is not used.
Specifies the cursor is scrollable.
With ASENSITIVE specified, the cursor is SENSITIVE DYNAMIC if a temporary table is not used. Otherwise, it is INSENSITIVE. This is the same as NO SCROLL except that scroll options are available with the FETCH statement.
When scrolling is used, ASENSITIVE is the default scroll type.
INSENSITIVE means that the cursor is not sensitive to changes in the underlying tables of the result set.
With INSENSITIVE specified, the cursor is made read-only, and no positioned update or delete is allowed
SENSITIVE means that the cursor is sensitive to changes made after the cursor is opened.
Note:If changes cannot be made visible to the cursor, an error is returned on the bind of the cursor open statement.
Changes cannot be made visible if a temporary table is required, or when the cursor has more than one database table, or when a User Defined Function Table (UDFT) is used.
A SENSITIVE scroll cursor must therefore reference only a single database table and not use aggregation.
SENSITIVE scroll cursors can be DYNAMIC or STATIC. The DYNAMIC scroll cursor is the default with SENSITIVE specified.
DYNAMIC means that changes made by the current transaction are visible immediately, and changes made by other transactions are visible when committed.
A temporary table is not built, so qualifying rows that are inserted while the cursor is open are visible, and rows deleted or updated such that they no longer qualify while the cursor is open are no longer visible.
If a row is updated, it is logically moved to its new position in the result set. For example, if the key being used to retrieve rows dynamically is on column line_number, if the application increments line_number using a positioned update, then the same row will be fetched using fetch next.
This is more efficient than an insensitive scroll cursor because no temporary table is required, but the application must be able to work properly with the dynamic nature of the result set.
As with an insensitive scroll cursor, a result table is built, so the size of the result set does not change. However, rows fetched using the fetch sensitive option reflect the current state of the corresponding underlying base table row.
Row of a cursor declared as sensitive static can be fetched as sensitive or insensitive. If fetch sensitive is used, then the temporary table is updated to reflect the corresponding row of the underlying database table. If fetch insensitive is used, the row is returned from the temporary table in its current state, which reflects changes due to a previous fetch sensitive.
A cursor with the specified name is defined when your program is executed. The name must not be the same as the name of another cursor declared in your program.
If you specify SQLMODE=ANSI or SQLMODE=FIPS in the SQL Preprocessor options, the cursor name can be 1 to 18 characters.
For all other modes, the cursor name can be 1 to 32 characters.
When this CA Datacom/DB extension is specified, the cursor stays open when a COMMIT WORK is executed. Any record-at-a-time command that commits the logical unit of work (for example LOGCP, LOGCR) works the same way. This is especially useful in a batch program that does updates and issues COMMIT WORK periodically to keep the log from becoming full and to limit the amount of work RESTART would have to do in case of a failure. The repositioning of the cursor is harder to program without WITH HOLD.
For information about the select-statement, see Select-Statement.
Specifies the name of a prepared statement. That statement must be prepared (using the PREPARE statement) sometime after the DECLARE CURSOR statement is executed and before the OPEN statement is executed, and it must be a select-statement. For information on the PREPARE statement, see PREPARE.
A cursor in the open state designates a result table and a position relative to the rows of that table. The table is the result table specified by the select-statement of the cursor.
The result table is read-only if the select-statement includes any of the following:
The result table is also read-only if the FROM clause of the outer subselect of the select-statement:
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|