Introduction to SQL

Data Control Language (DCL)

In this section we will be using commands that control the behavior of database objects. Although DCL form a part of PL/SQL part, they have been described in brief here.

The commands have been described one at a time, covering all the clauses.

The commands have been described with appropriate example one at a time covering all the clauses before we go on to discuss Data Control Language (DCL) commands, let’s understand the concept of transaction.

What is a Transaction?

A transaction consists of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed.

For example, when a shopkeeper updates the amount of a particular product sold each week, he will also want to update the total amount sold to date. However, he will not want to update one without also updating the other; otherwise, the data will be inconsistent. The way to ensure that either both actions occur or neither action occurs is to use a transaction.

Now we come to various DCL commands which make use of transactions.

Savepoint

When a transaction is rolled back to a savepoint all changes made after that savepoint are undone. Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is erased. After a savepoint has been created, you can continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.

SAVEPOINT Savepoint_name

The above statement sets a named transaction savepoint whose name is savepoint_name.

Rollback

The Rollback Statement terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database.

Thus, Rollback statement is used to undo work done in the current transaction.

ROLLBACK [TO SAVEPOINT savepoint_name]

The above statement rolls back a transaction to the SAVEPOINT named savepoint_name. Modifications that this transaction made to rows after the savepoint was set, are undone in the rollback.