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.
On COMMIT What happens to Cursors
On COMMIT cursors will be closed automatically
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.
What happens when one statement fails in series of SQL statements
If some statements within a transaction are executed and then one statement fails, all executed statements are rolled back and the database remains unchanged.
Life Cycle of Cursor
2 Top Ideas to Control Cursors
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.