Transactions and Cursors | Teradata Vantage - Transactions and Cursors - Teradata Vantage - Analytics Database

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 asensitive.
• 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 asensitive.

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 asensitive. This implies that when the transaction is closed, the cursor is closed. If a cursor is asensitive, 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. If the system detects a COMMIT, ROLLBACK, or ABORT during compile time, it returns an error and does not create the stored procedure.

  If the system does not detect a COMMIT, ROLLBACK, or ABORT during compile time, it returns a run-time error. This is a failure in Teradata session mode, and the system closes the transaction and the cursor.

  If the ROLLBACK or ABORT occurs in a nested call (in Teradata session mode), the system reports the nested call as having 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 asensitive. The system executes 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 in which it was created terminates.
• The system closes a cursor that is without hold when the transaction in which it was created terminates.

Cursor Sensitivity

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

• If a cursor is asensitive, 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.