The SAVEPOINT Statement - Oracle

Using the SAVEPOINT statement, you can mark a spot in the middle of a transaction to which you might wish to roll back. ASAVEPOINTstatement allows you to undo part of a transaction rather than all of it. Following is the syntax to use in creating a savepoint:

SAVEPOINT savepoint_name;

You uniquely name the savepoint and then may subsequently reference it in aROLLBACK statement as follows:

ROLLBACK TO SAVEPOINT savepoint_name;

Regardless of how many savepoints exist within a transaction, aROLLBACK statement without aSAVEPOINTreference will automatically roll back the entire transaction. The following SQL*Plus example uses a savepoint to conditionally undo the DML statements since theSAVEPOINTstatement was issued. First, the example creates a new region in theREGIONStable because you are opening a branch office in Antarctica:

SQL> insert into regions (region_id, region_name)
  2  values (5, 'Antarctica');

1 row created.

SQL> savepoint new_region;

Savepoint created.

The savepointNEW_REGIONmay come in handy later if you need to roll back the insertion of the new countries you will be adding in the next few statements:

SQL> insert into countries (country_id, country_name, region_id)
  2 values ('NA', 'Northern Antarctica', 5);

1 row created.

SQL> savepoint new_country_1;

Savepoint created.

SQL> insert into countries (country_id, country_name, region_id)
  2  values ('SA', 'Southern Antarctica', 5);

1 row created.

SQL> savepoint new_country_2;

Savepoint created.

After performing these two inserts, you realize that you have a geographic anomaly on your hands, and that for now you are just going to add one country to theCOUNTRIEStable but keep the new region in theREGIONStable:

SQL> rollback to new_region;

Rollback complete.

SQL> insert into countries (country_id, country_name, region_id)
  2  values ('IA', 'Inner Circle of Antarctica', 5);

1 row created.

Just to double-check your work before you run theCOMMITstatement, you query theREGIONS andCOUNTRIEStables:

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Antarctica

SQL> select * from countries;

CO COUNTRY_NAME                    REGION_ID
-- ------------------------------ ----------
AR Argentina                               2 AU Australia                               3 BE Belgium                                 1 BR Brazil                                  2 CA Canada                                  2 CH Switzerland                             1 CN China                                   3 DE Germany                                 1 DK Denmark                                 1 EG Egypt                                   4 FR France                                  1

CO COUNTRY_NAME                    REGION_ID
-- ------------------------------ ----------HK HongKong                                3 IA Inner Circle of Antarctica              5 IL Israel                                  4 IN India                                   3 IT Italy                                   1 JP Japan                                   3 KW Kuwait                                  4 MX Mexico                                  2 NG Nigeria                                 4 NL Netherlands                             1 SG Singapore                               3

CO COUNTRY_NAME                    REGION_ID
-- ------------------------------ ----------
UK United Kingdom                          1 US United States of America                2 ZM Zambia                                  4 ZW Zimbabwe                                4

26 rows selected.

SQL>

Now that everything looks good, you commit the single-row change to theREGIONStable, and the single-row change to theCOUNTRIES table:

SQL> commit;

Commit complete.