You use a savepoint along with a ROLLBACK statement; the savepoint gives you the option of rolling back a transaction to an intermediate point (a savepoint).

The syntax for the ROLLBACK statement is

ROLLBACK [TO savepoint];

The variable is defined as follows:

savepoint is a previously named savepoint.

Consider the example shown in Listing 6.25. Imagine that your application has a transaction that updates three tables: table_1, table_2, and table_3. If Oracle returns an error on the update to table_2, you can roll back to the first savepoint, which is table_1_update.

Listing 6.25. Sample use of a savepoint.

SQL> update table_1
  2  set table_1_col = 11;
4 rows updated.

SQL> savepoint table_1_update;
Savepoint created.

SQL> delete from table_2;
3 rows deleted.

SQL> rollback to table_1_update;
Rollback complete.

SQL> select * from table_2;
TABLE_2_COL
-----------
         99
         99
         99

You should use savepoints with care, however, because they add an additional layer of complexity to an application. Be sure that your transactions are well defined before you decide to implement savepoints.

Summary

Today's lesson discussed the use of these fundamental SQL statements:

• INSERT adds rows to a table.
• UPDATE modifies existing rows.
• DELETE removes rows from a table.
  

You can also use a subquery in conjunction with these statements.

The TRUNCATE statement irreversibly deletes all rows in a table.

A database transaction is a set of changes to one or more database tables, which constitutes a logical unit of work. You use the COMMIT statement to make the transaction permanent. Alternatively, you use the ROLLBACK statement to rescind the transaction.

What Comes Next?

On Day 7, "Taking Advantage of SQL Built-In Functions," you learn how to use a variety of functions in the four SQL statements—SELECT, INSERT, UPDATE, and DELETE.

Q&A

Q What is the syntax to indicate the beginning of a transaction?

A Typically, none. By default, a transaction begins with any change to any table since the last committed transaction or since the beginning of the database session—whichever is later. However, you must use the SET TRANSACTION READ ONLY to indicate the start of a read-only transaction.

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercise before you continue with tomorrow's lesson.

Quiz

1. Construct an SQL statement that adds a course with the following characteristics: Department ID = BIO, Course ID = 137, Title = INSECT BEHAVIOR, Description = In-depth study of insect societies and their behavior patterns, Units = 3, no additional fees.
2. Construct an SQL statement that charges $50 in additional fees for all courses in the philosophy department.
3. Construct an SQL statement that eliminates a scheduled class if it is offered by the English department or is going to be held in Flugle Hall.

Exercise

Several of the instructors at Flugle College have decided to create a new department called Integrated Studies. As a result, the English, History, and Philosophy departments will merge to become the Integrated Studies department. The department ID for this new department will be INTSTD. In the database, create the Integrated Studies department (without deleting the existing departments). Also, modify the contents of the Instructor table so that instructors in the English, History, and Philosophy department are now associated with the Integrated Studies department.