Previous | Table of Contents | Next

Page 196

FIG. 9.1
Using nested While
loops in a conversion
routine to create two
tables.




Using the For Loop

The numeric For loop is used to process records a predetermined number of times. This loop has a variable that counts each iteration of the loop and compares it to two variables that represent the high and low iteration ranges. A second type of For loop is called a Cursor For loop. This cursor is associated with an explicit cursor. It is an extremely powerful loop because of the integration of cursor commands into the procedure.

The following sections describe how to create and use the numeric For and the Cursor For loops.

Creating and Using the Numeric For Loop

This type of cursor executes the loop a predetermined number of times. The cursor has two settings, an upper number and lower number. It also has a count variable that contains the number of iterations for the current loop. As long as this variable is between the high and low settings, the loop continues. When the value falls outside the range of these values, the loop terminates. Figure 9.2 illustrates the components and layout of the loop.

The first line contains the parameters of the loop. The line begins with the keyword For, followed by the name of the counting variable, the keyword In, the lowest iteration number, and the highest iteration number. The counting variable is initially assigned the value of the lowest iteration number. This increases by one with each iteration. Using the reverse option causes the counting variable to initially be assigned the highest iteration number. This number decreases by one with each iteration. The high and low iteration number values can be assigned during

Page 197

the execution of the program. You can list variables in the declaration line. This enables the developer the opportunity to assign values on-the-fly. The parameter line is followed by the keyword loop, the loop executable statements, and the end loop keyword.

FIG. 9.2
Components and layout
of the Numeric For
loop.



Listing 9.5 contains a simple block that has a Numeric For loop. The statements in the loop cause the output of the last name of the employee. The counting variable (cnt_var) has a value of 1 on the first iteration. It increments by one on each pass through the loop. Because the high increment parameter is set to 5, there are five passes through the loop.

Listing 9.5 L_09_05.sql—Defining a Simple Numeric For Loop

SQL> set serveroutput on;
SQL> declare
 2  e_rec  employee%rowtype;
 3  cursor a is select * from employee;
 4 begin
 5  open a;
 6  for cnt_var in 1..5
 7   loop
 8    fetch a into e_rec;
 9    dbms_output.put_line (e_rec.last_name);
 10   end loop;
 11  close a;
 12 end;
 13 /
COOLIDGE

Page 198

Listing 9.5 Continued

JOHNSON
REAGAN
BUSH
JOHNSON
PL/SQL procedure successfully completed.

TAFT
ROOSEVELT
ANTHONY
ROOSEVELT
COOLIDGE

The following rules apply to these type of loops:

The Numeric For loop works well when you know the number of iterations in advance. When you don't know the number, you need to perform extra statements such as a Count(*), which was covered in Chapter 3, "Acquiring Data by Using the SELECT Statement," using the cursor select conditions to calculate this number in advance. If your high iteration parameter is greater than the number of records retrieved by the cursor, the cursor will not terminate after the last record has been reached. The last fetched record will display repeatedly until the high parameter is met. This problem does not exist with the Simple loop or While loop because you have the cursor attributes to control the exit of the loop.

The cursor for loop enables you to use the for loop structure without having to know the iterations in advance. The next section discusses this loop.

Creating and Using the Cursor For Loop

The Cursor For loop is a sophisticated looping procedure that integrates many of the cursor commands into the looping procedure. This loop does not have lowest or highest iteration parameters. It just evaluates or processes each of the records in a cursor until the last record in the cursor has been fetched. Although you can use the Numeric For loop for procedures that do not involve cursors, you can only use this loop with cursors. Figure 9.3 illustrates the basic structure of the loop.

The first line in this example contains the loop's parameters. It begins with the keyword For followed by the count variable, the keyword In, and the name of the cursor. The loop executable statements begin with the keyword loop and are completed by the keywords end loop. You

Page 199

can open the cursor before the first line of the cursor for structure or between the For declaration line and the keyword Loop.

FIG. 9.3
The basic structure of
the Cursor For loop.



Listing 9.6 illustrates a simple cursor for loop. The procedure displays the last name of the employees.

Listing 9.6 L_09_06.sql—Executing a Cursor For Loop to List the Last Names of the Employees

SQL> set serveroutput on;
SQL> declare
 2  e_rec   employee%rowtype;
 3  cursor a is select * from employee;
 4 begin
 5  for cnt_var in a
 6   loop
 7    fetch a into e_rec;
 8    dbms_output.put_line (cnt_var.last_name);
 9   end loop;
 10 end;
 11 /
COOLIDGE
REAGAN
.
.
                                             continues

Page 200

Listing 9.6 Continued

ROOSEVELT
ROOSEVELT

PL/SQL procedure successfully completed.

TAFT
ANTHONY
COOLIDGE
REAGAN
JOHNSON
CARTER
NIXON
EISENHOWER
ROOSEVELT
WILSON

The Cursor For loop enables you to omit the cursor commands and attributes that you used with other looping procedures. The first statement that you can eliminate is the e_rec local variable array. The cursor for loop automatically sets up local variables for each of the items in the cursor. Because the Cursor For loop is used for cursors only and is terminated when the %notfound attribute is true, the structure does not need the Open, Fetch, and Close Cursor commands. Listing 9.7 illustrates this concept.

Listing 9.7 L_09_07.sql—Using the Cursor For Loop Without Declaring Local Variables

SQL> set serveroutput on;
SQL> declare
 2   cursor a is select * from employee;
 3  begin
 4   for cnt_var in a
 5   loop
 6    dbms_output.put_line (cnt_var.last_name);
 7   end loop;
 8 end;
 9 /
COOLIDGE
JOHNSON
.
.
ANTHONY
ROOSEVELT

PL/SQL procedure successfully completed.

f

TAFT
ROOSEVELT
ANTHONY
ROOSEVELT

Previous | Table of Contents | Next