Transactions and Cursors | VantageCloud Lake - Transactions and Cursors - Teradata Vantage

SQL Terminating Statements and Cursors

• COMMIT terminates all open cursors and commits the changes made by the cursors while a transaction was in progress. (ANSI session mode only).
• ROLLBACK (ANSI and Teradata session modes) or ABORT (Teradata session mode only) terminates all open cursors within the current transaction and discards any changes made by the cursors while the transaction was in progress.
• END TRANSACTION terminates all open cursors within the current transaction and commits any changes made by the cursors while the transaction was in progress (Teradata session mode only).

Cursor Semantics for Implicit Transactions

For implicit transactions (cursor opened in Teradata session mode only):

• A FOR CURSOR loop opens the cursor as a holdable cursor, and its sensitivity is insensitive.
• A FOR CURSOR loop also supports transaction control statements.
• In the case of a DECLARE, OPEN, or FETCH CURSOR, the cursor is holdable and its sensitivity is insensitive.

Cursor Semantics for Explicit Transactions

For explicit transactions (cursor opened in Teradata mode or in ANSI session mode):

• A FOR CURSOR loop opens the cursor as a without hold cursor, and its sensitivity is insensitive. This implies that when the transaction is closed, the cursor is closed. If a cursor is insensitive, the visibility of significant changes to SQL data is implementation-dependent.
• A FOR CURSOR loop does not permit a COMMIT, ROLLBACK, or ABORT within the FOR loop. Where the system detects the command determines its action.

  When System Detects Statement	System Action
  Compile time	System returns compile-time error and does not create stored procedure.
  Run time	System returns run-time error. In Teradata session mode, this is a failure, and the system closes the transaction and the cursor.
  In nested call in Teradata session mode	System reports that nested call failed. The failure still applies for a subsequent FETCH CURSOR.

• In the case of a DECLARE, OPEN, OR FETCH CURSOR, the cursor is without hold and its sensitivity is insensitive. The system runs transaction control statements successfully, but the next FETCH or CLOSE CURSOR causes the system to return an error.

Cursor Holdability and Transaction and Session Termination

• The system does not close a holdable cursor if that cursor is open at the time the transaction terminates with a COMMIT.
• A holdable cursor that is closed at the time the transaction terminates remains closed.
• The system closes a holdable cursor if the transaction terminates with a ROLLBACK.
• The system closes and destroys a holdable cursor when the session that created the cursor terminates.
• The system closes a cursor that is without hold when the transaction that created the cursor terminates.

Cursor Sensitivity

If a cursor is open, and the transaction that opened the cursor makes a significant change to data, the system determines whether that change is visible through that cursor before closing the cursor, as follows:

• If a cursor is insensitive, the visibility of significant changes to data is implementation-dependent.
• If a cursor is insensitive, significant changes to data are not visible.
• If a cursor is sensitive, significant changes to data are visible.