Your first procedure is to be named MOVIE_RENTAL_SP and is going to provide functionality to process movie rentals. Based on data that will represent the movie id, member id and payment method your procedure will need to generate a rental id and then insert a new row of data into the mm_rental table. The process will also need to update the quantity column in the mm_movie table to reflect that there is one less copy of the rented movie in stock. Along with the processing you will also need to define some user-defined exception handlers that will be used in validating the input data. Since you may need to recreate your procedure several times during the debugging process it is suggested that you use the CREATE OR REPLACE syntax at the beginning of the CREATE statement.
The following steps will help you in setting up your code.
You will need to define three parameters, one each for movie id, member id, and payment method. Make sure that each one matches the data type of the associated column in the database tables.
You will have several other variables that will need to be identified and defined. It might be easier to read through the rest of the specs before you start trying to define these (look for hints in the specifications.
You will need to define four user-defined exceptions; one for unknown movies, one for unknown member, one for unknown payment method, and one for if a movie is unavailable.
You will need to validate each of the three pieces of data passed to the procedure. One easy way to do this might be to use a SELECT statement with the COUNT function to return a value into a variable based on a match in the database table against the piece of data that you are validating. If the query returns a zero then there is no match and the data is invalid; any value greater than zero means a match was found and thus the data is valid. You will need the following validations.
Validate the movie id to make sure it is valid. If not then raise the unknown movie exception.
Validate the member id to make sure one exists for that id. If not then raise the unknown member exception.
Validate the payment method to make sure it exists. If not then raise the unknown payment method exception.
Check the movie quantity to make sure that there is a movie to be rented for the movie id. If not then raise the unavailable movie exception.
If all the data passes validation then you will need to create a new rental id. This process should be in a nested block with its own EXCEPTION section to catch a NO_DATA_FOUND exception if one should happen. You can generate a new rental id by find the largest rental id value in the mm_rental table (Hint: MAX function) and then increasing that value by one. The NO_DATA_FOUND exception would only be raised if there were no rental id's in the table.
Now you are ready to insert a new row of data into the mm_rental table. Use the SYSDATE function for the check out date and NULL for the check in date.
Now update the mm_movie table to reflect one less movie for the associated movie id.
Finally you will need to set up an EXCEPTION section for all of your exception handling. For each exception output you want to state what the problem is, the invalid data value and a note that the rental cannot proceed. For example, for an invalid movie id number you might say "There is no movie with id: 13 - Cannot proceed with rental". You also want to include a WHEN OTHERS exception handler.
Using this code as a reference:
DROP TABLE MM_MOVIE_TYPE CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_pay_type CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_member CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_movie CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_rental CASCADE CONSTRAINTS PURGE;
DROP SEQUENCE mm_rental_seq;
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 Masacre', '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-03';
COMMIT;
DROP TABLE MM_MOVIE_TYPE CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_pay_type CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_member CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_movie CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_rental CASCADE CONSTRAINTS PURGE;
DROP SEQUENCE mm_rental_seq;
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 Masacre', '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-03';
COMMIT;
update mm_movie
add column stk_flag char(1)
SQL>
1 DECLARE
2 cursor mov IS
3 select movie_qty, movie_value from mm_movie
4 for UPDATE NOWAIT;
5 BEGIN
6 FOR mm_movie IN mov
7 LOOP
8 IF mm_movie.movie_value * mm_movie.movie_qty >= 75 THEN
9 UPDATE mm_movie SET stk_flag = '*' WHERE CURRENT OF mov;
10 ELSE
11 UPDATE mm_movie SET stk_flag = NULL WHERE CURRENT OF mov;
12 END IF;
13 END LOOP;
14 COMMIT;
15* END;
SQL> /
SQL> --Question 3--
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
2
3 v_count NUMBER;
4 v_title mm_movie.movie_title%TYPE;
5 v_movie_id NUMBER;
6 v_step NUMBER;
7
8 BEGIN
9
10 v_movie_id := 2;
11
12 v_step := 1;
13 SELECT count(*) INTO v_count FROM mm_movie WHERE movie_id = v_movie_id;
14
15 v_step := 2;
16 SELECT count(*) INTO v_count FROM mm_rental WHERE movie_id = v_movie_id;
17
18 SELECT m.movie_title, COUNT(r.rental_id) INTO v_title, v_count
19 FROM mm_movie m, mm_rental r
20 WHERE m.movie_id = r.movie_id
21 AND m.movie_id = v_movie_id
22 GROUP BY m.movie_title;
23
24 DBMS_OUTPUT.PUT_LINE('For title '|| v_title || ' the count = ' || v_count);
25
26 EXCEPTION
27 WHEN NO_DATA_FOUND THEN
28 IF (v_step = 1) THEN
29 DBMS_OUTPUT.PUT_LINE('Movie ID '||v_movie_id||' was not found.');
30 ELSE
31 DBMS_OUTPUT.PUT_LINE('Movie ID '||v_movie_id||' was never rented.');
32 END IF;
33
34 WHEN OTHERS THEN
35 DBMS_OUTPUT.PUT_LINE(
36 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
37 END;
38
39 /
and this is what I have now
spool patny_week3_iLab.txt;
create or replace procedure movie_rental_sp
(movie_id IN mm_movie, member_id IN mm_member, payment_method IN mm_pay_type, movie qty IN mm_movie)
IS
BEGIN
IF movie_id =
then
ELSE IF
DBMS_OUTPUT.PUT_LINE('There is no movie with ID:'|| ||'cannot proceed with rental.');
END IF;
IF member_id =
then
ELSE IF;
DBMS_OUTPUT.PUT_LINE('There is no member with ID:|| ||'cannot proceed with rental.');
IF payment_method =
then
ELSE IF
DBMS_OUTPUT.PUT_LINE('We don't have'|| || as a payment method.');
END IF;
IF movie_qty => 0
then
ELSE IF
DBMS_OUTPUT.PUT_LINE('We don't have enough copies of said movie.' );
END IF;
BEGIN
DECLARE
rentalID NUMBER( 4);
select max(rental_id)
into rentalID
from rental
where max(rental_id) + 1
insert into rental_id(rental_id, member_id, movie_id, checkout_date, checkin_date, payment_methods_id)
VALUES(, 10, 13, SYSDATE, NULL,
EXECEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('THE FOLLOWING ERROR OCCURRED:');
DBMS_OUTPUT.PUT_LINE('THIS IS THE CODE NUMBER:' ||SQLCODE);
DBMS_OUTPUT.PUT_LINE('THIS IS THE MESSAGE:'||SQLERRM);
END;
EXECEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('THE FOLLOWING ERROR OCCURRED:');
DBMS_OUTPUT.PUT_LINE('THIS IS THE CODE NUMBER:' ||SQLCODE);
DBMS_OUTPUT.PUT_LINE('THIS IS THE MESSAGE:'||SQLERRM);
END;
/