Cursor hold on COMMIT you need to know these points

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.

Lead yourself to enhance your SQL skills and transform into Senior SQL Developer.

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.

Cursor Life cycle
Cursor Life cycle

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.
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.