PL/SQL Transaction Commit, Rollback, Savepoint, Autocommit, Set Transaction
Oracle PL/SQL transaction oriented language. Oracle transactions provide a data integrity. PL/SQL transaction is a series of SQL data manipulation statements that are work logical unit. Transaction is an atomic unit all changes either committed or rollback.
At the end of the transaction that makes database changes, Oracle makes all the changes permanent save or may be undone. If your program fails in the middle of a transaction, Oracle detect the error and rollback the transaction and restoring the database.
You can use the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION command to control the transaction.
The COMMIT statement to make changes permanent save to a database during the current transaction and visible to other users,
Commit Syntax :
Commit comments are only supported for backward compatibility. In a future release commit comment will come to a deprecated.
Commit Example :
The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. If you make a mistake, such as deleting the wrong row from a table, a rollback restores the original data. If you cannot finish a transaction because an exception is raised or a SQL statement fails, a rollback lets you take corrective action and perhaps start over.
ROLLBACK Example :
Above example statement is exception raised because eno = 1 is already so DUP_ON_INDEX exception rise and rollback to the dup_found savepoint named.
SAVEPOINT savepoint_names marks the current point in the processing of a transaction. Savepoints let you rollback part of a transaction instead of the whole transaction.
SAVEPOINT Syntax :
SAVEPOINT Example :
No need to execute COMMIT statement every time. You just set AUTOCOMMIT ON to execute COMMIT Statement automatically. It's automatic execute for each DML statement. set auto commit on using following statement,
AUTOCOMMIT Example :
You can also set auto commit off,
SET TRANSACTION statement is use to set transaction are read-only or both read write. you can also assign transaction name.
SET TRANSACTION Syntax :
Set transaction name using the SET TRANSACTION [...] NAME statement before you start the transaction.
SET TRANSACTION Example :