Skip to Main Content
Learning Oracle PL/SQL
book

Learning Oracle PL/SQL

by Bill Pribyl, Steven Feuerstein
November 2001
Beginner content levelBeginner
424 pages
11h 11m
English
O'Reilly Media, Inc.
Content preview from 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, ...

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.
Start your free trial

You might also like

Learning Oracle PL/SQL

Learning Oracle PL/SQL

Darryl Hurley
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl

Publisher Resources

ISBN: 0596001800Supplemental ContentCatalog PageErrata