Cursor

Because many programming languages do not provide an equivalent of the type “table”, the concept of the cursor is used when SQL statements are embedded in programs. A cursor enables you to process the rows of a table individually one after the other.

A cursor is assigned to a table referred to as the cursor table. This table is the derived table of the query expression that defined the cursor.

There are a number of SQL statements that can be used with cursors:

DECLARE CURSOR OPEN CLOSE FETCH DELETE ... WHERE CURRENT OF ... UPDATE ... WHERE CURRENT OF ... STORE RESTORE

Declare a cursor Opening a cursor Close a cursor Position cursor and read row Delete current row Update current row Save cursor position Restore cursor position

A cursor must be defined, be opened before it is used, and be closed after it has been used. The SQL statements must be used in a predefined order.

There are two types of cursors: cursors that can be updated (updatable cursor) and cursors that cannot be updated.

In routines, local cursors which can only be addressed within the COMPOUND statement are defined with the DECLARE CURSOR statement, see section "Local cursors".

A local cursor differs from a normal cursor only in its limited area of validity.