Oracle Transaction Statements

Oracle Transaction Statements

A transaction is a series of one or more SQL statements that Oracle Database treats as a unit.
A transaction is an atomic unit. A transaction ends when it is committed or rolled back.

The transaction control statements are:

SAVEPOINT – is a point in a transaction to which you can later roll back.
A transaction can have multiple savepoints. Savepoints are optional.
Oracle Database rolls back only the statements run after the savepoint.

COMMIT – ends the current transaction, makes its changes permanent, erases its savepoints, and releases its locks.

ROLLBACK – rolls back either the entire current transaction or only the changes made after the specified savepoint.

Rollback to Savepoint

declare
v_name varchar2(255);
begin

update CUSTOMERS
set name = 'Customer_A'
where id=1 
AND name='Customer_Abc';

DBMS_OUTPUT.put_line('upd_1: '||SQL%ROWCOUNT);

SAVEPOINT update_1;

Insert into CUSTOMERS (ID,NAME,ADDRESS) 
values (4,'Customer_D','Address 4D');

DBMS_OUTPUT.put_line('upd_2: '||SQL%ROWCOUNT);

SAVEPOINT update_2;

select name into v_name from CUSTOMERS where id=5;

COMMIT;
exception 
	when no_data_found then
  DBMS_OUTPUT.put_line('ROLLBACK TO SAVEPOINT update_1');
	ROLLBACK TO SAVEPOINT update_1;
end;

Rollback

declare
v_name varchar2(255);
begin

update CUSTOMERS
set name = 'Customer_A'
where id=1 
AND name='Customer_Abc';

DBMS_OUTPUT.put_line('upd_1: '||SQL%ROWCOUNT);

Insert into CUSTOMERS (ID,NAME,ADDRESS) 
values (4,'Customer_D','Address 4D');

DBMS_OUTPUT.put_line('upd_2: '||SQL%ROWCOUNT);

select name into v_name from CUSTOMERS where id=5;

COMMIT;
exception 
	when no_data_found then
  DBMS_OUTPUT.put_line('ROLLBACK');
	ROLLBACK ;
end;

Commit

declare
v_name varchar2(255);
begin

Insert into CUSTOMERS (ID,NAME,ADDRESS) 
values (4,'Customer_D','Address 4D');

DBMS_OUTPUT.put_line('upd_2: '||SQL%ROWCOUNT);

select name into v_name from CUSTOMERS where id=4;

DBMS_OUTPUT.put_line('Customer name: '||v_name);

COMMIT;
end;