0 Replies - 4631 Views - Last Post: 23 July 2011 - 05:24 PM

#1 hash020  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 21-January 11

create a procedure part 1

Posted 23 July 2011 - 05:24 PM

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.
Compile and check your code. If you get a PROCEDURE CREATED WITH COMPILATION ERRORS message then type in SHOW ERRORS and look in your code for the line noted in the error messages (be sure to compile your code with the session command SET ECHO ON). Once you have a clean compile then your are ready to test.

I have this code


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;

ALTER MM_MOVIE
ADD COLUMN stk_flag CHAR(1);




This is my answer


create or replace procedure "MOVIE_RENTAL_SP"

(movieid IN NUMBER,
memberid IN NUMBER,
paymethod IN NUMBER)
 AS
   

errmovieid exception;
   
errmemberid exception;
   
errpayid exception;
   
nrentalid number;
  
countmovie number;
   
countmember number;
   
countpayment number;



BEGIN


select count(movie_id) into countmovie 
from mm_rental where movie_id=movieid;
    
select count(member_id) into countmember from mm_rental 
where member_id=memberid;
    
select count(payment_methods_id) into countpayment from mm_rental where payment_methods_id=paymethod;

if countmovie < 1 then
         
 raise errmovieid;         
    
end if;
   
 if countmember < 1 then
          
raise errmemberid;
    
end if;
  
 if countpayment < 1 then
       
 raise errpayid;
   
end if;
   
INSERT INTO mm_rental (rental_id,member_id,movie_id,payment_methods_id) values (mm_rental_seq.nextval,memberid,movieid,paymethod);
   
select max(rental_id) into nrentalid from mm_rental;
     
update mm_rental set checkin_date=SYSDATE where rental_id=nrentalid;
     
update mm_movie set movie_qty=movie_qty-1 where movie_id=movieid;
  
 Commit;
  
 exception
         
when errmovieid then
              
 raise_application_error(-20001, 'There is no Movie ID:' || to_char(movieid,'999'));
         
when errmemberid then
              
 raise_application_error(-20001, 'There is no Member ID:' || to_char(memberid));
        
 when errpayid then
               
raise_application_error(-20001, 'There is no Payment ID:' || to_char(paymethod));
 
when others then
              
 raise_application_error(-20011, sqlerrm);
   
     

END;





and this was the result



SQL> execute movie_rental_sp(10,20,2);
BEGIN movie_rental_sp(10,20,2); END;

*
ERROR at line 1:
ORA-20001: There is no Member ID:20
ORA-06512: at "D03231571.MOVIE_RENTAL_SP", line 39
ORA-06512: at line 1


SQL> execute movie_rental_sp(10,10,7);
BEGIN movie_rental_sp(10,10,7); END;

*
ERROR at line 1:
ORA-20001: There is no Payment ID:7
ORA-06512: at "D03231571.MOVIE_RENTAL_SP", line 40
ORA-06512: at line 1


SQL> execute movie_rental_sp(5,10,2);

PL/SQL procedure successfully completed.

SQL> execute movie_rental_sp(5,11,2);
BEGIN movie_rental_sp(5,11,2); END;

*
ERROR at line 1:
ORA-20001: There is no Member ID:11
ORA-06512: at "D03231571.MOVIE_RENTAL_SP", line 39
ORA-06512: at line 1





The last one is supposed to say no copies available but i can't figure out how to put the exception in it.

Is This A Good Question/Topic? 0
  • +

Page 1 of 1