Hi I have a relatively simple case project due for my online PL/SQL course. I ne
ID: 3627238 • Letter: H
Question
Hi I have a relatively simple case project due for my online PL/SQL course. I need to create a package to include the procedures and functions shown below.
The case project reads as follows:
In Chapter 4 More Movies case project, you created two procedures to support the check out and check in of rented movies. In Chapter 5, you created a function to allow inquries as to movie availability.
Create a package named mm_rentals_pkg to contain all these program units. Make all the program units public. Test the MOVIE_INFO function using movie ids of 6 and 7.
Here is the script to create all tables needed:
CREATE TABLE mm_movie_type
(movie_cat_id NUMBER(2),
movie_category VARCHAR(12),
CONSTRAINT movie_cat_id_pk PRIMARY KEY (movie_cat_id));
CREATE TABLE mm_pay_type
(payment_methods_id NUMBER(2),
payment_methods VARCHAR(14),
CONSTRAINT payment_methods_id_pk PRIMARY KEY (payment_methods_id));
CREATE TABLE mm_member
(member_id NUMBER(4),
last VARCHAR(12),
first VARCHAR(8),
license_no VARCHAR(9),
license_st VARCHAR(2),
credit_card VARCHAR(12),
suspension VARCHAR(1) DEFAULT 'N',
mailing_list VARCHAR(1),
CONSTRAINT cust_custid_pk PRIMARY KEY (member_id),
CONSTRAINT cust_credcard_ck CHECK (LENGTH(credit_card) = 12));
CREATE TABLE mm_movie
(movie_id NUMBER(4),
movie_title VARCHAR(40),
movie_cat_id NUMBER(2) NOT NULL,
movie_value DECIMAL(5,2),
movie_qty NUMBER(2),
CONSTRAINT movies_id_pk PRIMARY KEY (movie_id),
CONSTRAINT movie_type_fk FOREIGN KEY (movie_cat_id)
REFERENCES mm_movie_type(movie_cat_id),
CONSTRAINT movies_value_ck CHECK (movie_value BETWEEN 5 and 100));
CREATE TABLE mm_rental
(rental_id NUMBER(4),
member_id NUMBER(4),
movie_id NUMBER(4),
checkout_date DATE DEFAULT SYSDATE,
checkin_date DATE,
payment_methods_id NUMBER(2),
CONSTRAINT rentals_pk PRIMARY KEY (rental_id),
CONSTRAINT member_id_fk FOREIGN KEY (member_id)
REFERENCES mm_member(member_id),
CONSTRAINT movie_id_fk FOREIGN KEY (movie_id)
REFERENCES mm_movie(movie_id),
CONSTRAINT pay_id_fk FOREIGN KEY (payment_methods_id)
REFERENCES mm_pay_type(payment_methods_id));
Create sequence mm_rental_seq start with 13;
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card)
VALUES (10, 'Tangier', 'Tim', '111111111', 'VA', '123456789111');
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card, mailing_list)
VALUES (11, 'Ruth', 'Babe', '222222222', 'VA', '222222222222', 'Y');
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card, mailing_list)
VALUES (12, 'Maulder', 'Fox', '333333333', 'FL', '333333333333', 'Y');
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card)
VALUES (13, 'Wild', 'Coyote', '444444444', 'VA', '444444444444');
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card, mailing_list)
VALUES (14, 'Casteel', 'Joan', '555555555', 'VA', '555555555555', 'Y');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
VALUES ( '1', 'SciFi');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
VALUES ( '2', 'Horror');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
VALUES ( '3', 'Western');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
VALUES ( '4', 'Comedy');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
VALUES ( '5', 'Drama');
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (1, 'Alien', '1', 10.00, 5);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (2, 'Bladerunner', '1', 8.00, 3);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (3, 'Star Wars', '1', 15.00, 11);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (4,'Texas Chainsaw Massacre', '2', 7.00, 2);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (5, 'Jaws', '2', 7.00,1);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (6, 'The good, the bad and the ugly', '3', 7.00,2);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (7, 'Silverado', '3', 7.00,1);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (8, 'Duck Soup', '4', 5.00,1);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (9, 'Planes, trains and automobiles', '4', 5.00,3);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (10, 'Waking Ned Devine', '4', 12.00,4);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (11, 'Deep Blue Sea', '5', 14.00,3);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
VALUES (12, 'The Fifth Element', '5', 15.00,5);
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
VALUES ('1', 'Account');
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
VALUES ('2', 'Credit Card');
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
VALUES ('3', 'Check');
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
VALUES ('4', 'Cash');
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
VALUES ('5', 'Debit Card');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (1,'10', '11', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (2,'10', '8', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (3,'12', '6', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (4,'13', '3', '5');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (5,'13', '5', '5');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (6,'13', '11', '5');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (7,'14', '10', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (8,'14', '7', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (9,'12', '4', '4');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (10,'12', '12', '4');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (11,'12', '3', '4');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
VALUES (12,'13', '4', '5');
UPDATE mm_rental
SET checkout_date = '04-JUN-07';
UPDATE mm_rental
SET checkin_date = '06-JUN-07'
WHERE rental_id IN(1,4,5,7,10);
COMMIT;
Here is the origianl code for the Chapter 4 assignment:
ALTER TABLE mm_movie
ADD stk_flag char(1);
DECLARE
CURSOR cur_movie IS
SELECT movie_value, movie_qty
FROM mm_movie
FOR UPDATE NOWAIT;
lv_flag mm_movie.stk_flag%TYPE;
BEGIN
FOR rec_movie IN cur_movie LOOP
IF rec_movie.movie_value * rec_movie.movie_qty >= 75 THEN
lv_flag := '*';
ELSE lv_flag := NULL;
END IF;
UPDATE mm_movie
SET stk_flag = lv_flag
WHERE CURRENT OF cur_movie;
END LOOP;
COMMIT;
END;
/
Here is the original code for Chapter 5 case project.
SET SERVEROUTPUT ON
/* This sequence will enable the auto-increment of the rental_id column. */
create sequence rental_id_seq start with 13;
Create or replace procedure MOVIE_RENT_SP(movieid number, memberid number, paymentmethod number) as
FOREIGN_KEY_VIOLATION EXCEPTION;
PRAGMA EXCEPTION_INIT(FOREIGN_KEY_VIOLATION, -2291);
begin
savepoint start_transaction;
insert into MM_RENTAL(rental_id,member_id,movie_id,checkout_date,payment_methods_id) values(rental_id_seq.nextval,memberid,movieid,sysdate,paymentmethod);
update MM_MOVIE set movie_qty=movie_qty-1 where movie_id=movieid;
commit;
Exception
When FOREIGN_KEY_VIOLATION then
dbms_output.put_line('Foreign Key Violation. Value not found in Parent table');
rollback to start_transaction;
When INVALID_NUMBER Then
dbms_output.put_line('Please enter only numbers');
rollback to start_transaction;
When ROWTYPE_MISMATCH then
dbms_output.put_line(' Invalid data type or precision');
rollback to start_transaction;
When VALUE_ERROR then
dbms_output.put_line('Error with data type');
When OTHERS Then
dbms_output.put_line('An error has occurred due to invalid data');
rollback to start_transaction;
end;
/
/* Test using member id = 13, movie id = 12, and payment method = 4 */
exec MOVIE_RENT_SP(12,13,4);
select * from mm_rental;
select * from mm_movie;
Create or replace procedure MOVIE_RETURN_SP(movieid number, memberid number) as
movid mm_rental.movie_id%type;
memid mm_rental.member_id%type;
begin
savepoint start_transaction;
select movie_id,member_id into movid, memid from mm_rental where movie_id=movieid;
update MM_RENTAL set checkin_date=sysdate where member_id=memberid and movie_id=movieid;
update MM_MOVIE set movie_qty=movie_qty+1 where movie_id=movieid;
commit;
Exception
When NO_DATA_FOUND then
dbms_output.put_line('Memberid or movie id not found');
rollback to start_transaction;
When INVALID_NUMBER Then
dbms_output.put_line('Please enter only numbers');
rollback to start_transaction;
When ROWTYPE_MISMATCH then
dbms_output.put_line(' Invalid data type or precision');
rollback to start_transaction;
When VALUE_ERROR then
dbms_output.put_line('Error with data type');
rollback to start_transaction;
When OTHERS Then
dbms_output.put_line('An error has occurred due to invalid data');
rollback to start_transaction;
end;
/
/* Test for the rental recorded above: member id = 13, movie id = 12 */
exec movie_return_sp(12,13);
select * from mm_rental;
select * from mm_movie;
Hints for this case project:
Basically there are two parts in this assignment, the package specifications and the package body.
1. First, in the specifications we need to make public variable declarations for any return variables.
2. Then we list our procedure and functions.....and within them, their local variable exactly as they are declared in the the previous assignments.
3. Be sure to include the return declaration from the funtion.
4. Next, the body is created and it is simply the function and the 2 procedures just copied and pasted in. The only sufferance is the first and line line. Use page 234 for the mock version.
<b>5. Remember to Test the MOVIE_INFO function
using movie ids of 6 and 7.</b>
Thanks in advance for your help.
Explanation / Answer
NOTE : -- not included the MOVIE_INFO function since its body is not provided in question
-- step 1 package specification
CREATE OR REPLACE PACKAGE mm_rentals_pkg AS
TYPE t_ret_val IS REF BOOLEAN;
FUNCTION MOVIE_INFO RETURN t_ret_val;
PROCEDURE MOVIE_RENT_SP(movieid number, memberid number, paymentmethod number)
PROCEDURE MOVIE_RETURN_SP(movieid number, memberid number);
END mm_rentals_pkg;
-- step 2 package body
CREATE OR REPLACE PACKAGE BODY mm_rentals_pkg AS
procedure MOVIE_RENT_SP(movieid number, memberid number, paymentmethod number) as
FOREIGN_KEY_VIOLATION EXCEPTION;
PRAGMA EXCEPTION_INIT(FOREIGN_KEY_VIOLATION, -2291);
begin
savepoint start_transaction;
insert into MM_RENTAL(rental_id,member_id,movie_id,checkout_date,payment_methods_id) values(rental_id_seq.nextval,memberid,movieid,sysdate,paymentmethod);
update MM_MOVIE set movie_qty=movie_qty-1 where movie_id=movieid;
commit;
Exception
When FOREIGN_KEY_VIOLATION then
dbms_output.put_line('Foreign Key Violation. Value not found in Parent table');
rollback to start_transaction;
When INVALID_NUMBER Then
dbms_output.put_line('Please enter only numbers');
rollback to start_transaction;
When ROWTYPE_MISMATCH then
dbms_output.put_line(' Invalid data type or precision');
rollback to start_transaction;
When VALUE_ERROR then
dbms_output.put_line('Error with data type');
When OTHERS Then
dbms_output.put_line('An error has occurred due to invalid data');
rollback to start_transaction;
end MOVIE_RENT_SP;
procedure MOVIE_RETURN_SP(movieid number, memberid number) as
movid mm_rental.movie_id%type;
memid mm_rental.member_id%type;
begin
savepoint start_transaction;
select movie_id,member_id into movid, memid from mm_rental where movie_id=movieid;
update MM_RENTAL set checkin_date=sysdate where member_id=memberid and movie_id=movieid;
update MM_MOVIE set movie_qty=movie_qty+1 where movie_id=movieid;
commit;
Exception
When NO_DATA_FOUND then
dbms_output.put_line('Memberid or movie id not found');
rollback to start_transaction;
When INVALID_NUMBER Then
dbms_output.put_line('Please enter only numbers');
rollback to start_transaction;
When ROWTYPE_MISMATCH then
dbms_output.put_line(' Invalid data type or precision');
rollback to start_transaction;
When VALUE_ERROR then
dbms_output.put_line('Error with data type');
rollback to start_transaction;
When OTHERS Then
dbms_output.put_line('An error has occurred due to invalid data');
rollback to start_transaction;
end MOVIE_RETURN_SP;
-- not included the MOVIE_INFO function since its body is not provided in question
END mm_rentals_pkg;
-- step 3 package test
mm_rentals.pkg.MOVIE_INFO(6,7) ;