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.
Tip
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. ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access