You can update or delete a row returned by a cursor if the cursor is updatable. If the cursor is read-only, you cannot update or delete it. By default, Adaptive Server attempts to determine whether a cursor can be updated before designating it as read-only.
For information on making cursors updateable by using configuration parameter select for update in Adaptive Server 15.7 and later, see “Using select for update”.
You can explicitly specify whether a cursor is read-only by using the read only or update keywords in the declare statement. Specifying a cursor as read-only ensures that Adaptive Server correctly performs positioned updates. Make sure the table being updated has a unique index. If it does not, Adaptive Server rejects the declare cursor statement.
All scrollable cursors and all insensitive cursors are read-only.
The following example defines an updatable result set for the pubs_crsr cursor:
declare pubs_crsr cursor for select pub_name, city, state from publishers for update of city, state
The example includes all the rows from the publishers table, but it explicitly defines only the city and state columns as updatable.
Unless you plan to update or delete rows through a cursor, declare it as read-only. If you do not explicitly specify read only or update, the semi_sensitive nonscrollable cursor is implicitly updatable when the select statement does not contain any of the following constructs:
distinct option
group by clause
Aggregate function
Subquery
union operator
at isolation read uncommitted clause
You cannot specify the for update clause if a cursor’s select statement contains one of these constructs. Adaptive Server also defines a cursor as read-only if you declare certain types of cursors that include an order by clause as part of their select statement. See “Types of cursors”.
