Previous | Table of Contents | Next

Page 192

NOTE
My personal favorite attribute is the %found. I find this logic easier for me to understand.
I use it in as the loop continuation determinate in my while loops.

Using the %rowcount Attribute

The %rowcount attribute returns the current number of records that have been fetched. When the cursor is opened, this number equals 0. When the first fetch is executed, the attribute equals 1. This attribute helps to limit the number of records fetched from the cursor. You can also use it to number each fetched from the cursor. Listing 9.2 illustrates these uses.

Listing 9.2 L_09_02.SQL—Using the %rowcount Attribute to Limit the Records Fetched from a Cursor and to Number Each Fetched Row

SQL> set serveroutput on;
SQL> declare
 2   emp_record    employee%rowtype;
 3   cursor employee_list is
 4    select last_name, first_name from employee order by 1;
 5 begin
 6   open employee_list;
 7   loop
 8    fetch employee_list into emp_record.last_name, emp_record.first_name;
 9    exit when employee_list%rowcount = 5;
 10    dbms_output.put_line (employee_list%rowcount||' `||emp_record.last_name);
 11   end loop;
 12 end;
 13 /
1 ANTHONY
2 BUSH
3 CARTER
4 CLINTON

PL/SQL procedure successfully completed.

The listing uses the %rowcount attribute in the exit statement to terminate the loop after fetching the fifth record from the cursor. The attribute is also used in the dbms_output statement to number the various records displayed. Notice the %rowcount attribute is incremented before the exit statement is evaluated. Because this evaluation occurs before the record is output, only four records display. To display five records, move line 10 to line 9.

0
1 ANTHONY
2 BUSH
3 CARTER
4 CLINTON

Page 193

Using the %isopen Attribute

The last cursor attribute is %isopen. It is used to determine whether the cursor is currently open. This is a potentially important attribute to know because issuing a Fetch or Close command when the cursor is not open causes an exception. A closed cursor also causes an exception when the %found, %notfound, and %rowcount cursor attributes are evaluated. When you have a complex program and you want to avoid these exceptions, this command could save some problems. Listing 9.3 uses the %isopen to check the status of the cursor before opening it. The cursor for the script opens only if the cursor attribute %isopen is false. This evaluation occurs in line 6 and 7 of the Listing.

Listing 9.3 L_09_03.SQL—Using the %Isopen Attribute to Check Whether a Cursor Is Open Before Issuing the Open Command

SQL> set serveroutput on;
SQL> declare
 2  emp_record    employee%rowtype;
 3  cursor employee_list is
 4   select last_name, first_name from employee order by 1;
 5 begin
 6  if not employee_list%isopen then
 7   open employee_list;
 8  end if;
 9  loop
 10   fetch employee_list into emp_record.last_name, emp_record.first_name;
 11   exit when employee_list%rowcount = 5;
 12   dbms_output.put_line (employee_list%rowcount||' `||emp_record.last_name);
 13  end loop;
 14 end;
 15 /
1 ANTHONY
2 BUSH
3 CARTER
4 CLINTON

PL/SQL procedure successfully completed.

Using Explicit and Implicit Cursors

In the preceding examples, the name of the cursor preceded all the cursor attributes. Cursors that have specific names are called explicit cursors. All the cursors discussed thus far are explicit cursors.

There are a number of other cursors called implicit cursors. Oracle creates implicit cursors when Select, Insert, Update, or Delete commands have been issued. Sometimes it is important to your PL/SQL program to know the state of these cursors. Fortunately, you can use the same cursor attributes described in the preceding sections for explicit cursors with implicit cursors. The difference is the implicit cursor attributes begin with the word SQL rather than the name of the cursor. When they are used, they return the value of the most recent executed

Page 194

SQL command. The following are the implicit cursor attributes: sql%found, sql%notfound, sql%rowcount, and sql%isopen.

The While loop is the second looping procedure of three PL/SQL loops available. It consists of two components. The first is the condition statement placed at the beginning of the loop. The second is the keywords end loop, which denote the completion of the looping procedure. Because the While loop has its continuation evaluation done at the beginning of the loop, the condition must be true initially for the loop to ever be performed. This differs from the simple loop you learned about in the preceding chapter. The continuation evaluation occurs somewhere in the body of the loop. This means the condition evaluation does not have to be true to enter the looping procedure.

Listing 9.4 shows a simple While loop that displays the last names of the employees. The cursor attributes discussed earlier in this chapter are excellent devices to use with a While loop. This example uses the %found attribute to decide whether to enter and continue the loop. For the condition to be true, the last fetch from the cursor has to be true. This means the block has to have a successful fetch performed prior to the beginning of the loop. Without this statement, the loop would never be executed. The loop also has another fetch before the end. This is needed to bring a fresh record into the loop. It also is placed as the last step in the loop
because the application needs a fresh %found attribute when the program returns to the beginning of the loop. If the attribute is false because the last record was fetched, the loop terminates.

Listing 9.4 L_09_04.sql—Executing a While Loop

SQL> set serveroutput on;
SQL> declare
 2  e_rec   employee%rowtype;
 3  cursor a is select * from employee order by last_name;
 4 begin
 5  open a;
 6  fetch a into e_rec;
 7  while a%found loop
 8   dbms_output.put_line (e_rec.last_name);
 9   fetch a into e_rec;
 10  end loop;
 11  close a;
 12 end;
 13 /
ANTHONY
BUSH
.
.
TRUMAN
WILSON

PL/SQL procedure successfully completed.

Page 195

TIP
When using a %found cursor attribute with a While loop, the first Fetch command is placed outside the loop usually preceding the first line. If a succesful fetch occurs, the while loop can be performed. If a successful fetch does not occur, the %found attribute contained in the while evaluation will prevent the loop from being performed. A second fetch is placed before the end of the loop. This retrieves a fresh record to be processed as the loop is repeated. If the fetch is unsuccessful, the loop will be terminated as it returns to the while evaluation.
When using the %notfound cursor attribute, there is only one fetch and it is usually the first line of the loop statements. This allows the application to enter the loop. The %notfound attribute is true since a record was not successfully fetched. The fetch command is placed at the top of the loop in order to retrieve a fresh record for the statements.

While loops can contain nested While loops. This technique is often used when converting data. In a recent re-engineering project of an existing Substation Transformer system, for example. The existing system used the transformer serial number for the primary and foreign keys. It was determined that the serial number is not always unique because several manufacturers use the same numbering. In addition, the serial number can change when the transformer is remanufactured. This is not a good condition for relational databases.

It was decided that each transformer would have an artificial (or unseen) key called rec_id that would never change or be duplicated. The old system had a related table of tests that used the serial number for its foreign key. The nested While loop procedure was used to generate a unique rec_id value for each transformer and test record. In addition, the program populates each test record with the foreign key rec_id of its parent transformer. This maintains the referential integrity of the tables.

Figure 9.1 shows a representation of the conversion program used to create the new primary keys and to load new transformer and transformer tests tables. The block contains two cursors and two While loops. The outer loop uses the trf_main cursor to retrieve each of the transformers. On each iteration of this loop, a new record number is computed and assigned to the transformer during the insert into the new transformer table.

Inside the outer loop is an inner While loop. For each of the transformer records fetched by the trf_main cursor, the inner loop fetches the test records for the transformer from the old table. Each of these test records is assigned the transformer's rec_id as a foreign key value. Each test record is also assigned a sequential artificial key. The modified test record is then inserted into the new test table. The inner loop is completed when the last test record for the transformer is inserted into the table. The cursor is closed and the statements in the outer While loop are executed.

This procedure continues until the last transformer record is inserted into the table. The outer While loop is ended, the associated cursor is closed, and the program is terminated. The program results in two transformer tables, each table having a unique primary key , and the tests table having a foreign key relating the record to the new primary key in the transformer table.

Previous | Table of Contents | Next