ITEC 340 Stored Procedures and Triggers Homework
Consider the following three tables holding information about a summer camp:
| Campers | |||||||||
| camperID | lastname | firstname | gender | address | city | state | zip | homephone | budget |
| 1 | Jones | David | M | 1 Main Street | Blacksburg | VA | 24060 | (540) 333-9866 | 100.00 |
| 2 | Davenport | Billy | M | 2 Eastern Ave. | Richmond | VA | 23454 | (804) 222-6789 | 200.00 |
| 3 | Smith | Sally | F | 8 West Street | Radford | VA | 24142 | (540) 673-8894 | 150.00 |
| Charges | Categories | |||||
| ChargeNum | camperID | Category | chargedate | Amount | description | |
| 1 | 1 | candy | 21-Jun-2021 | 2.00 | candy | |
| 2 | 1 | clothing | 22-Jun-2021 | 12.00 | clothing | |
| 3 | 2 | candy | 25-Jun-2021 | 1.00 | gifts | |
| 4 | 3 | gifts | 27-Jun-2021 | 20.00 | misc | |
| 5 | 2 | misc | 30-Jun-2021 | 4.00 | ||
| 6 | 1 | candy | 30-Jun-2021 | 3.00 | ||
| 7 | 1 | candy | 1-Jul-2021 | 2.00 | ||
- Create the tables above, and populate them with sample data. You can use the exact same sample data as above. Define primary keys and foreign key constraints for the tables.
- Add a column to the campers table named “spent” which will hold the total amount spent so far by that camper. Initialize the values in that column to match the total of all charges made by each camper.
- Create a SEQUENCE named ChargeNumbers that starts at the next available chargenum for your database and increments by 1 each time the next value is pulled from the sequence.
- Write a stored procedure which accepts the camperid, category, chargedate, and amount as input parameters and has one output parameter which is of type varchar2 which will contain a message.
- If the camperID is not a valid camperID the output parameter should be set to ‘ERROR: No such camper ID.’
- If the category is not a valid category the output parameter should be set to ‘ERROR: No such charge ‘
- If the Amount is not in the correct range the output parameter should be set to ‘ERROR: amount must be >0 and no more than $ 40.’
- If the Amount would cause the total spent to be more than the budget for that camper the output parameter should be set to ‘ERROR: insufficient funds.’
- If there are no error conditions the stored procedure should
- insert the new charge using the chargenumbers sequence to generate the chargenum
- update the amount spent for that camper
- if your procedure fails a rollback should be issued and if it succeeds a commit should be
- You can code for which error condition has occurred by looking at what constraint name is contained in the SQL error message SQLERRM or you can use if statements in your procedure code to perform the same tests as your If the former you may add a constraint to prevent spent field from being larger than budget field.
- Because your procedure has an OUT parameter you will have to write a short program to test the procedure like the ones illustrated in the Stored Procedures and Triggers PowerPoint.
