|
Chapter 6 Database Interaction and Cursors |
|
You can manipulate cursors using the OPEN, FETCH, and CLOSE statements. When you need to get information about the current status of your cursor, or the result of the last fetch from a cursor, you will access cursor attributes.
Both explicit and implicit cursors have four attributes, as shown in Table 6.1 .
|
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. |
To obtain information about the execution of the cursor, you append the cursor attribute name to the name of your cursor. For example, if you declare a cursor as follows:
CURSOR caller_cur IS SELECT caller_id, company_id FROM caller;
then the four attributes associated with the cursor are:
caller_cur%FOUND caller_cur%NOTFOUND caller_cur%ROWCOUNT caller_cur%ISOPEN
Some of the ways you can access the attributes of an explicit cursor are shown below in bold:
DECLARE CURSOR caller_cur IS SELECT caller_id, company_id FROM caller; caller_rec caller_cur%ROWTYPE; BEGIN /* Only open the cursor if it is not yet open */ IF NOT
caller_cur%ISOPEN
THEN OPEN caller_cur END IF; FETCH caller_cur INTO caller_rec; /* Keep fetching until no more records are FOUND */ WHILE
caller_cur%FOUND
LOOP DBMS_OUTPUT.PUT_LINE ('Just fetched record number ' || TO_CHAR
(caller_cur%ROWCOUNT));
FETCH caller_cur INTO caller_rec; END LOOP;
CLOSE caller_cur;
END;
PL/SQL does provide these same attributes for an implicit cursor. Because an implicit cursor has no name, PL/SQL assigns the generic name SQL to it. Using this name, you can access the attributes of an implicit cursor. For more information on this topic, see Section 6.9.5, "Implicit SQL Cursor Attributes" later in the chapter.
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. If you try to use the %ROWCOUNT attribute in the WHERE clause of a SELECT, for example:
SELECT caller_id, company_id FROM caller WHERE company_id = company_cur%ROWCOUNT;
then you will get a compile error:
PLS-00229: Attribute expression within SQL expression
The four explicit cursor attributes are examined in detail in the following sections.
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;
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;
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;
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
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 cursor has the same four attributes as an explicit cursor:
SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT SQL%ISOPEN
The values returned by implicit cursor attributes differ from those of explicit cursor attributes in the following ways:
If the RDBMS has not opened an implicit SQL cursor in the session, then the SQL%ROWCOUNT attribute returns NULL instead of raising the INVALID_CURSOR error. References to the other attributes (ISOPEN, FOUND, NOTFOUND) all return FALSE. You will never raise the INVALID_CURSOR error with an implicit cursor attribute reference.
The %ISOPEN attribute will always return FALSE -- before and after the SQL statement. After the statement is executed (whether it is SELECT, UPDATE, DELETE, or INSERT), the implicit cursor will already have been opened and closed implicitly. An implicit cursor can never be open outside of the statement itself.
SQL cursor attributes are always set according to the results of the most recently executed SQL statement. That SQL statement might have been executed in a stored procedure which your program called; you might not even be aware of that SQL statement execution. If you plan to make use of a SQL cursor attribute, make sure that you reference that attribute immediately after you execute the SQL statement.
The %FOUND attribute returns TRUE if an UPDATE, DELETE, or INSERT affected at least one record. It will return FALSE if those statements failed to affect any records. It returns TRUE if an implicit SELECT returns one row.
The behavior of the %NOTFOUND attribute for UPDATE, DELETE, or INSERT statements is the opposite of %FOUND. The situation for an implicit SELECT is a bit different: when you use an implicit SELECT statement, never rely on the %NOTFOUND and %FOUND attributes.
When an implicit SELECT statement does not return any rows, PL/SQL immediately raises the NO_DATA_FOUND exception. When an implicit SELECT statement returns more than one row, PL/SQL immediately raises the TOO_MANY_ROWS exception. In either case, once the exception is raised, control shifts to the exception section of the PL/SQL block.
|
|
|
|
| 6.8 Closing Cursors |
|
6.10 Cursor Parameters |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.