Closing a Select or Function Cursor

When a cursor is associated with a SELECT, EXECUTE FUNCTION, or EXECUTE PROCEDURE statement of SQL, closing the cursor terminates the associated SQL statement.

The database server releases all resources that it might have allocated to the active set of rows, for example, a temporary table that the cursor used to hold an ordered set. The database server also releases any locks that it might have held on rows that were selected through the cursor. If a transaction contains the CLOSE statement, however, the database server does not release the locks until you issue the COMMIT WORK or ROLLBACK WORK statement.

After you close a Select cursor or a Function cursor, the FETCH statement cannot reference that cursor until you reopen it.

In an SPL routine, the built-in SQLCODE function can indicate the result of the CLOSE statement for a Select cursor or a Function cursor. This function returns a value equivalent to the SQLCODE field of the sqlca structure. HCL OneDB™ issues an error, however, if you invoke the built-in SQLCODE function outside the calling context of an SPL routine.