Ilab 4 - PROCEDURE movie_return_sp (p_id IN...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Sheet1 Page 1 -----------CREATE MOVIE PACKAGE-------------- CREATE OR REPLACE PACKAGE mm_rentals_pkg IS PROCEDURE movie_rent_sp (p_id IN mm_rental.member_id%type, p_movie IN mm_rental.movie_id%type, p_pay IN mm_rental.payment_methods_id%type) PROCEDURE movie_return_sp (p_id IN mm_rental.member_id%type, p_movie IN mm_rental.movie_id%type) FUNCTION movie_message_sf (p_idmovie IN NUMBER) RETURN VARCHAR2 END / ------------CREATE PACKAGE BODY---------------- CREATE OR REPLACE PACKAGE BODY mm_rentals_pkg IS ----------UPDATE AND INSERTION OF NEW VALUES---------- PROCEDURE movie_rent_sp (p_id IN mm_rental.member_id%type, p_movie IN mm_rental.movie_id%type, p_pay IN mm_rental.payment_methods_id%type) IS BEGIN INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id) VALUES (rent_seq.nextval, p_id, p_movie, p_pay) UPDATE mm_movie SET movie_qty=movie_qty-1 WHERE mm_movie.movie_id=p_movie END movie_rent_sp ----------PROCEDURE FOR MOVIE RETURN------------------
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: PROCEDURE movie_return_sp (p_id IN mm_rental.member_id%type, p_movie IN mm_rental.movie_id%type) IS lv_returndate mm_rental.checkin_date%type BEGIN SELECT sysdate INTO lv_returndate FROM dual UPDATE mm_rental SET checkin_date=lv_returndate WHERE rental_id=p_id---------UPDATE MOVIE QTY-----------------------------UPDATE mm_movie SET movie_qty=movie_qty+1 WHERE mm_movie.movie_id=p_movie END movie_return_sp Sheet1 Page 2-------------CREATE PROCEDURE FOR MESSAGE-------------FUNCTION movie_message_sf (p_idmovie IN NUMBER) RETURN VARCHAR2 IS lv_qty NUMBER lv_title mm_movie.movie_title%type lv_message VARCHAR2(100) BEGIN SELECT movie_qty, movie_title INTO lv_qty, lv_title FROM mm_movie WHERE movie_id=p_idmovie lv_message:=(lv_title||' is available: '||lv_qty||' on the shelf.') RETURN lv_message END movie_message_sf END /...
View Full Document

Page1 / 2

Ilab 4 - PROCEDURE movie_return_sp (p_id IN...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online