Subset of SELECT statements associated with cursors

As indicated in the syntax diagram for the DECLARE statement, not all SELECT statements can be associated with a read-only or update cursor.

If the DECLARE statement includes the FOR READ ONLY or FOR UPDATE option, you must observe certain restrictions on the SELECT statement that is included in the DECLARE statement (either directly or as a prepared statement).

If the DECLARE statement includes the FOR READ ONLY option, the SELECT statement cannot have a FOR READ ONLY or FOR UPDATE option. (For a description of SELECT syntax and usage, see SELECT statement.)

If the DECLARE statement includes the FOR UPDATE option, the SELECT statement must conform to the following restrictions:
  • The statement can select data from only one table.
  • The statement cannot include any aggregate functions.
  • The statement cannot also include the FOR UPDATE keywords.
  • The statement cannot include any of the following clauses or keywords: DISTINCT, EXCEPT, FOR READ ONLY, FOR UPDATE, GROUP BY, INTERSECT, INTO TEMP, MINUS, ORDER BY, UNION, or UNIQUE.