PL/SQL Transactions

A transaction is a sequence of operations performed by executing the SQL statements in PL/SQL block of code, where the following rules are applicable:

1. If we have a set of statements in a transaction, then the complete set executes as a block, where if a single statement fails, the affect of all the previous successful statement executions is also reverted back.
2. In a transaction, either all the statements get executed successfully or none.
3. The scope of a transaction is defined by using COMMIT and ROLLBACK commands

Using PL/SQL Transactions

It has a beginning and an end.

A transaction begins whenever the first SQL statement (particularily DML commands INSERT, UPDATE, DELETE, SELECT) is encountered and ends when a COMMIT or ROLLBACK command is executed.

Using COMMIT

Commit command is executed after every DML command as they are not auto saved or commited like DDL commands. This way, the commit command permanently changes the data in the database.

Following is the syntax:

Commit;

NOTE: By default, automatic commit for DML commands is off. The automatic commit for DML commands can be set by using the following command:

set autocommit on;
-- and to turn it off
set autocommit off;

Using ROLLBACK

Rollback means undo. Whenever rollback command is executed, it ends the transaction and undoes all the changes made during the transaction. Rollback can be applied to those transactions which are not committed.

The rollback command will have no affect if it is executed after the commit command because in that case the commit command will make the changes done in the transaction permanent.

Following is the syntax:

Rollback [to savepoint <savepointname >];

where,

savepoint is an optional parameter and is used to rollback a transaction partly upto a certain specified point.

savepointname is the name given to the savepoint created during the transaction and is user-defined.

Using SAVEPOINT

For longer transactions, savepoint is quite useful as it divides longer transactions into smaller parts and marks certain points of a transaction as checkpoints.

It is useful when we want to rollback a particular part of a transaction instead of applying rollback to unwanted parts of a transaction or the complete transaction.

For example, if a complete transaction has 8 DML statements, and we create a savepoint after 4 statements, then if, for some reason after the execution of 6th statement we want to rollback uptil the 4th statement, then we can easily do that and the transaction can again be executed starting from the 4th statement.

Following is the syntax:

Savepoint  <savepointname>;

Time for an Example!

Below we have a PL/SQL program to demonstrate the execution of a transaction,

set serveroutput on;

DECLARE
	rollno student.sno%type;
	snm student.sname%type;
	s_age student.age%type;
	s_cr student.course%type;
BEGIN
	rollno := &sno;
	snm := '&sname';
 	s_age := &age;
	s_cr := '&course';
	INSERT into student values(rollno,snm,age,course);
	dbms_output.put_line('One record inserted');
	COMMIT;
END;

In the above code of PL/SQL block, there is a table called STUDENT in the database with columns sno as number, sname as varchar2, age as number and course as varchar2.

In the code, we have executed an INSERT statement and then used the COMMIT statement to commit or permanently save the changes into the database.

Instead of the COMMIT statement, if we use the ROLLBACK statement there, then even though the INSERT statement executed successfully, still, after the execution of the PL/SQL block if you will check the Student table in the database, you will not find the new student entry because we executed the ROLLBACK statement and it rolled back the changes.

PL/SQL Code Example with Savepoint and Rollback

Let's add two insert statement in the above code and put a savepoint in between them and then use the ROLLBACK command to revert back changes of one insert statement.

set serveroutput on;

DECLARE
	rollno student.sno%type;
	snm student.sname%type;
	s_age student.age%type;
	s_cr student.course%type;
BEGIN
	rollno := &sno;
	snm := '&sname';
 	s_age := &age;
	s_cr := '&course';
	INSERT into student values(rollno,snm,age,course);
	dbms_output.put_line('One record inserted');
	COMMIT;
	-- adding savepoint
	SAVEPOINT savehere;
	-- second time asking user for input
	rollno := &sno;
	snm := '&sname';
 	s_age := &age;
	s_cr := '&course';
	INSERT into student values(rollno,snm,age,course);
	dbms_output.put_line('One record inserted');
	ROLLBACK [TO SAVEPOINT savehere];
END;

After execution of the above code, we will have one entry created in the Student table, while the second entry will be rolled back.