Page 189
This chapter offers you a more complex and sophisticated view of PL/SQL, and introduces you to two additional looping procedures that offer more power. This chapter also explains how procedures and functions are named and executed. The chapter also discusses Oracle's package concept. This is a very object-oriented concept that enables the developer to group procedures, functions, and cursors together.
Page 190
In the preceding chapter, several examples used the %notfound cursor attribute in If statements. The attribute was used in a condition expression to determine whether the looping procedure should be terminated. Without the %notfound attribute, it would have been very difficult to stop the loop at the desired time. Cursor attributes are devices that determine the status of the cursor. Determining this status is important for knowing when to terminate loops or perform other procedures. The syntax of the cursor attribute is the name of the cursor followed by the cursor attribute. The name is needed to identify the particular cursor in which the application is interested. It is common to have multiple cursors open at a given time.
Table 9.1 lists PL/SQL's four different cursor attributes.
Table 9.1 Cursor Attributes
Name | Description |
%found | This attribute is true when the last fetch returned a record from thecursor. When it did not, the attribute is false. |
%notfound | This attribute is false when the last fetch returned a record from the cursor. It is true when the fetch did not retrieve a record. |
%rowcount | This attribute returns the number of records fetched from the cursor up to that point. |
%isopen | This attribute is true when the named cursor is currently open. It is false when it is closed. |
The %found attribute returns a value of true when the last fetch returned a record from the cursor. This attribute and its exact opposite, %notfound, are useful evaluation devices in a condition statement. In the preceding chapter, several examples use the %notfound in if or when statements as a test to execute the Exit loop command. These are arguably the best tools to determine the proper time to close a looping procedure.
NOTE |
Many of the applications I develop contain nested cursors in nested while loops. The nested cursors are used to retrieve records from a related table for each record selected from the base table. The cursor attributes are a good tool to help the applications control the opening and closing of the cursors.n |
Listing 9.1 demonstrates the use of the %found and %notfound cursor attributes. The listing displays the number of tools and eyeglasses purchased by the employees in the company.
Page 191
Listing 9.1 L_09_01.SQLUsing the %found and %notfound Cursor Attributes to Terminate Looping Procedures
SQL> set serveroutput on; SQL> declare 2 cnt number := 0; 3 tool_record tools%rowtype; 4 glasses_record glasses%rowtype; 5 cursor tool is select fk_payroll_number from tools; 6 cursor glass is select fk_payroll_number from glasses; 7 begin 8 open tool; 9 loop 10 fetch tool into tool_record.fk_payroll_number; 11 exit when tool%notfound; 12 cnt := cnt + 1; 13 end loop; 14 dbms_output.put_line (cnt||' Tools were purchased'); 15 cnt := 0; 16 close tool; 17 open glass; 18 loop 19 fetch glass into glasses_record.fk_payroll_number; 20 exit when not glass%found; 21 cnt := cnt + 1; 22 end loop; 23 close glass; 24 dbms_output.put_line (cnt||' Eyeglasses were purchased'); 25 end; 26 / 34 Tools were purchased 18 Eyeglasses were purchased PL/SQL procedure successfully completed.
In this listing, two looping procedures are established. The first procedure counts the number of tool purchased by employees. It uses the %notfound attribute to determine when to stop the loop. The loop should be terminated when the last record from the cursor has been counted. The condition is contained in line 11. When the %notfound attribute is true, the exit statement can be performed. This attribute is true when the last record from the cursor has been fetched. You might notice that the count variable (cnt) on line 21 is incremented after the condition is evaluated. If it was incremented before the exit condition, it would have counted an additional record. It would have counted the iteration when the fetch was negative.
The second looping procedure uses the %found attribute as the condition in the exit. Because this attribute is true when a record is fetched, and you do not want the loop terminated when the value is true, you must use the not keyword to change the termination logic. When the %found attribute is not true, the loop is terminated. Of course, not true means the value is false. These two attributes give you a lot of control when performing loops that contain cursors.