6.9.1 The %FOUND Attribute

The %FOUND attribute reports on the status of your most recent FETCH against the cursor. The attribute evaluates to TRUE if the most recent FETCH against the explicit cursor returned a row, or FALSE if no row was returned.

If the cursor has not yet been opened, a reference to the %FOUND attribute raises the INVALID_CURSOR exception. You can evaluate the %FOUND attribute of any open cursor, because you reference the cursor by name.

In the following example, I loop through all the callers in the caller_cur cursor, assign all calls entered before today to that particular caller, and then fetch the next record. If I have reached the last record, then the %NOTFOUND attribute is set to TRUE and I exit the simple loop.

OPEN caller_cur;
LOOP
   FETCH caller_cur INTO caller_rec;
   EXIT WHEN NOT caller_cur%FOUND;

   UPDATE call
      SET caller_id = caller_rec.caller_id
    WHERE call_timestamp < SYSDATE;
END LOOP;
CLOSE call_cur;

In this next example, I keep a count of the total number of orders entered for a particular company. If I have fetched my last order (%FOUND is FALSE), then I display a message in Oracle Forms informing the user of the total number of orders:

OPEN order_cur;
LOOP
   FETCH order_cur INTO order_number, company_id;
   EXIT WHEN order_cur%NOTFOUND;
   do_other_stuff_then_keep_count;
   :order.count_orders := :order.count_orders + 1;
END LOOP;
CLOSE order_cur;

IF :order.count_orders > 1
THEN
   DBMS_OUTPUT.PUT_LINE
      ('A total of ' || TO_CHAR (:order.count_orders) ||
       ' orders have been found.');
ELSE
   /*
   || I hate to code messages like 'A total of 1 orders was found.'
   || It makes me sound illiterate. So I will include a special-case
   || message when just one order is found.
   */
   DBMS_OUTPUT.PUT_LINE('Just one order was found.');
END IF;

6.9.2 The %NOTFOUND Attribute

The %NOTFOUND attribute is the opposite of %FOUND. It returns TRUE if the explicit cursor is unable to fetch another row because the last row was fetched. If the cursor is unable to return a row because of an error, the appropriate exception is raised. If the cursor has not yet been opened, a reference to the %NOTFOUND attribute raises the INVALID_CURSOR exception. You can evaluate the %NOTFOUND attribute of any open cursor, because you reference the cursor by name.

When should you use %FOUND and when should you use %NOTFOUND? The two attributes are directly, logically opposed, so whatever you can do with one you can also do with a NOT of the other. In other words, once a fetch has been performed against the open cursor <cursor_name>, the following expressions are equivalent:

<cursor_name>%FOUND						= NOT <cursor_name>%NOTFOUND
<cursor_name>%NOTFOUND						= NOT <cursor_name>%FOUND

Use whichever formulation fits most naturally in your code. In a previous example, I issued the following statement:

EXIT WHEN NOT caller_cur%FOUND;

to terminate the loop. A simpler and more direct statement would use the %NOTFOUND instead of %FOUND, as follows:

EXIT WHEN caller_rec%NOTFOUND;

6.9.3 The %ROWCOUNT Attribute

The %ROWCOUNT attribute returns the number of records fetched from a cursor at the time that the attribute is queried. When you first open a cursor, its %ROWCOUNT is set to zero. If you reference the %ROWCOUNT attribute of a cursor that is not open, you will raise the INVALID_CURSOR exception. After each record is fetched, %ROWCOUNT is increased by one. This attribute can be referenced in a PL/SQL statement, but not in a SQL statement.

You can use %ROWCOUNT to limit the number of records fetched from a cursor. The following example retrieves only the first ten records from the cursor, providing the top ten companies placing orders in 1993:

DECLARE
   CURSOR company_cur IS
      SELECT company_name, company_id, total_order
        FROM company_revenue_view
       WHERE TO_NUMBER (TO_CHAR (order_date)) = 1993
       ORDER BY total_order DESC;
   company_rec company_cur%ROWTYPE;
BEGIN
OPEN company_cur;
LOOP
   FETCH company_cur INTO company_rec;
   EXIT WHEN company_cur%ROWCOUNT > 10 OR
             company_cur%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE
      ('Company ' || company_rec.company_name ||
       ' ranked number ' || TO_CHAR (company_cur%ROWCOUNT) || '.');
END LOOP;
CLOSE company_cur;

6.9.4 The %ISOPEN Attribute

The %ISOPEN attribute returns TRUE if the cursor is open; otherwise, it returns FALSE. In most cases when you use a cursor, you open it, fetch from it, and close it, all within one routine. Most of the time it is easy to know whether your cursor is open or closed. In some cases, however, you will spread your cursor actions out over a wider area of code, perhaps across different routines (possible if the cursor is declared in a package). If so, it will make sense to use the %ISOPEN attribute to make sure that a cursor is open before you perform a fetch:

IF NOT caller_cur%ISOPEN
THEN
   OPEN caller_cur;
END IF;
FETCH caller_cur INTO caller_rec;
...

NOTE: Remember that if you try to open a cursor that has already been opened, you will receive a runtime error:

ORA-06511: PL/SQL: cursor already open

6.9.5 Implicit SQL Cursor Attributes

When the RDBMS opens an implicit cursor to process your request (whether it is a query or an INSERT or an UPDATE), it makes cursor attributes available to you with the SQL cursor. This is not a cursor in the way of an explicit cursor. You cannot open, fetch from, or close the SQL cursor, but you can access information about the most recently executed SQL statement through SQL cursor attributes.

The SQL curs>

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.