Nov

Savepoints In SQL Transactions. – SAVEPOINT

The intermediate markers within the context of a transaction called savepoints. Savepoints divide a long transaction into smaller parts.
User then have the option of rolling back before the current point in the transaction but after a declared savepoint within the transaction.

The Folllowing simple example illustartes the SAVEPOINT.

-- Created on 11/4/2008 by PLSQLTIPS
declare
-- Local variables here
i integer;
begin
-- Test statements here

 insert into MyTable(name,address,id) values('Abcd2','address2',2);
insert into MyTable(name,address,id) values('Abcd3','address3',3);
commit;

end;

This will insert two rows in MyTable;

SQL> select * from MyTable;
NAME             ADDRESS             ID
Abcd2            address2             2
Abcd3            address3             3
SQL>

Now Let us add a SAVEPOINT in the above example

SQL> delete from MyTable;
2 rows deleted
SQL> commit;

Let us add a SAVEPOINT ‘abcd’ in between the DML statements.

-- Created on 11/4/2008 by PLSQLTIPS
declare
  -- Local variables here
begin
 -- Test statements here
  insert into MyTable(name,address,id) values('Abcd2','address2',2);
  savepoint abcd;
  insert into MyTable(name,address,id) values('Abcd3','address3',3);
 rollback to abcd;
  commit;
end;

SQL> select * from MyTable;
NAME            ADDRESS             ID
Abcd2           address2             2
SQL>

The second ‘INSERT‘ statement rolled back to the SAVEPOINT ‘abcd’ before committing the transaction.
So only one record is persisted in the table permanently.

Savepoints In SQL Transactions. – SAVEPOINT

Categories