Oracle 11g PL/SQL Programming More Movie Rentals is experimenting with a new con

Question

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. It’s
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 movie’s 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.

Explanation / Answer

create or replace procedure MOVIE_RETURN_SP
(
memberID in number,
movieID in number
)
IS
quantity number;
begin
select count(*) into quantity from MM_MOVIE where movie_ID = movieID;
quantity := quantity+1;
update MM_MOVIE set CHECKIN_DATE = sysdate, MOVIE_QTY=quantity where movie_ID = movieID;
commit;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

end;

create or replace procedure MOVIE_RENT_SP
(
memberID in number,
movieID in number,
paymentmethod in number
)
IS
quantity number;
begin
insert into MM_RENTAL (MEMBER_ID, MOVIE_ID, PAYMENT_METHOD )values(memberID,movieID,paymentmethod);
select count(*) into quantity from MM_MOVIE where movie_ID = movieID;
quantity := quantity-1;
update MM_MOVIE set MOVIE_QTY =quantity where movie_ID = movieID;
commit;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

end;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.