 |
|
Oracle Concepts by Burleson
Consulting |
Create a Custom SQL BIF
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
Oracle 10g 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
Oracle 10g 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
;
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
|
|
