Properly grouped transactions can enhance the logical consistency of your database. This two-part series will give you an overview of how to use transactions with an Oracle database. It is excerpted from chapter 33 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Transactions using SQL command line begin with any single DML statement and end (either successfully or unsuccessfully) when one of the following events occurs:
Either aCOMMITor aROLLBACKstatement is executed. ACOMMITstatement makes the changes to the table permanent, while aROLLBACKundoes the changes to the table.
The user exits the SQL command-line interface session using theEXITcommand to terminate the SQL command-line session (automaticCOMMITstatement executed).
The user exits the SQL Commands GUI tool with the Autocommit box unchecked by closing the browser or clicking the Logout link (automaticROLLBACKstatement executed).
A DDL (Data Definition Language) or DCL (Data Control Language) statement is executed (automaticCOMMITstatement executed).
The database crashes (automaticROLLBACK statement executed).
The SQL command-line session crashes (automaticROLLBACKstatement executed).
In addition, you can use theSAVEPOINTstatement to subdivide the DML statements further within a transaction before you issue the finalCOMMITstatement for all DML statements within the transaction. TheSAVEPOINT statement essentially allows partial rollbacks within a transaction. We show you the statements for these events in the following sections; later in this chapter, we cover the equivalent steps using PHP scripts.
Explicit COMMIT Statement
There are many situations when you want a given set of DML statements—a transaction—to fail or succeed, ensuring data integrity. Suppose that the management team decides that to keep the salary budget the same next year, all employees who get raises must be offset by employees who get pay cuts. When the updates are made to the database, it is important that the total salary paid out every month remains constant; therefore, the total of the pay increases, and pay cuts must either all succeed or all fail. You believe that by using one of the Oracle Database XE GUI tools you can perform these salary adjustments as a transaction; you can use either the SQL Scripts tool or the SQL Commands tool to perform these tasks.
If you use the SQL Scripts tool shown in Figure 33-1, you perform two pay cuts and three pay increases in a single transaction with the total salary paid remaining constant, which was your original goal; however, you cannot use theCOMMITstatement in the SQL Scripts tool. Instead, at the completion of the script, the SQL Scripts tool automatically executes aCOMMIT. Although this appears to be an implicitCOMMIT (see the next section), it is actually an explicitCOMMITperformed on your behalf by the Scripts Editor. If the secondSELECT statement had not generated the original total, the payroll employee would have to execute additionalUPDATEstatements to ensure the final total is the same as the first total rather than just throwing out the entire transaction and restoring the table’s rows to their initial values and starting over.
Figure 33-1. Using the Oracle Database XE Scripts Editor to update salaries
In situations where you want more control over when a transaction completes, you can use SQL Commands to control the transaction by unchecking the Autocommit checkbox (shown in Figure 33-2) and executing theSELECTandUPDATE statements one at a time until you obtain the desired results, at which point you would execute an explicitCOMMIT. If you do not uncheck the Autocommit checkbox, the changes you make are saved to the database permanently after eachUPDATE statement.
If the database crashes after the secondUPDATEstatement (Figure 33-1) or after you run theUPDATEstatement by itself using SQL Commands (Figure 33-2), the results from all statements in the transaction would be removed from the database. The following statement (run twice, once at the beginning of the script and once at the end) ensures that the total of the monthly salaries is the same before and after the updates:
select sum(salary) from hr.employees;
Once you finish your updates and the total is the same as the first time you calculate it, you can run theCOMMITstatement to save the results permanently in the database.
Figure 33-2. Using SQL Commands to process a transaction
Please check back next week for the conclusion to this series.