|
|
|
Oracle:
Create a custom SQL BIF
After reading all of the reading assignments
for this tutorial, create a custom BIF to
convert Fahrenheit temperatures to
centigrade.
Your task is to create a custom SQL built-in
function in PL/SQL function named plus_tax
that accepts a book price and returns the
price plus 7% tax.
Here is the
code to create a custom SQL BIF. Note that
the DETERMINISTIC clause is required for an
SQL BIF. This is because you must tell the
database that the function will always
return the same output value when given the
same input value.
CREATE OR REPLACE FUNCTION
plus_tax(p_book_retail_price number)
RETURN NUMBER DETERMINISTIC
AS
price_plus_tax NUMBER(5,2);
BEGIN
-- Tax is set at 7%
price_plus_tax := p_book_retail_price
+ p_book_retail_price*.07;
return price_plus_tax;
END;
/
Your tasks
are as follows:
Step 1
– Copy the above function and create the
function in your Oracle database. Test your
call to your new BIF by using this SQL:
select
plus_tax(book_retail_price)
from
book
;
Step 2
– Reproduce the following output using your
new plus_tax BIF, and e-mail the script to
your instructor.
Tue May
28
page 1
Book List
Alphabetical
with 7% sales tax
Price
Book
Retail Plus 7%
Title
Price
Tax
----------------------------------------
-------- --------
Dos For
Dummies
$19.95 $21.35
The Zen Of Auto
Repair $99.95
$106.95
Unix For
Experts
$38.95 $41.68
Bears Are People
Too $34.95
$37.40
Cooking Light
$24.95
$26.70
How To Housebreak Your
Horse $29.95
$32.05
Managing
Stress
$39.95 $42.75
Never Eat
Boogers
$10.95 $11.72
Non Violins In The
Workplace $11.95
$12.79
Operations Research
Theory $44.95
$48.10
Oracle9i Sql
Tuning $49.95
$53.45
Pay No Taxes And Go To
Jail $10.95
$11.72
Piano
Greats
$32.95 $35.26
Reduce Spending The Republican
Way $27.95
$29.91
The Fall Of
Microsoft $19.95
$21.35
The Willow Weeps No
More $29.95
$32.05
Was George Washington
Gay? $24.95
$26.70
Windows
Sucks
$34.95 $37.40
Writers
Market
$22.95 $24.56
Zero Loss
Finance
$21.95 $23.49
Step 3
– Add a second parameter to your function
that allows you to pass the tax percentage
as input.
CREATE OR REPLACE FUNCTION
plus_tax(p_book_retail_price number,
tax_percent number)
You
should then be able to run this query to
show sales tax at 15%:
select
plus_tax(book_retail_price, 15)
from
book
;
Reproduce the report from step 2, with a
sales tax percentage of 8%, and e-mail the
script to your instructor. Your report
should look like this:
Book List
Alphabetical
with 8% sales tax
Price
Book
Retail Plus 8%
Title
Price Tax
----------------------------------------
-------- --------
Dos For
Dummies
$19.95 $21.55
The Zen Of Auto
Repair $99.95
$107.95
Unix For
Experts
$38.95 $42.07
Bears Are People
Too $34.95
$37.75
Cooking
Light
$24.95 $26.95
How To Housebreak Your
Horse $29.95
$32.35
Managing
Stress
$39.95 $43.15
Never Eat
Boogers
$10.95 $11.83
Non Violins In The Workplace
$11.95 $12.91
Operations Research
Theory $44.95
$48.55
Oracle9i Sql
Tuning $49.95
$53.95
Pay No Taxes And Go To
Jail $10.95
$11.83
Piano
Greats
$32.95 $35.59
Reduce Spending The Republican
Way $27.95
$30.19
The Fall Of
Microsoft $19.95
$21.55
The Willow Weeps No
More $29.95
$32.35
Was George Washington
Gay? $24.95
$26.95
Windows
Sucks
$34.95 $37.75
Writers
Market
$22.95 $24.79
Zero Loss
Finance
$21.95 $23.71
ANSWERS:
Step 2
col c1 heading 'Book|Title'
format a40
col c2 heading 'Retail|Price'
format $999.99
col c3 heading 'Price|Plus 7%|Tax'
format $999.99
ttitle 'Book List|Alphabetical|with 7%
sales tax'
select
initcap(book_title) c1,
book_retail_price*1 c2,
plus_tax(book_retail_price) c3
from
book
order by
book_title
;
Step 3
col c1 heading 'Book|Title'
format a40
col c2 heading 'Retail|Price'
format $999.99
col c3 heading 'Price|Plus 8%|Tax'
format $999.99
ttitle 'Book List|Alphabetical|with 8%
sales tax'
select
initcap(book_title) c1,
book_retail_price*1 c2,
plus_tax(book_retail_price, 8) c3
from
book
order by
book_title
;
Note: These exercises may use the
pubsdb.sql script that can be
downloaded at this link.
|
|
|
|
|
|
| |
BC
Remote Oracle Support
P.O. Box 511 • Kittrell, NC, 27544
Copyright © 2007 by Burleson
Enterprises, Inc. All rights reserved.
|
|
