Previous | Table of Contents | Next

Page 173

FIG. 8.3
Basic if-then-else
statement.




Listing 8.3 L_08-03.txt—Using 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.

Using Nested If Statements

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.sql—Using 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.

Using Elseif in Your If Construct

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.sqk—Using 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.

Using Loops and Exits

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:

Previous | Table of Contents | Next