User Defined Functions
Syntax of Function
Function Returning Value
Returning Boolean Value
Function Syntax Returning Boolean
PL/SQL Blocks Conclusion
Procedure Percentage Average
Procedures Calling Functions
Adding Procedure Function
What is a trigger?
Trigger Procedure Distinction
Special Trigger Variables
Combination Insert, Update, Delete
Database Triggers Conclusion
Sending Parameters SqlPlus
PL/SQL Parameter Usage
Parameter Procedure Placement
Parameters Return Command Function
PL/SQL Cursor Parameters
Create Oracle Package
What is a Package?
Making Package Specification
Making Package Body
Executing Package Components
Function Returning Value - Exercise
Functions returning a number, text, or date value
Course project: Create a sales tax function
Create a function for the course project that determines the sales tax percent based on a customer's home state
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:
TAX (% of total sale, excluding shipping and handling)
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.
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.
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.
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
button to submit your answers.
Remember that you must submit all your responses to this exercise at once.