Function Returning Value - Exercise

Functions returning number, text, or date value

Course project: Create a sales tax function using a function in Oracle PL/SQL

Objective: Create a function for the course project that determines the sales tax percent based on a customer's home state

Exercise scoring

This exercise is worth a total of 25 points. You will be awarded points for

proper syntax of all SQL and PL/SQL,
proper use of logic,
use of exception handling, and
quality of documentation.

Once you have completed your answer you will submit the exercise.

Background and overview

When a customer at the Pet Store makes a purchase, the total order must include sales tax. We will assume that we must charge each customer the sales tax percentage that is appropriate for his or her state of residence. Although this is usually done only for mail order purchases, for our Pet Store project, we will do it for all purchases. Here is the sales tax rate table by state:

STATE	TAX (% of total sale, excluding shipping and handling)
CA	4.5
HI	4.03
NE	3.5
WI	5.0

The function should be named GET_PERCENT. It will accept a single parameter: the CUST_ID. The returned value should be the sales tax percent in decimal format. For example, if the tax is 5.3%, the returned value should be .053.
In addition to the function, you should write an UPDATE command that uses the function. The UPDATE command updates one row in the CUSTOMER_SALE record and sets the TAX_AMOUNT column to equal the sum of all rows in the SALE_ITEM table for this sale, multiplied by the sales tax found by the function you have created.
The row to update has the SALES_ID = 34.

Download files

If you have not already done so, download the PETSTORE schema files from the Resources page if you plan to install them into your own database.

Instructions

Include comments and exception handlers in your SQL code. Submit the PL/SQL script, the UPDATE command, and documentation to explain how your function works.

Hints

You will score higher if you use exception handling for error conditions.
Your UPDATE command should update one row in the CUSTOMER_SALE table; however, the function should work on any row in the CUSTOMER_SALE table.

Your documentation should include the file name, date, author; a list and explanation of parameters, a list and explanation of all errors handled, and a short description of what the function is supposed to do.

Submitting your exercise

Enter your PL/SQL, your UPDATE command, and your documentation into the text box below. Click the Submit button to submit your answers.
Remember that you must submit all your responses to this exercise at once.