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.