Overview of Cursor Types
Cursors are typically required for data manipulation language (DML)
operations on more than one row of data (or on collection
variables). You can declare the following types of cursors with the
DECLARE statement:
- A Select cursor is a cursor associated with a SELECT statement.
- A Function cursor is a cursor associated with an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement.
- An Insert cursor is a cursor associated with an INSERT statement.
Sections that follow describe each of these cursor types. Cursors
can also have sequential, scroll, and hold characteristics
(but an Insert cursor cannot be a scroll cursor). These characteristics
determine the structure of the cursor; see Cursor Characteristics. In addition, a Select
or Function cursor can specify read-only or update mode.
For more information, see Select Cursor or Function Cursor.
Tip: Function cursors behave the same
as Select cursors that are enabled as update cursors.
Besides associating a cursor directly with the text of an SQL statement, the FOR keyword of the DECLARE statement can be followed by the identifier of a prepared SQL statement, associating the cursor with the result set of an INSERT, SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement that was prepared dynamically. This feature enables you to associate different SQL statements with the same cursor at different times. In this case, the type of cursor depends on the prepared SQL statement that the statement_id or statement_id variable specifies when the cursor is opened. (For more information, see Associating a Cursor with a Prepared Statement.)