Savepoints In SQL Transactions. – SAVEPOINT

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

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.

Also read,

Technorati Tags:
, , , , , , , , , , ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

2 thoughts on “Savepoints In SQL Transactions. – SAVEPOINT

  1. Pingback: SQL - Transaction Statements -Transaction Management | SQL and PLSQL

  2. Pingback: Autonomous Transactions in PL/SQL.Oracle Autonomous Transactions | SQL and PLSQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page