Cursor holdability refers to a characteristic in cursors that is concerned with whether a cursor is automatically closed when the transaction in which the cursor was opened is committed. A transaction is an atomic unit of work.
This means that all statements within the transaction must succeed or none of them can have any effect. If some statements within a transaction are executed and then one statement fails, all executed statements are rolled back and the database remains unchanged.
SQL provides two options that allow you to define cursor holdability:
WITH HOLD and WITHOUT HOLD.
- If you specify WITH HOLD, your cursor will remain open after you commit the transaction, until you explicitly close it.
- If you specify WITHOUT HOLD, your cursor will be automatically closed when the transaction is committed.
- If neither option is specified, WITHOUT HOLD is assumed and your cursor is automatically closed.