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