Listing 5.8. continued

    v_OverTime := v_HoursWorked - 40;
       DBMS_OUTPUT.PUT_LINE(`Hours overtime worked = ` || v_OverTime);
  END IF;
END;
/

By previously typing SETSERVEROUTPUT ON, you are telling Oracle to run the DBMS_OUTPUT package, which aids in the debugging process by allowing you to display output to the screen. This, combined with the DBMS_OUTPUT.PUT_LINE statement, shows you the calculated values of the variable v_OverTime.

OUTPUT
Your output should be

Hours overtime worked = 10

You have set up two variables—V_HoursWorked and v_OverTime—to store the actual number of hours worked and the calculated number of overtime hours. These two variables are defined as a NUMBER to allow calculations on these values:

v_HoursWorked NUMBER := 50 ; --Number of hours worked by hourly employee
v_OverTime NUMBER := 0 ; --Storage of Overtime Hours

PL/SQL then starts evaluating the IF statement. Because you initialized v_HoursWorked to a value of 50 and 50 is greater than 40, and the IF statement is evaluated to true. Because the condition is true, you will process all statements under the THEN keyword.

IF v_HoursWorked > 40 THEN

You can now calculate the hours of overtime by simply subtracting 40 from v_HoursWorked. You can then display the output to the screen using the DBMS_OUTPUT.PUT_LINE command.

v_OverTime := v_HoursWorked - 40;
       DBMS_OUTPUT.PUT_LINE(`Hours overtime worked = ` || v_OverTime);

Finally, the ending / tells Oracle to execute the statements entered.

The IF...THEN...ELSE Statement

In the preceding example, you did not care what the results were if the hours were under 40 because you were only trying to determine the total hours of overtime worked. However, what if you did not initialize v_OverTime to a value of zero? The IF...THEN...ELSE statement allows you to process a series of statements under ELSE if the condition is false.

The Syntax for the IF...THEN...ELSE Statement

IF <some_condition_evaluates_to_true>
THEN
<perform_statements_condition_true>
     ELSE
<perform_statements_condition_false>
END IF;

In this syntax, the first parameter, some_condition_evaluates_to_true, tests a BOOLEAN condition that you provide. If the condition is true, the second parameter, perform_statements_condition_true, executes. If the condition is false, the parameter perform_statements_condition_false executes.

You can now alter your original IF statement to reflect what to do if the condition is false (see Listing 5.9).

INPUT
Listing 5.9. Adding ELSE to the IF block.

set echo on
DECLARE
  v_HoursWorked Number := 50 ; --Number of hours worked by hourly employee
  v_OverTime Number ; --Storage of Overtime Hours
BEGIN
  IF v_HoursWorked > 40 THEN
    v_OverTime := v_HoursWorked - 40;
       DBMS_OUTPUT.PUT_LINE(`Hours overtime worked = ` || v_OverTime);
     ELSE
          v_OverTime := 0;
  END IF;
END;
/

Again, the only change was to remove the initialization of variables in the declaration area and to set the value of v_OverTime to zero if there was no overtime under the ELSE statement.

Nested IF Statements

By nesting IF statements, you can check for many complex conditions before executing a series of statements. This allows you to defer executing inner IF statements unless the outer IF conditions apply.

The Syntax for Nested IF Statements

IF <condition1 evaluates to true>
THEN
     IF <condition2 evaluates to true>
     THEN
          <perform statements>
     ELSE <both conditions have been evaluated to false>
          IF <condition3 evaluates to true>
          THEN
               <perform statements>
          ELSE
               <perform statements>
          END IF;
     END IF;
END IF;

Did you notice that even on the ELSE statements you can keep on adding IF statements? As you can see, it is possible to continue to nest loops for as long as you need, but nesting too much will most likely cause you grief in the debugging stage. Imagine just four levels of IF statements and trying to go through all the possible conditions to make sure that you produce the desired outcome.

In the overtime example, you will first determine how many people worked over 40 hours. If the employee is hourly, overtime hours are calculated. If the employee paytype is set to `S', then the employee is salaried with no overtime. If the employee paytype is set to `E', then the employee is executive management who gets no overtime (but lots of options!). See the code in Listing 5.10.

INPUT
Listing 5.10. Using nested IF statements.

DECLARE
  v_HoursWorked Number := 80 ; --Number of hours worked by hourly employee
  v_OverTime Number := 0 ; --Storage of Overtime Hours
  v_PayType char(1) := `E'; --Classification of employee, E, S, or H

BEGIN
IF v_HoursWorked > 40 THEN
     IF v_PayType = `H' THEN
          v_OverTime := v_HoursWorked - 40;
          DBMS_OUTPUT.PUT_LINE(`Hours overtime worked = ` || v_OverTime);
     ELSE
          IF v_PayType = `S' THEN
               DBMS_OUTPUT.PUT_LINE(`Employee is Salaried');
          ELSE
               DBMS_OUTPUT.PUT_LINE(`Employee is Executive Management');
          END IF;
     END IF;
END IF;
END;
/

In Listing 5.10, the first IF statement evaluates to true. Notice that because most employees do not work over 40 hours, you would have to avoid all the inner logic! The second statement evaluates to false because the paytype is set to `E' for executive management. Control passes to the ELSE statement where another IF statement is evaluated. Again, the condition is evaluated to false because the value of paytype is set to `E'. The final ELSE statement then defaults to executive management as there are only three types of workers.

In addition, this example of nested IF statements is a poor programming choice. What happens if another classification of paytype is added? This would be better suited to reading data from a form or creating a user-defined function to handle these situations.