Previous | Table of Contents | Next

Page 177

Listing 8.5 is a PL/SQL program that outputs each number from 1 to 50. To do this, the loop must be executed 50 times. The program has a counter variable that increases by a value of one with each iteration of the loop. An If statement is contained in the program. When the value of the counter reaches 50, the Exit command is issued and the loop terminates.

Listing 8.6 L_08_06.sql—Using a Loop Structure to Output a Series of Numbers

SQL> set serveroutput on;
SQL> declare
 2  counter  number := 1;
 3 begin
 4  dbms_output.put_line (`This is the beginning of the series of numbers');
 5  loop
 6   dbms_output.put_line (counter);
 7   counter := counter + 1;
 8   if (counter > 50) then
 9     exit;
 10   end if;
 11  end loop;
 12  dbms_output.put_line (`This is the end of the numbers');
 13 end;
 14 /
This is the beginning of the series of numbers
1
2
.
.
49
50
This is the end of the numbers
PL/SQL procedure successfully completed.

You can also terminate the loop by using a When operator rather than an If statement. It has a slightly different syntax. The Exit command is followed by the word when and an evaluation expression. You can use the When operator when you are testing for conditions not related to counters. An example of this type of condition is no_data_found. This condition can exist when the Select command was issued and no records were found. In the next chapter, you are introduced to a number of conditions that can be used as evaluation conditions to terminate the loop. Listing 8.6 illustrates the use of the When operator. It replaces the If statement from the program executed in Listing 8.5.

Listing 8.7 L_08_07.sql—Using the When Operator to Terminate the Loop

SQL> set serveroutput on;
SQL> declare
 2  counter  number := 1;
 3 begin
 4  dbms_output.put_line (`This is the beginning of the series of numbers');
                                                                         continues

Page 178

Listing 8.7 Continued

 5  loop
 6   dbms_output.put_line (counter);
 7   counter := counter + 1;
 8   exit when counter > 50;
 9  end loop;
 10  dbms_output.put_line (`This is the end of the numbers');
 11 end;
 12 /
This is the beginning of the series of numbers
1
2
.
.
48
49
50
This is the end of the numbers

PL/SQL procedure successfully completed.

You can use two other types of repeating structures in PL/SQL. They are the while loop and the for loop. The while loop does not need an Exit command. The for loop performs specialized functions by using cursors. The next chapter covers these two structures in more detail.

CAUTION
When using any loop statement, be certain to check whether a condition always exists that terminates the loop. I have written many programs where I didn't have a condition to terminate a loop. This causes a condition called an infinite loop. When this happens, I spend a great deal of time watching the cursor on my machine blink.
To break an infinite loop, the control+c keys may be pressed. This will generally break the processing. It is an abnormal break, so none of the changes that may have been performed will be saved. Oracle will rollback the changes. If there are not pending changes, Oracle will respond immediately to the sequence. If a large number of changes are pending, Oracle must rollback the changes before responding. This may take a period of time.

Understanding PL/SQL Cursors

A database cursor is a device in PL/SQL that acquires a set of records and places them in memory for individual processing. It is not the same thing as a screen cursor which designates a position on a screen Cursors can be compared to a bookmark placed inside the pages of the book. Each page is a record in the set or records acquired by the database manager per the cursor's instructions. When the book is first opened, you are on the first page or record. The bookmark is also on the first page. As you read one page after the other, you move the

Page 179

bookmark to identify the current page. A cursor operates in much the same way. When the cursor is initiated with an Open cursor command, Oracle places the set of records in memory and places the cursor on the first record. As you retrieve records to your program by using the Fetch command, the cursor moves to the next record. This is the same as you did with your bookmark when you moved to the next page. When you are done looking at the records, you close cursor and remove the records by using the Close command.

Controlling Your Cursors with Cursor Commands

Several commands and steps are used in relation to cursors. Figure 8.4 illustrates these commands. The first task is to define the cursor. This occurs in the Declare section of the PL/SQL block. The definition consists of the words "cursor cursor_name is" followed by a Select statement. The Select statement is used to retrieve the desired records and values for the cursor. A semicolon completes this cursor definition.

FIG. 8.4
Cursor definition and
cursor commands.


Cursors retrieve values from the database. They need local variables to place the retrieved values into. As you have seen in the section of this chapter about defining variables, these variables are defined in the Declare section. There must be one local variable for each column named in the cursor. The variables must be the same data type and must be at least as large as the column.

The cursor is activated when the "open cursor name" command is issued in the Executable portion of the PL/SQL block. Issuing the command causes the database manager to retrieve the records identified in the Select statement and to place the records in memory. A semicolon terminates the command.

Page 180

The Fetch command assigns the values of the current cursor record to the local variables. The syntax of this statement is the keyword fetch followed by the name of the cursor. This is followed by the local variables that will contain the values from the cursor columns. The value for the first column in the Select statement will go in the first variable listed in the Fetch command. The command must contain the same number of variables as the select statement. A semicolon terminates this command. The cursor is ended with the "close cursor name" command. This command frees memory of the records the Select command placed in it. A semicolon terminates this command also.

Listing 8.8 is a PL/SQL block that examines the birthdays of the employees and determines the most common day of the week for birthdays. The program employs a number of the features discussed thus far in this chapter. The Declare block defines a number of variables and initializes them during the declaration. The bday variable is used to hold the birthday values returned by the cursor "birthday". It is defined with the %type declaration to ensure it has the same data type as the cursor column.

Listing 8.8 L_08_08.TXT—Executing a Cursor in a Loop to Determine the Most Common Day of the Week that Our Employees Were Born

SQL> set serveroutput on;
SQL> declare
  2    bday       date;  day_of_week  varchar2(10); sunday     number := 0;
  3    monday     number := 0; tuesday    number := 0; wednesday  number := 0;
  4    thursday   number := 0; friday     number := 0; saturday   number := 0;
  5    cursor birth_days is select birth_date from employee;
  6  begin
  7    open birth_days;
  8    loop
  9      fetch birth_days into bday;
 10      if (birth_days%notfound) then
 11            exit;
 12      end if;
 13      day_of_week := rtrim(to_char(bday, `DAY'), ` `);
 14      if (day_of_week = `SUNDAY') then
 15        sunday := sunday + 1;
 16        elsif (day_of_week = `MONDAY') then
 17          monday := monday + 1;
 18        elsif (day_of_week = `TUESDAY') then
 19          tuesday := tuesday + 1;
 20        elsif (day_of_week = `WEDNESDAY') then
 21          wednesday := wednesday + 1;
 22        elsif (day_of_week = `THURSDAY') then
 23          thursday := thursday + 1;
 24        elsif (day_of_week = `FRIDAY') then
 25          friday := friday + 1;
 26        elsif (day_of_week = `SATURDAY') then
 27          saturday := saturday + 1;
 28      end if;
 29     end loop;
 30     close birth_days;

Previous | Table of Contents | Next