Introduction to Cursor Attributes

This section describes each of the different cursor attributes at a high level. They are explored in more detail for each of the kinds of cursors throughout this chapter, as well as in Chapter 13 and Chapter 15.

PL/SQL offers a total of six cursor attributes, as shown in Table 14-1.

Table 14-1. Cursor attributes
Name	Description
%FOUND	Returns TRUE if record was fetched successfully, FALSE otherwise.
%NOTFOUND	Returns TRUE if record was not fetched successfully, FALSE otherwise.
%ROWCOUNT	Returns number of records fetched from cursor at that point in time.
%ISOPEN	Returns TRUE if cursor is open, FALSE otherwise.
%BULK_ROWCOUNT	Returns the number of records 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 from the implicit cursor? (Notice that the "name" of my cursor in this case is "SQL".)

· DECLARE· TYPE num_tab IS TABLE OF NUMBER;· deptnums num_tab;· BEGIN· SELECT deptno· BULK COLLECT INTO deptnums· FROM dept;· · DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);END;

The following sections offer brief descriptions of each of the cursor attributes.

14.1.3.1 The %FOUND attribute

The %FOUND attribute reports on the status of your most recent FETCH against the cursor. This 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 explicit cursor's %FOUND attribute is set to FALSE and I exit the simple loop. After my UPDATE statement, I check the implicit cursor's %FOUND attribute as well.

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; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE ( 'Calls updated for ' || caller_rec.caller_id); END IF;END LOOP;CLOSE caller_cur;

14.1.3.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? Use whichever formulation fits most naturally in your code. In the previous example, I issued the following statement to exit my loop:

EXIT WHEN NOT caller_cur%FOUND;

An alternate and perhaps more readable formulation might use %NOTFOUND instead, as follows:

EXIT WHEN caller_cur%NOTFOUND;

14.1.3.3 The %ROWCOUNT attribute

The %ROWCOUNT attribute returns the number of records fetched from a cursor at the time 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.

Use %ROWCOUNT to verify that the expected number of rows have been fetched (or updated, in the case of DML) or to stop your program from executing after a certain number of iterations.

14.1.3.4 The %ISOPEN attribute

The %ISOPEN attribute returns TRUE if the cursor is open; otherwise, it returns FALSE.

14.1.3.5 The %BULK_ROWCOUNT attribute

The %BULK_ROWCOUNT attribute, designed for use with the FORALL statement, returns the number of rows processed by each DML execution. This attribute has the semantics of an associative array. It is covered in more detail in Chapter 13.

14.1.3.6 The %BULK_EXCEPTIONS attribute

The %BULK_EXCEPTIONS attribute, designed for use with the FORALL statement, returns exception information that may have been raised by each DML execution. This attribute has the semantics of an associative array of records. It is covered in more detail in Chapter 13.

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 eror: PLS-00229: Attribute expression within SQL expression

14.1.4 Referencing PL/SQL Variables in a Cursor

Because a cursor must be associated with a SELECT statement, every cursor must reference at least one table from the database and determine from that (and from the WHERE clause) which rows will be returned in the active set. This does not mean, however, that a PL/SQL cursor's SELECT may return only database information.

The list of expressions that appears after the SELECT keyword and before the FROM keyword is called the select list. In native SQL, this select list may contain both columns and expressions (SQL functions on those columns, constants, etc.). In PL/SQL, the select list of aSELECT may contain PL/SQL variables and complex expressions.

In the following cursor, the SELECT statement retrieves rows based on the employee table, but the information returned in the select list contains a combination of table columns, a PL/SQL variable, and a bind variable from the host environment (such as an Oracle Forms item):

DECLARE /* A local PL/SQL variable */ projected_bonus NUMBER := 1000; /* || Cursor adds $1000 to the salary of each employee || hired more than three years ago. */ CURSOR employee_cur IS SELECT employee_id, salary + projected_bonus new_salary, /* Column alias */ :review.evaluation /* Bind variable */ FROM employee WHERE hiredate < ADD_MONTHS (SYSDATE, -36); BEGIN ...END;

You can reference local PL/SQL program data (PL/SQL variables and constants), as well as host language bind variables in the WHERE, GROUP BY, andHAVING clauses of the cursor's SELECT statement.