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.sqlUsing 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.sqlUsing 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. |
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.
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.TXTExecuting 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;