Page 173
FIG. 8.3
Basic if-then-else
statement.
Listing 8.3 L_08-03.txtUsing an If-Then-Else Statement in a PL/SQL Block
SQL> set serveroutput on; SQL> declare 2 month char(3); 3 begin 4 month := upper(substr(to_char(sysdate), 4, 3)); 5 dbms_output.put_line (sysdate); 6 if (month = `JAN' or month = `FEB' or month = `MAR' 7 or month = `APR' or month = `MAY' or month = `JUN') then 8 dbms_output.put_line (`I say hello. Hello !'); 9 else 10 dbms_output.put_line (`I say goodbye. Goodbye !'); 11 end if; 12 end; 13 / 23-APR-97 I say hello. Hello ! PL/SQL procedure successfully completed.
The initial evaluation in the construct compares the value of "month" with values that represent the first six months of the year. The evaluation clause uses the or predicate to link the various condition expressions. If one of the expressions is true, the commands following the then are executed. If none of the expressions are true, the commands following the else are executed.
Page 174
The script was executed in April, therefore the expressions evaluated as true, as the commands following the then were executed.
Several rules apply to the if-then-else construct.
If-then-else constructs can contain nested If-Then-Else constructs , the then or else sections can contain one or more If-then-else constructs. These If statements can also contain nested If statements. This capability to nest If-then-else constructs enables developers to outline complex algorithms for their programs. This is a very important tool for use in your data conversion and Form programs.
Listing 8.4 contains an If-then-else construct with a nested If-then-else constructs. The PL/SQL block determines whether the date is in February and what the date of the last day of the month is. The program has `01-FEB-97' assigned to the sample_date variable. The first line of the program extracts the month from the original date. The first If statement determines whether the month is February. If it is, the year is divided by four. The MOD function calculates the remainder. If there is no remainder, the last day of the month is the "29th." The date in the PL/SQL program is not in a leap year. The program prints "The Last Day of the Month is the 28th."
Listing 8.4 L_08_04.sqlUsing Nested If Statements in a PL/SQL Block
SQL> declare 2 sample_date char(9) := '01-FEB-97'; 3 remainder number; 4 month char(3); 5 last_day char(2); 6 begin 7 month := substr(sample_date, 4, 3); 8 if (month = `FEB') then 9 remainder := mod(to_number(substr(sample_date, 8,2)),4); 10 if (remainder = 0) then 11 last_day := `29'; 12 dbms_output.put_line (`This is a Leap Year'); 13 dbms_output.put_line (`The Last Day of the Month Is The `||last_day||'th'); 14 else 15 last_day := `28'; 16 dbms_output.put_line (`This is not a Leap Year'); 17 dbms_output.put_line (`The Last Day of the Month Is The `||last_day||'th');
Page 175
18 end if; 19 else 20 dbms_output.put_line (`The Month is Not February'); 21 end if; 22 end; 23 / This is not a Leap Year The Last Day of the Month Is The 28th PL/SQL procedure successfully completed.
This program has a number of conditions to evaluate. The first evaluation is whether the month is February. When it is, it needs to determine whether the year is a leap year or not. This is the nested if-then-else construct. Without the ability to nest the constructs, you could not document complex logic needed for your programs.
Instead of using a nested if-then-else construct in the else section, you can use the elseif word. This word sets up a structure similar to an if-then-else construct except that it does not have its own else if. Because it is in the else portion of the structure, it uses the end if of the structure to denote its completion. You can use this construct as many times as needed in the section. Listing 8.5 illustrates the use of the elseif construct. The intent of the PL/SQL block is to determine the current month of the year and output a holiday that occurs during that month. The if-then-else construct has a series of conditions used to determine the month of the year. The first condition following the If checks to see whether the month is January. The remaining conditions follow each elseif and check for the months from February to June. Each of the conditions is mutually exclusive. If any of the conditions are met, the commands following are performed and the program exits the structure.
Listing 8.5 L_08_05.sqkUsing the Elseif Construct to Display the Correct Monthly Events
SQL> set serveroutput on; SQL> declare 2 month char(3); 3 begin 4 month := substr(to_char(sysdate, `DD-MON-YY'), 4,3); 5 if (month = `JAN') then 6 dbms_output.put_line (`January has Martin Luther King's Birthday'); 7 elsif (month = `FEB') then 8 dbms_output.put_line (`February has Valentines Day'); 9 elsif (month = `MAR') then 10 dbms_output.put_line (`March has Spring Training'); 11 elsif (month = `APR') then 12 dbms_output.put_line (`April has My Birthday'); 13 elsif (month = `MAY') then 14 dbms_output.put_line (`May has Mothers Day'); 15 elsif (month = `JUN') then
Page 176
Listing 8.5 Continued
16 dbms_output.put_line (`June has My Wedding Anniversary'); 17 else 18 dbms_output.put_line (`I Ran Out Of Room In The Example'); 19 end if; 20 end; 21 / April has My Birthday PL/SQL procedure successfully completed.
Following the final elsif in Listing 8.5 is an
else section. It is used as a catch-all for any instances that do not match the conditions in the
If statements. In the case of the preceding example, the months July to December do not match any the conditions contained in the
if-elseif statements. Thus, if the current month is between July and December, none of
the commands that followed these statements will be executed. The command following the
else keyword is executed.
TIP |
The Elseif structure is a good tool to use when a large number of options need to be evaluated in the if-then-else structure. It ensures that each path through the structure is mutually exclusive of the others. This is not true with a series of nested If statements. |
PL/SQL has several structures that allow your programs to execute the same procedure repeatedly. These structures are very powerful devices that enable you to process multiple records using the same set of PL/SQL statements The first structure considered here is called a loop.
The Loop structure has three parts: the Loop command, an Exit command, and an End loop command. The following bulleted list illustrates these commands. The keyword loop denotes the beginning of the structure. It tells Oracle to execute the commands within the structure until the Exit command is encountered. The End loop command tells Oracle that it has reached the end of the Loop structure. Oracle then begins to re-execute the commands from the beginning of the structure, continuing until the Exit command is executed. The components of a loop structure are: