11 Querying Data

How do I…

11.1 Issue single record queries with implicit cursors?

11.2 Issue multiple record queries with explicit cursors?

11.3 Use cursor attributes to monitor cursor state?

11.4 Use cursor parameters to enhance cursor reusability?

11.5 Use cursor variables for more flexibility?

11.6 Use wildcards in a query?

11.7 Lock rows when I query them?

11.8 Prevent the selection of duplicate rows?

11.9 Traverse a tree structure?

One of the most powerful features of Oracle is the sophisticated way it retrieves data from the database. Querying data can be as simple as retrieving all records from a single table or as complex as querying the hierarchy of tree-structured data.

Many of the topics covered in this chapter are common to all relational databases supporting SQL, whereas others are specific to Oracle, such as cursors that maintain the set of rows returned from a query temporarily and manipulate the rows one at a time. Oracle comes with powerful functions and packages to enhance SQL. Using its power to create sophisticated queries within your application means that records don’t need to be manipulated within your application. This chapter also explores methods of querying data from the database.

11.1 Issue Single Record Queries with Implicit Cursors

If you don’t explicitly define a cursor for a SELECT query, then Oracle creates an implicit one. An implicit cursor automatically performs the OPEN, FETCH, and CLOSE steps that must be specified with an explicit cursor. Several limitations, however, exist when using implicit cursors. This How-To describes how to issue queries with implicit cursors.

11.2 Issue Multiple Record Queries with Explicit Cursors

Using explicit cursors is a more controlled approach to querying data within PL/SQL than with implicit cursors. Control of processing each record returned from the SELECT statement of the cursor is possible. Also, additional exception handling processes can be implemented with explicit cursors. This How-To describes how to create a basic explicit cursor to query multiple records.

11.3 Use Cursor Attributes to Monitor Cursor State

Four attributes can be used with both implicit and explicit cursors: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT, which return useful information about the execution of a cursor. These attributes can be used in expressions by appending the attribute to the cursor name. This How-To pertains to the use of cursor attributes to monitor the state of a cursor in PL/SQL.

11.4 Use Cursor Parameters to Enhance Cursor Reusability

Parameters can be passed to a cursor just as you would pass parameters to a stored procedure. Rather than reference PL/SQL variables directly in a cursor declaration, a cursor that accepts a parameter list is much easier to read and maintain, in addition to the benefits of reusability of the cursor within the PL/SQL block. This How-To presents the techniques for using cursor parameters to enhance cursor reusability.

11.5 Use Cursor Variables for More Flexibility

A cursor is tied to a static query, but cursor variables are dynamic because you can associate different queries with a cursor variable as long as the columns returned by each query match the declaration of the cursor variable. Cursor variables are like pointers and are very useful in passing result sets between stored subprograms. This How-To explores the use of cursor variables for more flexibility.

11.6 Use Wildcards in a Query

You don’t always know the exact spelling of values in a character field, and to cope with this, wildcards enable you to query information without knowing the exact value of a field. The LIKE operator in Oracle is a powerful tool for pattern matching. In Oracle, the percent sign (%) is the wildcard and can represent any number of characters. The underscore (_) character is a position marker. It can represent any single character in its position. This How-To covers the process of using wildcards in a query.

11.7 Lock Rows When I Query Them

If you are planning to update a row after it has been queried, it is important to lock the row to prevent other users from unexpectedly updating it. The FOR UPDATE clause in a query locks the record it returns, until a COMMIT statement is executed or the session is terminated. This How-To presents the method for locking records with a query.

11.8 Prevent the Selection of Duplicate Rows

Sometimes a query returns duplicate records based on its selection criteria. If you are looking for all the unique values contained in a column, you can remove duplicate values by using the DISTINCT operator in a query. This How-To covers the technique used to prevent duplicate results in a query.

11.9 Traverse a Tree Structure

Data can be stored in a hierarchical structure within a table. An example is the reporting structure within an organization. Oracle provides support for a hierarchical structure through the CONNECT BY clause. This How-To presents the method for navigating a tree structure in Oracle.