O'Reilly logo

Advanced Oracle PL/SQL Programming with Packages by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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'); ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required