If you recall from Chapter 3, it is generally a good idea to have the key business logic of a PHP/Oracle application implemented inside the database. As discussed in that chapter, in simple cases, you don't even need to write PL/SQL code to move the data processing to the data—instead, you can design a complex SQL query that, when issued, instructs the database server to perform all the necessary data processing. Turning back to the example discussed in the preceding section, you might modify the UPDATE statement used there so that it updates the records representing stock managers only if the new salary of each and every stock manager is still between the minimum and maximum salary specified for the stock manager in the jobs table, thus eliminating the need to perform a separate query that returns the number of stock manager records satisfying the above condition, and, therefore, reducing the amount of code that must be written to implement the desired behavior.
In essence, this new UPDATE combines all the three steps outlined at the beginning of the preceding section within a single statement. You don't even need now to explicitly commit or roll back the UPDATE operation. Instead, you can execute that UPDATE statement in the OCI_COMMIT_ON_SUCCESS mode, which guarantees that the operation is automatically committed upon successful execution, or rolled back otherwise.
The following script shows the new UPDATE statement in action:
<?php
//File: transQuery.php
if(!$dbConn = oci_connect('hr', 'hr', '//localhost/orcl')) { $err = oci_error();
trigger_error('Could not establish a connection: ' . $err['message'], E_USER_ERROR); };
$jobno = 'ST_MAN'; $query = "
UPDATE (SELECT salary, job_id FROM employees WHERE (SELECT count(*) FROM employees WHERE job_id=:jobid AND salary*1.1 BETWEEN (SELECT min_salary FROM jobs WHERE job_id=:jobid) AND
(SELECT max_salary FROM jobs WHERE job_id=:jobid)) IN
(SELECT count(*) FROM employees WHERE job_id=:jobid) ) emp SET emp.salary = salary*1.1 WHERE emp.job_id=:jobid";
$stmt = oci_parse($dbConn,$query);
oci_bind_by_name($stmt, ':jobid', $jobno); if (!oci_execute($stmt, OCI_COMMIT_ON_SUCCESS)) { $err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR); };
$updrows = oci_num_rows($stmt); if ($updrows>0) { print "Transaction is committed";
} else {
print "Transaction is rolled back"; }
?>
Here, you define the UPDATE statement that will update all the records representing stock managers, increasing their salaries by 10%, provided that each and every new salary doesn't exceed the maximum salary defined for the stock manager in the jobs table. If at least one new salary exceeds the maximum salary, the UPDATE statement will update no rows.
dml_table_expression_clause of the UPDATE statement, you specify the SELECT statement that returns either all the records from the employees table or none of them, depending on whether or not all the records that satisfy the condition in the WHERE clause of the UPDATE statement (all records representing stock managers, in this case) can be updated so that the new salary in each of the records being updated does not exceed the maximum salary.
This SELECT statement is referred to as an inline view. Unlike regular views discussed in the Taking Advantage of Views section in Chapter 3, inline views are not database schema objects but subqueries that can be referenced only within their containing statements, using aliases. In this example, using the emp inline view in the UPDATE statement eliminates the need to separately perform the query that returns the number of records representing stock managers and then figure out whether that number is equal to the number of rows actually affected by the UPDATE statement. Now the script has to perform only one SQL statement to get the job done, thus reducing the script execution time significantly.
The above is a good example of how you can benefit from moving the key business logic of a PHP/Oracle application from PHP to Oracle. In this example, rather than using two separate statements and analyzing their results in PHP, you employ only one SQL statement that makes the database server perform all the required data processing.
Also note how binding is performed in this example. You bind the jobno PHP variable to the jobid placeholder used in the UPDATE statement. It is interesting to note that the jobid placeholder appears in the statement more than one time. Unlike the previous example where the UPDATE statement was executed in the OCI_DEFAULT mode, which explicitly opens a transaction, in this example you execute the statement in the OCI_COMMIT_ON_SUCCESS mode, thus committing the UPDATE operation automatically upon successful execution.
As mentioned earlier, OCI_COMMIT_ON_SUCCESS is the default execution mode. This means that you do not need to explicitly specify it when calling oci_execute. In this example, it is specified explicitly just to emphasize the point.
In the previous example, you still use the oci_num_rows function to obtain the number of rows affected by the UPDATE statement. However, this time you don't need to compare that number with the total number of records representing stock managers, as you did it in the preceding example. All you need to find out here is whether or not the number of rows affected by the UPDATE statement is greater than 0. If the number of updated rows is greater than 0, this automatically means that the UPDATE operation has modified all the records representing stock managers and has been successfully committed. In this case, all you need to do is output a message informing the user that the transaction is committed.
If the number of updated rows is equal to 0, this means that the UPDATE operation did not affect any rows. In this case, all you have to do is to output a message informing the user that the transaction is rolled back. However, in reality the transaction has committed but no rows were affected by the UPDATE operation.
Developing Transactional Code
So far, you have seen a few simple examples that showed the basics of how Oracle transactions work in PHP. This section takes you through more complex examples of using transactions in PHP/Oracle applications.