Oracle 11g PL/SQL Programming

More Movie Rentals is experimenting with a new concept to make rentals more convenient. Members can request movies via the Internet, or they can check out at a store location. In either case, a small barcode sticker of the member ID and movie ID is printed at the time of rental. Its affixed to a paper slipcase for the movie, which can serve as an envelope. Members can return a movie by sending it via U.S. Mail or dropping it off at a store location. In either case, the clerk scans the member ID and movie ID from the slipcase barcodes.

Two procedures are needed to record rentals and returns in the database. Create a procedure named MOVIE_RENT_SP that adds a new record to the MM_RENTAL table and updates the movie inventory, which is the MOVIE_QTY column of the MM_MOVIE table. This procedure should accept a member ID, movie ID, and a payment method. The member ID is scanned in from a barcode on the membership card, and the movie ID is scanned in from a barcode on the movie case. The cashier selects the payment type. Test the procedure with member ID = 13, movie ID = 12, and payment method = 4. Verify that the rental has been added and the movie inventory has been updated.

The second procedure needs to record the movies return. Create a procedure named MOVIE_RETURN_SP that logs the current date in the CHECKIN_DATE column and updates the movie inventory. Inputs are the member ID and movie ID from the barcodes on the slipcase, so the procedure should determine the rental ID first. Test the procedure with member ID = 13 and movie ID = 12.