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

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

 
 
 

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.