Oracle PL/SQL Programming, 5th Edition

Chapter 14. 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, MERGEs, and, of course, queries.

Note

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 16.

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. It focuses on exploring the intersection point of DML and PL/SQL, answering such questions as: How can you take full advantage of DML from within the PL/SQL language? And how do you manage transactions that are created implicitly when you execute DML statements? See Transaction Management.

To appreciate the importance of transactions 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 ...