PL/SQL – transactions

Transactions in Oracle are controlled using a few key words:
BEGIN – starts
SAVEPOINT name – creates a marker which can be used to roll back part of transaction only
COMMIT – accepts
ROLLBACK – rejects everything
ROLLBACK TO SAVEPOINT name – rejects changes up to savepoint

Transaction also can be closed (commited or rejected) when DDL or DCL command is executed (commit is executed), session is killed, db crash ocurred, … (rollbacks).

You can decide whether your tool (SQLPLUS, SQLDeveloper) commits transaction for you. By default you need to commit on your own. To change this use SET AUTOCOMMIT ON.

--simple transaction commited
begin
insert into hr.regions values (100, 'Region A');
insert into hr.regions values (101, 'Region B');
commit;
end;

--commit or rollback simple transaction
declare
regions_count number := 0;
begin
insert into hr.regions values (103, 'Region C');
insert into hr.regions values (104, 'Region D');
select count(*) into regions_count from hr.regions;
    if regions_count > 15 
        then rollback; 
        else commit;
    end if;
end;

--rolling back to savepoint
declare
regions_count number := 0;
begin
insert into hr.regions values (103, 'Region C');
savepoint after_a;
insert into hr.regions values (104, 'Region D');
select count(*) into regions_count from hr.regions;
    if regions_count > 5 
        then rollback to savepoint after_a; --will rollback only one insert
    end if;
commit; -- the second insert will be commited
end;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s