Advanced Oracle PL/SQL Programming with Packages

A.2. Solutions

This section contains the answers to the exercises shown earlier in this appendix.

A.2.1. Conditional Logic

1. Rewrite the following IF statements so that you do not use the IF statement to set the value of no_revenue. What is the difference between the two statements?

   The first IF statement can be simplified to:

   no_revenue := NVL (total_sales, 1) <= 0;

   I use NVL to make sure that no_revenue is set to FALSE, as would happen in the original IF statement. Without using NVL, I will set no_revenue to NULL if total_sales is NULL.

   The second statement is a bit more complicated, again due to the complexities of handling NULL values. If total_sales is NULL, the IF statement does not assign a value to no_revenue at all. NULL is never less than or equal to any number. So I still need an IF statement, but not (strictly speaking!) to assign a value to no_revenue:

   IF total_sales IS NOT NULL
   THEN
      no_revenue := total_sales <= 0;
   END IF;

2. Rewrite the following IF statement to work as efficiently as possible under all conditions, given the following information: the calc_totals numeric function takes 3 minutes to return its value, while the overdue_balance Boolean function returns TRUE/FALSE in less than a second.

   IF NOT overdue_balance (company_id_in => 1005)
   THEN
      IF calc_totals (1994, company_id_in => 1005)
      THEN
         display_sales_figures (1005);
      ELSE
         contact_vendor
      END IF;
   ELSE
      contact_vendor;
   END IF;

3. Rewrite the following IF statement to get rid of unnecessary nested IFs:

   IF salary < 10000 ...