Using the RELEASE Option
Oracle9i rolls back changes automatically if your program terminates abnormally. Abnormal termination occurs when your program does not explicitly commit or roll back work and disconnect using the RELEASE ××ded SQL statement.
Normal termination occurs when your program runs its course, closes open cursors, explicitly commits or rolls back work, disconnects, and returns control to the user. Your program will exit gracefully if the last SQL statement it executes is either
EXEC SQL COMMIT WORK RELEASE END-EXEC.
or
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
where the token WORK is optional. Otherwise, locks and cursors acquired by your user session are held after program termination until Oracle9i recognizes that the user session is no longer active. This might cause other users in a multiuser environment to wait longer than necessary for the locked resources.
Using the SET TRANSACTION Statement
You can use the SET TRANSACTION statement to begin a read-only or read/write transaction, or to assign your current transaction to a specified rollback segment. A COMMIT, ROLLBACK, or data definition statement ends a read-only transaction.
Because they allow "repeatable reads," read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables. During a read-only transaction, all queries refer to the same snapshot of the database, providing a multitable, multiquery, read-consistent view. Other users can continue to query or update data as usual. An example of the SET TRANSACTION statement follows:
EXEC SQL SET TRANSACTION READ ONLY END-EXEC.
The SET TRANSACTION statement must be the first SQL statement in a read-only transaction and can appear only once in a transaction. The READ ONLY parameter is required. Its use does not affect other transactions. Only the SELECT (without FOR UPDATE), LOCK TABLE, SET ROLE, ALTER SESSION, ALTER SYSTEM, COMMIT, and ROLLBACK statements are allowed in a read-only transaction.
In the following example, a store manager checks sales activity for the day, the past week, and the past month by using a read-only transaction to generate a summary report. The report is unaffected by other users updating the database during the transaction.
EXEC SQL SET TRANSACTION READ ONLY END-EXEC.
EXEC SQL SELECT SUM(SALEAMT) INTO
AILY FROM SALES
WHERE SALEDATE = SYSDATE END-EXEC.
EXEC SQL SELECT SUM(SALEAMT) INTO :WEEKLY FROM SALES
WHERE SALEDATE > SYSDATE - 7 END-EXEC.
EXEC SQL SELECT SUM(SALEAMT) INTO :MONTHLY FROM SALES
WHERE SALEDATE > SYSDATE - 30 END-EXEC.
EXEC SQL COMMIT WORK END-EXEC.
* -- simply ends the transaction since there are no changes
* -- to make permanent
* -- format and print report