Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     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.

For a complete overview of Oracle SQL, see the book "Easy Oracle SQL".  It's only $9.95 and has a downloadable code depot:

http://www.rampant-books.com/book_2005_1_easy_sql.htm

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.