Create a stored procedure named prc_new_detail to insert new rows in the DETAILRENTAL table. The procedure should satisfy the following requirements.
a. The video number will be provided as a parameter.
b. Verify the video number exists in the VIDEO table. If it does not exist, then display a message that the video does not exist, and do not write any data to the database.
c. If the video number does exist, then verify that the VID_STATUS for that video is “IN”. If the status is not “IN”, then display a message that the return of the video must be entered before it can be rented again, and do not write any data to the database.
d. If the status is “IN”, then retrieve the values of PRICE_RENTFEE, PRICE_DAILYLATEFEE, and PRICE_RENTDAYS associated with the video from the PRICE table.
e. Calculate the due date for the video rental by adding the number of days found in PRICE_RENTDAYS above to 11:59:59PM (hours:minutes:seconds) on the current system date.
f. Insert a new row in the DETAILRENTAL table using the previous value returned by rent_num_seq as the RENT_NUM, the video number provided in the parameter as the VID_NUM, the PRICE_RENTFEE as the value for DETAIL_FEE, the due date calculated above for the DETAIL_DUEDATE, PRICE_DAILYLATEFEE as the value for DETAIL_DAILYLATEFEE, and null for the DETAIL_RETURNDATE.

  • CreatedDecember 03, 2014
  • Files Included
Post your question