ITEC 340 Stored Procedures and Triggers Homework

Consider the following three tables holding information about a summer camp:



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.
Powered by WordPress