Make Cursors Updatable

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, SAP ASE attempts to determine whether a cursor can be updated before designating it as read-only.

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 SAP ASE correctly performs positioned updates. Make sure the table being updated has a unique index. If it does not, SAP ASE 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. SAP ASE 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.