11.2 How do I…Issue multiple record queries with explicit cursors?

Problem

I need to query multiple records within PL/SQL. I would like more control over querying data within PL/SQL than I have with implicit cursors. After the results of the query have been moved into PL/SQL, I need to be able to navigate through the record set. How do I issue multiple record queries with explicit cursors?

Technique

To perform a multiple record query in PL/SQL, a cursor must be used. A cursor is a pointer to an area in the Process Global Area (PGA) on the server called the context area. Explicit cursors can be defined only by using PL/SQL, which is an extension of SQL. When a query is executed on the server, the set of rows the query returns is contained in the context area and can be retrieved to the client application through operations on a cursor.

The general flow of the PL/SQL routine is to define a cursor using a SELECT statement, OPEN the cursor, repeatedly FETCH data, and then CLOSE the cursor.

The OPEN command executes the SELECT query of the cursor. How-To’s 11.4 and 11.5 give more detail on the OPEN command. When the FETCH command is used, the values of the record returned from the cursor are pushed into variables. This is where PL/SQL and explicit cursors have great value. Other tables can be updated based on the values of the variables. Other procedures or packages can also be called and programs started, among other possibilities. In this How-To, the results of the query are returned to the user. The CLOSE command is used to release memory associated with the cursor and generally to end the PL/SQL routine.

Steps

1. Connect to SQL*Plus as the WAITE user account. If you have not already run CHP11_1.SQL from How-To 11.1, run it now to create the DEPT11 table and populate it with sample data.

2. After the DEPT11 table has been created, run the CHP11_3.SQL file, as shown in Figure 11.3. The PL/SQL block contained in the file demonstrates the use of an explicit cursor.

SQL> START CHP11_3.sql

DEPT_NO = 1 DEPT_NAME = Marketing

DEPT_NO = 2 DEPT_NAME = Sales

DEPT_NO = 3 DEPT_NAME = I/S

DEPT_NO = 4 DEPT_NAME = Finance

PL/SQL procedure successfully completed.

SQL>

The four records present in the DEPT11 table are fetched in a loop and the column values are displayed, but when the %FOUND attribute evaluates to FALSE, the loop is exited.

How It Works

Lines 2 and 3 contain the declarative section of the block. An explicit cursor is defined in line 2, and line 3 uses the %ROWTYPE attribute to declare a record variable representing all the columns returned by the cursor. Alternatively, you can use DEPT11%ROWTYPE to mean the same thing. Lines 5 through 12 contain the executable section of the block. Line 5 opens the cursor, and the loop in lines 7 through 11 fetches the next row from the result set. The first FETCH statement in line 6 is necessary to set the %FOUND attribute before entering the WHILE loop, which tests the attribute value on each entry into the loop.

After a FETCH is issued to retrieve the next row from the cursor, the %FOUND attribute is set to TRUE, if the FETCH was successful, and FALSE otherwise. The WHILE loop continues as long as the %FOUND attribute evaluates to TRUE. For each row that is fetched, the column values are displayed in lines 8 and 9. If the FETCH fails to retrieve a row from the cursor, the %FOUND attribute is set to FALSE and the loop is exited. Line 12 closes the cursor to release system resources used by the cursor. Although not a recommended practice, you can use the Boolean NOT operator for negation. NOT CURSOR_NAME%FOUND is equivalent to %NOTFOUND, and NOT CURSOR_NAME%NOTFOUND is equivalent to %FOUND.

Comments

This How-To shows some examples of how much more flexible explicit cursors are than implicit cursors. Much of the rest of this chapter provides more coverage of explicit cursors and PL/SQL. How-To 11.3 describes how to use cursor attributes, such as %FOUND, in more detail. How-To 11.4 shows how explicit cursor parameters can enhance reusability. How-To 11.5 shows how to use cursor variables.