Learning Oracle PL/SQL

5.6. Advanced Data Retrieval Topics

In the previous section we looked at how a relatively advanced feature, dynamic SQL, can significantly improve your applications. This section touches on a number of other data retrieval topics that can give your programs a substantial edge. If you want to hold off learning these topics until later, feel free to move on to Chapter 6 and return here at some future time.

The topics are:

• Preventing update anomalies by locking data

• Improving performance of embedded SELECT statements

• Implementing more sophisticated searching

• Parameterizing cursors

• Using strongly typed cursor variables

5.6.1. Preventing Update Anomalies by Locking Data

In certain kinds of applications you want only the user who has fetched data to be able to change it. Your program can obtain row-level locks on the user's behalf by adding the FORUPDATE clause to the end of the SELECT statement. Locking is a rich topic, but the basic idea of this type of lock is to ensure that no one else attempts to update or delete the rows identified in the where-clause:

SELECT ...
  FROM ...
  WHERE ...
   FOR UPDATE;

Now anyone else who attempts to update the row receives an error. Once the application issues a COMMIT (or a ROLLBACK) statement, Oracle releases the lock.

In a stateless web environment you have few opportunities to use this statement, because the locks would be released after the page gets drawn but before the user does any kind of save back to the database. (In a stateful environment, ...