Chapter 13. DML and Transaction Management

PL/SQL is tightly integrated with the Oracle database via the SQL language. From within PL/SQL, you can execute any Data Manipulation Language (DML) statements—specifically INSERTs, UPDATEs, DELETEs, and, of course, queries.

You cannot, however, execute Data Definition Language (DDL) statements in PL/SQL unless you run them as dynamic SQL. This topic is covered in Chapter 15.

You can also join multiple SQL statements together logically as a transaction, so that they are either saved ("committed" in SQL parlance) together, or rejected in their entirety ("rolled back"). This chapter examines the SQL statements available inside PL/SQL to establish and manage transactions.

To appreciate the importance oftransactions in Oracle, it helps to consider the "ACID" principle: a transaction has Atomicity, Consistency, Isolation, and Durability. These concepts are defined as follows:

Atomicity

A transaction's changes to a state are atomic: either they all happen or none happens.

Consistency

A transaction is a correct transformation of state. The actions taken as a group do not violate any integrity constraints associated with that state.

Isolation

Many transactions may be executing concurrently, but from any given transaction's point of view, other transactions appear to have executed before or after its own execution.

Durability

Once a transaction completes successfully, the changes to the state are made permanent and survive any subsequent failures.

A transaction can either be saved by performing a COMMIT or be erased by requesting a ROLLBACK. In either case, the affected locks on resources are released (a ROLLBACK TO might release only some of the locks). The session can then start a new transaction. The default behavior in a PL/SQL program is that there is one transaction per session, and all changes that you make are a part of that transaction. By using a feature called autonomous transactions, however, you can create nested transactions within the main, session-level transaction. This feature was added to PL/SQL in Oracle8i and is covered near the end of this chapter in Section 13.4.

13.1 DML in PL/SQL

From within any PL/SQL block of code you can execute DML statements (INSERTs, UPDATEs, and DELETEs) against any and all data structures to which you have access.

Access to these data structures is determined at the time of compilation when you're using the definer rights model. If you instead use the invoker rights model with the AUTHID CURRENT_USER compile option, access privileges are determined at runtime. See Chapter 20 for more details.