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.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 ...

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