Advanced Oracle PL/SQL Programming with Packages

A.1. Exercises

The exercises are arranged by topic:

Conditional logic

Loops

Exception handling

Cursors

Builtin functions

Builtin packages

Modules

Module evaluation

A.1.1. Conditional Logic

1. Rewrite the following IF statements so that you do not use an IF statement to set the value of no_revenue. What is the difference between these two statements? How does that difference affect your answer?

   IF total_sales <= 0
   THEN
      no_revenue := TRUE;
   ELSE
      no_revenue := FALSE;
   END IF;
   
   IF total_sales <= 0
   THEN
      no_revenue := TRUE;
   ELSIF total_sales > 0
   THEN
      no_revenue := FALSE;
   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 three minutes to return its value, while the overdue_balance Boolean function returns TRUE/FALSE in less than a second.

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

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

   IF salary < 10000 
   THEN 
      bonus := 2000;
   ELSE
      IF salary < 20000 
      THEN 
         bonus := 1500;
      ELSE
         IF salary < 40000 
         THEN 
            bonus := 1000;
         ELSE
            bonus := 500;
         END IF;
      END IF;
   END IF;

4. Which procedure will never be executed in this IF statement?

   IF (order_date > SYSDATE) AND order_total >= min_order_total THEN fill_order (order_id, 'HIGH PRIORITY'); ELSIF (order_date < SYSDATE) OR (order_date = SYSDATE) THEN fill_order (order_id, 'LOW PRIORITY'); ...