Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Write Complex Boolean Logic in PL/SQL

Your task is to replicate the following process on your sample database. The script will be called royalty_plsql.sql in your c:\Burleson directory.  The management for Bookhouse publishing wants you to create a small decision support program to forecast the additional amount that will be paid to authors if they increase their royalty rates.

They want to simulate the following scenario, with a grand total of the savings (or losses) from the proposed change.

* Increase the royalties by 5% for all computer books with gross sales of less than $250,000.

* Decrease the royalty rate by 5% for all books with gross sales of greater than $500,000.

* Increase the royalty rate by 3% for all miscellaneous books with gross sales of less than $250,000.

Wow!  On its face, this looks like an extremely complex problem.  So, how do we eat this Elephant?  One bite at a time!  Let’s start by analyzing the data requirements:

Step 1 – Analyze the required data. We start by analyzing the pubs schema to determine where the required data resides.

To answer their simulation, Bookhouse requires the following data:

* The book title

* The author last name

* The gross dollar sales amount for each book

* The total dollar royalty paid to each author – Note that the royalty for each author is located in the book_author table.

You analyzed your pubs schema and identified that you need a five-way table join, and highlighted the required data columns:

m6publisherdb

Step 2 – Formulate the basic query to gather the title, type and author name for all Bookhouse authors:

select
   book_title,
   book_type,
   author_last_name
from
   publisher
natural join
   book
natural join
   book_author
natural join
   author
where
   pub_name = 'bookhouse'
;

The initial SQL looks like this:

BOOK_TITLE                                                                     
------------------------------------------------------------
BOOK_TYPE                      AUTHOR_LAST_NAME                                
------------------------------ -----------------------------
windows sucks                                                                  
computer                       Burleson                                           
                                                                                
windows sucks                                                                  
computer                       hester   
                                      
                                                                                
piano greats                                                                   
music                          weaton                                          
                                                                                
piano greats                                                                   
music                          jeckle                                          
                                                                                
DOS for dummies                                                                
computer                       withers                                         
                                                                               
writers market                                                                 
miscellaneous                  shagger                                         

Step 3 – Add the gross sales per book by joining into the sales table and adding the appropriate GROUP BY clause:

BOOK_TITLE                                                                     
------------------------------------------------------------------------
BOOK_TYPE                      SUM(QUANTITY)*BOOK_RETAIL_PRICE                 
------------------------------ -------------------------------                 
DOS for dummies                                                                
computer                                                231021                 
                                                                                
piano greats                                                                   
music                                                 536755.5                 
                                                                                
windows sucks                                                                  
computer                                                636090                 
                                                                                
writers market                                                                 
miscellaneous                                           192780                 

Step 4 – Add the percentage for royalties and the total amount paid in royalties.  This will be the query that you place inside your PL/SQL snippet.

BOOK_TITLE
------------------------------------------------------------------------
BOOK_TYPE                      AUTHOR_LAST_NAME                                
------------------------------ ----------------------------------------        
SUM(QUANTITY)*BOOK_RETAIL_PRICE SUM(QUANTITY)*BOOK_RETAIL_PRICE*ROYALTY        
------------------------------- ---------------------------------------        
DOS for dummies                                                                
computer                       withers                                         
                               231021                           23102.1      
                                                                               
piano greats
music                          jeckle                                  
                               536755.5                       80513.325

piano greats
music                          weaton                                   
                               536755.5                        64410.66        

windows sucks                                                                  
computer                       hester                                          
                               636090                           50887.2        

windows sucks                                                                  
computer                       Burleson
                               636090                           69969.9          
                                                                    
writers market
miscellaneous                  shagger
             
                        192780                           21205.8        

Step 5 – Add the SQL to a PL/SQL snippet.  This involves declaring a cursor, opening the cursor, and fetching the cursor rows inside a loop.

a. Step 5a - Declare a cursor to traverse the data

set serveroutput on

DECLARE
   cursor c1 is
   select
      book_title . . .
   rc  c1%rowtype;

b. Step 5b - Open the cursor and use dbms_output.put_line to display the data:

BEGIN
   open c1;
   LOOP
      fetch c1 into rc;

      exit when c1%NOTFOUND;

      dbms_output.put_line(
         rc.book_title      ||' '||
      . . . .
      );
   END LOOP;
END;
/

The output from your PL/SQL should now look like this:

SQL> @royalty_plsql.sql

DOS for dummies computer withers 231021 23102.1                                
piano greats music jeckle 536755.5 80513.325                                   
piano greats music weaton 536755.5 64410.66                                     
windows sucks computer hester 636090 50887.2                                   
windows sucks computer Burleson 636090 69969.9                                    
writers market miscellaneous shagger 192780 21205.8                            

PL/SQL procedure successfully completed.

Step 6 – Add the Boolean logic.  This step involves declaring variables to hold the new royalty computations and the addition of the appropriate IF-THEN-ELSE logic to properly display the data.

Let’s review the logic rules:

* Increase the royalties by 5% for all computer books with gross sales of less than $250,000.

* Decrease the royalty rate by 5% for all books with gross sales of greater than $500,000.

* Increase the royalty rate by 3% for all miscellaneous books with gross sales of less than $250,000.

So, we need to start by defining a variable called v_new_royalty and analyze a single IF-THEN-ELSE structure to recompute the royalties into the v_new_royalty variable .  Here is a suggestion:

if rc.gross_sales > 500000
then
    v_new_royalty := rc.gross_royalty*.95;
   elsif
     (rc.book_type = 'computer' and rc.gross_sales < 250000)
   then
        . . .
      elsif
        . . . .
   end if;

Your task is to create an IF-THEN-ELSE structure that is the most easy to read.

When you have completed the addition of your Boolean logic, your output should look like this:

DOS for dummies computer withers 231021 25412.31                               
piano greats music jeckle 536755.5 76487.66                                     
piano greats music weaton 536755.5 61190.13                                    
windows sucks computer hester 636090 48342.84                                  
windows sucks computer Burleson 636090 66471.41                                    
writers market miscellaneous shagger 192780 20145.51                           

PL/SQL procedure successfully completed.

ANSWER:

set serveroutput on
DECLARE
   cursor c1 is
   select
      book_title,
      book_type,
      author_last_name,
      sum(quantity)*book_retail_price         gross_sales,
      sum(quantity)*book_retail_price*royalty gross_royalty
   from
      publisher
   natural join
      book
   natural join
      sales
   natural join
      book_author
   natural join
      author
   where
      pub_name = 'bookhouse'
   group by
      book_title,
      book_type,
      author_last_name,
      book_retail_price,
      royalty;
   rc  c1%rowtype;
   v_new_royalty number(8,2);
BEGIN
   open c1;
   LOOP
      fetch c1 into rc;

      exit when c1%NOTFOUND;

      if rc.gross_sales > 500000
      then
         v_new_royalty := rc.gross_royalty*.95;
         elsif
            (rc.book_type = 'computer' and rc.gross_sales < 250000)
         then
             v_new_royalty := rc.gross_royalty*1.1;
            elsif
               (rc.book_type = 'miscellaneous' and rc.gross_sales < 250000)
            then
                v_new_royalty := rc.gross_royalty*.95;
               else
                  v_new_royalty := rc.gross_royalty;
   end if;

      dbms_output.put_line(
         rc.book_title      ||' '||
         rc.book_type       ||' '||
         rc.author_last_name||' '||
         rc.gross_sales     ||' '||
         v_new_royalty
      );
   END LOOP;
END;
/

The main points of this tutorial include:

* PL/SQL has a built-in interface to Oracle, so that you can directly embed SQL inside PL/SQL.

* Oracle PL/SQL supports all standard language constructs, including variable definition. Looping, Boolean logic and internal RAM array support.

* PL/SQL has evolved over the past 12 years into a very powerful database language, and PL/SQL can perform as fast as C and C++ for database program execution.


For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.