Section 1.10. Querying Data

1.10. Querying Data

PL/SQL programs query information from the database with the SQL SELECT statement. Because PL/SQL is tightly integrated with the SQL language, you can execute this SELECT statement natively in your PL/SQL block, as shown below:

     DECLARE        l_employee employee%ROWTYPE;     BEGIN        SELECT * INTO l_employee          FROM employee         WHERE employee_id = 7500;     END;

This SELECT INTO is an example of an implicit cursor, and is just one of several ways you can query data from within a PL/SQL block. You have these choices:

Implicit cursors: A simple and direct SELECT...INTO retrieves a single row of data into local program variables. It's the easiest (and often the most efficient) path to your data, but it can often lead to coding the same or similar SELECTs in multiple places in your code.
Explicit cursors: You can declare the query explicitly in your declaration section (local block or package). In this way, you can open and fetch from the cursor in one or more programs, with a granularity of control not available with implicit cursors .
Cursor variables: Offering an additional level of flexibility, cursor variables (declared from a REF CURSOR type) allow you to pass a pointer to a query's underlying result set from one program to another. Any program with access to that variable can open, fetch from, or close the cursor.
Cursor expressions: Introduced in Oracle9i Database, the CURSOR expression transforms a SELECT statement into a REF CURSOR result set and can be used in conjunction with table functions (described in Chapter 3) to improve the performance of applications.

Chapter 2 describes cursors in detail.

1.10.1. Typical Query Operations

Regardless of the type of query cursor, PL/SQL performs the same operations to execute a SQL statement from within your program. In some cases, PL/SQL takes these steps for you. In others, such as with explicit cursors , you will write the code for these steps yourself.

Parse: The first step in processing a SQL statement is to parse it to make sure it is valid and to determine the execution.
Bind: When you bind, you associate values from your program (host variables) with placeholders inside your SQL statement. With static SQL, the PL/SQL engine itself performs these binds. With dynamic SQL, you must explicitly request a binding of variable values if you wish to use bind variables.
Open: When you open a cursor, the bind variables are used to determine the result set for the SQL statement. The pointer to the active or current row is set to the first row. Sometimes you will not explicitly open a cursor; instead, the PL/SQL engine will perform this operation for you (as with implicit cursors or native dynamic SQL).
Execute: In the execute phase, the statement is run within the SQL engine.
Fetch: If you are performing a query, the FETCH command retrieves the next row from the cursor's result set. Each time you fetch, PL/SQL moves the pointer forward in the result set. When you are working with explicit cursors, remember that FETCH does nothing (does not raise an error) if there are no more rows to retrieve.
Close: This step closes the cursor and releases all memory used by the cursor. Once closed, the cursor no longer has a result set. Sometimes you will not explicitly close a cursor; instead, the PL/SQL engine will perform this operation for you (as with implicit cursors or native dynamic SQL).

1.10.2. Cursor Attributes

PL/SQL offers a set of cursor attributes that can be used to obtain information about the cursor's state. These attributes are shown in Table 1-5. Generally, if you try to reference one of these attributes for a cursor that has not yet been opened, Oracle will raise the INVALID_CURSOR exception.

Table 1-5. Cursor attributes
Name	Description
%FOUND	Returns TRUE if data was fetched, FALSE otherwise
%NOTFOUND	Returns TRUE if data was not fetched, FALSE otherwise
%ROWCOUNT	Returns number of rows fetched from cursor at that point in time
%ISOPEN	Returns TRUE if cursor is open, FALSE otherwise
%BULK_ROWCOUNT	Returns the number of rows modified by the FORALL statement for each collection element
%BULK_EXCEPTIONS	Returns exception information for rows modified by the FORALL statement for each collection element

To reference a cursor attribute, attach it to the name of the cursor or cursor variable about which you want information. Here are some examples:

Is the explicit cursor still open?

 DECLARE    CURSOR happiness_cur IS SELECT simple_delights FROM ...; BEGIN    OPEN happiness_cur;    ...    IF happiness_cur%ISOPEN THEN ...

How many rows did I retrieve with the implicit bulk query? (Notice that the "name" of my cursor in this case is the generic "SQL.")

 DECLARE    TYPE id_nt IS TABLE OF department.department_id;    deptnums   id_nt; BEGIN    SELECT department_id      BULK COLLECT INTO deptnums      FROM department;    DBMS_OUTPUT.PUT_LINE (SQL%BULK_ROWCOUNT); END;

You can reference cursor attributes in your PL/SQL code, as shown in the preceding example, but you cannot use those attributes inside a SQL statement. For example, if you try to use the %ROWCOUNT attribute in the WHERE clause of a SELECT:

     SELECT caller_id, company_id        FROM caller WHERE company_id = company_cur%ROWCOUNT;

you will get a compile error:

     PLS-00229: Attribute expression within SQL expression

1.10.3. Implicit Cursors

PL/SQL declares and manages an implicit cursor every time you execute a SQL DML statement (INSERT, UPDATE, or DELETE) or a SELECT INTO that returns a single row from the database directly into a PL/SQL data structure. This kind of cursor is called implicit because Oracle implicitly or automatically handles many of the cursor-related operations for you, such as allocating a cursor, opening the cursor, fetching, and so on.

An implicit query is a SELECT statement that has the following special characteristics:

The SELECT statement appears in the executable section of your block; it is not defined in the declaration section, as explicit cursors are.
The query contains an INTO clause (or BULK COLLECT INTO for bulk processing). The INTO clause is a part of the PL/SQL (not the SQL) language and is the mechanism used to transfer data from the database into local PL/SQL data structures.
You do not open, fetch, or close the SELECT statement; all of these operations are done for you.

The general structure of an implicit query is as follows:

     SELECT column_list       [BULK COLLECT] INTO PL/SQL variable list       ...rest of SELECT statement...

If you use an implicit cursor, Oracle performs the open, fetches, and close for you automatically; these actions are outside your programmatic control. You can, however, obtain information about the most recently executed SQL statement by examining the values in the implicit SQL cursor attributes, as explained later in "Implicit SQL cursor attributes."

In the following sections, the term implicit cursor means a SELECT INTO statement that retrieves (or attempts to retrieve) a single row of data. Later we'll discuss the SELECT BULK COLLECT INTO variation that allows you to retrieve multiple rows of data with a single implicit query.

Here is an example of an implicit query that retrieves an entire row of information into a record:

     DECLARE        l_book book%ROWTYPE;     BEGIN        SELECT *          INTO l_book          FROM book         WHERE isbn = '0-596-00121-5';

1.10.3.1. Error handling with implicit cursors

The implicit cursor version of the SELECT statement is a "black box." You pass the SQL statement to the SQL engine in the database, and it returns a single row of information. You can't get inside the separate operations of the cursor, such as the open, fetch, and close stages. You are also stuck with the fact that Oracle will automatically raise exceptions from within the implicit SELECT for two common outcomes:

The query does not find any rows matching your criteria. In this case, Oracle raises the NO_DATA_FOUND exception .
The SELECT statement returns more than one row. In this case, Oracle raises the TOO_MANY_ROWS exception.

1.10.3.2. Implicit SQL cursor attributes

Oracle allows you to access information about the most recently executed implicit cursor by referencing the special implicit cursor attributes shown in Table 1-6. The table describes the significance of the values returned by these attributes for an implicit SQL query (SELECT INTO). Because the cursors are implicit, they have no name and, therefore, the keyword "SQL" is used to denote the implicit cursor. These implicit cursors also apply to implicit DML statements: INSERT, UPDATE, and DELETE.

Table 1-6. Implicit query cursor attributes and their values
Name	Description
SQL%FOUND	Returns TRUE if one row (or more in the case of BULK COLLECT INTO) was fetched or modified, FALSE otherwise (in which case Oracle will also raise the NO_DATA_FOUND exception).
SQL%NOTFOUND	Returns TRUE if a row was not fetched or no rows were modified by a DML statement, FALSE otherwise.
SQL%ROWCOUNT	Returns the number of rows fetched from or modified by the specified cursor. For a SELECT INTO, this will be 1 if a row was found and 0 if Oracle raises the NO_DATA_FOUND exception.
SQL%ISOPEN	Always returns FALSE for implicit cursors, because Oracle opens and closes implicit cursors atomically.

All the implicit cursor attributes return NULL if no implicit cursors have yet been executed in the session. Otherwise, the values of the attributes always refer to the most recently executed SQL statement, regardless of the block or program from which the SQL statement was executed.

1.10.4. Explicit Cursors

An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of your code and, in the process, assigned a name. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements.

With explicit cursors , you have complete control over the different PL/SQL steps involved in retrieving information from the database. You decide when to OPEN the cursor, when to FETCH records from the cursor (and therefore from the table or tables in the SELECT statement of the cursor), how many records to fetch, and when to CLOSE the cursor. Information about the current state of your cursor is available through examination of cursor attributes. This granularity of control makes the explicit cursor an invaluable tool for your development effort.

Let's look at an example. The following function determines (and returns) the level of jealousy I should feel for my friends, based on their location.

      1  CREATE OR REPLACE FUNCTION jealousy_level (      2     NAME_IN   IN   friends.NAME%TYPE) RETURN NUMBER      3  AS      4     CURSOR jealousy_cur      5     IS      6        SELECT location FROM friends      7         WHERE NAME = UPPER (NAME_IN);      8      9     jealousy_rec   jealousy_cur%ROWTYPE;     10     retval         NUMBER;     11  BEGIN     12     OPEN jealousy_cur;     13     14     FETCH jealousy_cur INTO jealousy_rec;     15     16     IF jealousy_cur%FOUND     17     THEN     18        IF jealousy_rec.location = 'PUERTO RICO'     19           THEN retval := 10;     20        ELSIF jealousy_rec.location = 'CHICAGO'     21           THEN retval := 1;     22        END IF;     23     END IF;     24     25     CLOSE jealousy_cur;     26     27     RETURN retval;     28  END;

This PL/SQL block performs the following cursor actions:

Line(s)	Description
47	Declare the cursor
9	Declare a record based on that cursor
12	Open the cursor
14	Fetch a single row from the cursor
16	Check a cursor attribute to determine if a row was found
1822	Examine the contents of the fetched row to calculate my level of jealousy
25	Close the cursor

To use an explicit cursor, you must first declare it in the declaration section of your PL/SQL block or in a package, as shown here:

     CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]        [ RETURN return_specification ]        IS SELECT_statement            [FOR UPDATE [OF [column list]];

where cursor_name is the name of the cursor, return_specification is an optional RETURN clause for the cursor, and SELECT_statement is any valid SQL SELECT statement. You can also pass arguments into a cursor through the optional parameter list. Once you have declared a cursor, you can OPEN it and FETCH from it.

As with implicit cursors, Oracle provides attributes for explicit cursors . Table 1-7 shows you the attribute values you can expect to see both before and after the specified cursor operations.

Table 1-7. Explicit cursor "before and after" attribute values
	%FOUND	%NOTFOUND	%ISOPEN	%ROWCOUNT
Before OPEN	ORA-01001 raised	ORA-01001 raised	FALSE	ORA-01001 raised
After OPEN	NULL	NULL	TRUE	0
Before first FETCH	NULL	NULL	TRUE	0
After first FETCH	TRUE	FALSE	TRUE	1
Before subsequent FETCH(es)	TRUE	FALSE	TRUE	1
After subsequent FETCH(es)	TRUE	FALSE	TRUE	Data dependent
Before last FETCH	TRUE	FALSE	TRUE	Data dependent
After last FETCH	FALSE	TRUE	TRUE	Data dependent
Before CLOSE	FALSE	TRUE	TRUE	Data dependent
After CLOSE	Exception	Exception	FALSE	Exception

1.10.5. BULK COLLECT

Oracle8i Database introduced a very powerful new feature that improves the efficiency of queries in PL/SQL: the BULK COLLECT clause . With BULK COLLECT, you can retrieve multiple rows of data through either an implicit or an explicit query with a single roundtrip to and from the database. BULK COLLECT reduces the number of context switches between the PL/SQL and SQL engines and, thereby, reduces the overhead of retrieving data. The syntax for this clause is:

     ... BULK COLLECT INTO collection_name[, collection_name] ...

where collection_name identifies a collection. Here are some rules and restrictions to keep in mind when using BULK COLLECT:

Prior to Oracle9i Database, you could use BULK COLLECT only with static SQL. With Oracle9i Database and Oracle Database 10g, you can use BULK COLLECT with both dynamic and static SQL.
You can use BULK COLLECT keywords in any of the following clauses: SELECT INTO, FETCH INTO, and RETURNING INTO.
The collections you reference can store only scalar values (strings, numbers, dates). In other words, you cannot fetch a row of data into a record structure that is a row in a collection.
The SQL engine automatically initializes and extends the collections you reference in the BULK COLLECT clause. It starts filling the collections at index 1, inserts elements consecutively (densely), and overwrites the values of any elements that were previously defined.
You cannot use the SELECT...BULK COLLECT statement in a FORALL statement.
SELECT...BULK COLLECT will not raise NO_DATA_FOUND if no rows are found. Instead, you must check the contents of the collection to see if there is any data inside it.
The BULK COLLECT operation empties the collection referenced in the INTO clause before executing the query. If the query returns no rows, this collection's COUNT method will return 0.

1.10.5.1. Limiting rows retrieved with BULK COLLECT

Oracle provides a LIMIT clause for BULK COLLECT that allows you to limit the number of rows fetched from the database. The syntax is:

     FETCH cursor BULK COLLECT INTO ... [LIMIT rows];

where rows can be any literal, variable, or expression that evaluates to an integer (otherwise, Oracle will raise a VALUE_ERROR exception).

LIMIT is very useful with BULK COLLECT, because it helps you manage how much memory your program will use to process data. Suppose, for example, that you need to query and process 10,000 rows of data. You could use BULK COLLECT to retrieve all those rows and populate a rather large collection. However, this approach will consume lots of memory in the Process Global Area for that session. If this code is run by many separate Oracle schemas, your application performance may degrade because of PGA swapping.

The following block of code uses the LIMIT clause in a FETCH that is inside a simple loop. Notice that I check the %NOTFOUND attribute after the FETCH to see if any more rows were retrieved.

     DECLARE        CURSOR allrows_cur IS SELECT * FROM EMPLOYEE;        TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE           INDEX BY BINARY_INTEGER;        l_employees employee_aat;        l_row PLS_INTEGER;     BEGIN        OPEN allrows_cur;        LOOP           FETCH allrows_cur BULK COLLECT INTO l_employees              LIMIT 100;           EXIT WHEN allrows_cur%NOTFOUND;           -- Process the data by scanning through the collection.           l_row := l_employees.FIRST;           WHILE (l_row IS NOT NULL)           LOOP              upgrade_employee_status (l_employees(l_row).employee_id);              l_row := l_employees.NEXT (l_row);           END LOOP;        END LOOP;        CLOSE allrows_cur;     END;

1.10.6. Cursor Variables and REF Cursors

A cursor variable is a variable that points to or references an underlying cursor. Unlike an explicit cursor, which names the PL/SQL work area for the result set, a cursor variable is a reference to that work area. Explicit and implicit cursors are static in that they are tied to specific queries. The cursor variable can be opened for any query, even for different queries within a single program execution.

1.10.6.1. Declaring REF CURSOR types

You must perform two distinct declaration steps in order to work with a cursor variable:

Create a referenced cursor TYPE.
Declare the actual cursor variable based on that type.

The syntax for creating a referenced cursor type is as follows:

     TYPE cursor_type_name IS REF CURSOR [ RETURN return_type ];

where cursor_type_name is the name of the type of cursor, and return_type is the RETURN data specification for the cursor type. The return_type can be any of the data structures valid for a normal cursor RETURN clause and is defined using the %ROWTYPE attribute or by referencing a previously defined record TYPE.

Notice that the RETURN clause is optional with the REF CURSOR type statement. Both of the following declarations are valid:

     TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;     TYPE generic_curtype IS REF CURSOR;

The first form of the REF CURSOR statement is called a strong type because it attaches a record type (or row type) to the cursor variable type at the moment of declaration. Any cursor variable declared using that type can be used only with SQL statement and FETCH INTO data structures that match the specified record type. The advantage of a strong REF TYPE is that the compiler can determine whether or not the developer has properly matched up the cursor variable's FETCH statements with its cursor object's query list.

The second form of the REF CURSOR statement, in which the RETURN clause is missing, is called a weak type. This cursor variable type is not associated with any record data structures. Cursor variables declared without the RETURN clause can be used in more flexible ways than the strong type. They can be used with any query, with any rowtype structure, and can vary even within the course of a single program.

Starting with Oracle9i Database, Oracle provides a predefined weak REF CURSOR type named SYS_REFCURSOR . You no longer need to define your own weak type; just use Oracle's:

     DECLARE        my_cursor SYS_REFCURSOR;

1.10.6.2. Declaring cursor variables

The syntax for declaring a cursor variable is:

     cursor_name cursor_type_name;

where cursor_name is the name of the cursor, and cursor_type_name is the name of the type of cursor previously defined with a TYPE statement .

Here is an example of the creation of a cursor variable:

     DECLARE        /* Create a cursor type for sports cars. */        TYPE sports_car_cur_type IS REF CURSOR RETURN car%ROWTYPE;        /* Create a cursor variable for sports cars. */        sports_car_cur sports_car_cur_type;     BEGIN        ...     END;

1.10.6.3. Opening cursor variables

You assign a value (the cursor object) to a cursor when you OPEN the cursor. So the syntax for the traditional OPEN statement allows for cursor variables to accept a SELECT statement after the FOR clause, as shown below:

     OPEN cursor_name FOR SELECT_statement;

where cursor_name is the name of a cursor or cursor variable and SELECT_statement is a SQL SELECT statement.

For strong REF CURSOR type cursor variables, the structure of the SELECT statement (the number and datatypes of the columns) must match or be compatible with the structure specified in the RETURN clause of the TYPE statement. If cursor_name is a cursor variable defined with a weak REF CURSOR type, you can OPEN it for any query, with any structure.

1.10.6.4. Fetching from cursor variables

As mentioned earlier, the syntax for a FETCH statement using a cursor variable is the same as that for static cursors:

     FETCH cursor_variable_name INTO record_name;     FETCH cursor_variable_name INTO variable_name, variable_name ...;

When the cursor variable is declared with a strong REF CURSOR type, the PL/SQL compiler makes sure the data structures listed after the INTO keyword are compatible with the structure of the query associated with the cursor variable.

If the cursor variable is of the weak REF CURSOR type, the PL/SQL compiler cannot perform the same kind of check it performs for a strong REF CURSOR type. Such a cursor variable can FETCH into any data structures because the REF CURSOR type is not identified with a rowtype at the time of declaration. At compile time, there is no way to know which cursor object (and associated SQL statement) will be assigned to that variable.

Consequently, the check for compatibility must happen at runtime, when the FETCH is about to be executed. At this point, if the query and the INTO clause do not structurally match, then the PL/SQL runtime engine will raise the predefined ROWTYPE_MISMATCH exception . Note that PL/SQL will use implicit conversions if necessary and possible.