Cursors

A cursor is used in an application program to select a set of rows and then process that returned data one row at a time. When a SELECT statement in an embedded SQL application returns multiple rows of data, you need a mechanism that makes this returned data or result set available to your application program, one row after another.

A cursor is like a name that is associated with a query. A cursor is created using the DECLARE CURSOR statement which defines the name of the cursor and specifies its associated query. Three additional SQL statements operate on cursors.
OPEN
Performs the query which builds the result set and prepares the cursor for retrieval of the first row.
FETCH
Retrieves one row of the result set and assigns the values of that row to target variables. Fetches are usually executed repeatedly until all rows of the result set have been retrieved.
CLOSE
Terminates the cursor and releases any resources that it was using. If needed again, the cursor can be reopened.
You can declare cursors as held or not held, returnable or not returnable, and scrollable or not scrollable.
holdability
A held cursor does not close after a commit operation. A cursor that is not held closes after a commit operation.
returnability
A returnable cursor returns the result set back to the caller of the procedure or client application.
scrollability
A non-scrollable cursor moves sequentially forward through a result set and each row can only be accessed once. A scrollable cursor can be moved randomly through the result set using the FETCH statement. Note that scrollable cursors are supported only in CLI, JDBC, and SQLJ applications.