DECLARE Cursor

Declare a cursor in a stored procedure.

Syntax

DECLARE cursor_name CURSOR [ WITHOUT HOLD ]
    FOR query_expression [ FOR { UPDATE | READ ONLY } ]

Description

Declare a cursor variable and bind it to a query definition, query_expression. Using cursors allows for fetching and processing query result set, one row at a time.

Declaring the cursor does not actually execute the query. Opening the cursor will actually run the query and populate the cursor. See OPEN Cursor.

In this release of NuoDB, the cursor is as sensitive as possible to insert, update, or delete operations made to the underlying rows of the result set by other database sessions.

Parameters

cursor_name

A name that is associated with the cursor being declared. This name must be unique within a stored procedure. All operations on a cursor variable must refer to the cursor name.

WITHOUT HOLD

This parameter specifies cursor holdability:

  • WITHOUT HOLD: The cursor will automatically close while in a fetch loop and either a COMMIT or ROLLBACK is issued. This is the default behavior.

query_expression

A valid SELECT statement that returns a result set. (See SELECT.) This can be selecting from one or more tables and/or views.

FOR { UPDATE | READ ONLY }

  • FOR READ ONLY: The cursor is read only and cannot be used for positional updates or deletes. A positional update or delete will cause a runtime error; that is, the stored procedure will be successfully created, but will throw an error when executed.

  • FOR UPDATE: The cursor can be used for positional updates or deletes while fetching the result set, by using WHERE CURRENT OF cursor_name in the UPDATE or DELETE DML SQL statement. For both tables and views, there are restrictions on the query definition that will allow it to be updatable. The only views that can be defined when using FOR UPDATE are updatable views. There are limitations on what views are allowed to be updatable (see Using Updatable Views). Tables are also restricted by these same limitations.