Skip to Main Content
Advanced Oracle PL/SQL Programming with Packages
book

Advanced Oracle PL/SQL Programming with Packages

by Steven Feuerstein
October 1996
Intermediate to advanced content levelIntermediate to advanced
687 pages
16h 41m
English
O'Reilly Media, Inc.
Content preview from 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'); ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin

Publisher Resources

ISBN: 1565922387Supplemental ContentCatalog PageErrata