View the step-by-step solution to:

DROP DROP DROP DROP DROP DROP TABLE MM_MOVIE_TYPE CASCADE CONSTRAINTS PURGE; TABLE mm_pay_type CASCADE CONSTRAINTS PURGE; TABLE mm_member CASCADE

I'm having trouble creating this procedure in oracle sql plus. I have already created one procedure

Your second procedure should be named MOVIE_RETURN_SP and should facilitate the process of checking a movie rental back in. For this procedure you will only need to pass one piece of data to the procedure; the rental id. You will need two user-defined exceptions; one for no rental record and one for already returned. You will be able to use several of the same techniques you used in the first procedure for your validation.

The following steps will help in setting up your code.

You will need to define only one parameter for the rental id number. Make sure that it matches the data type of the associated column in the database table.
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 the two user-defined exceptions mentioned above.
You will need to validate the rental id that is passed to the procedure. If it is not a valid one then raise the associated exception.
If it is valid then get the movie id and check in date from the mm_rental table.
Now check the check in date to make sure that it is NULL. If it is not then raise the associated exception.
If everything checks out then update the mm_rental table for the rental id you have and use the SYSDATE function for the check in date.
Now you can update the quantity in the mm_movie table for the associated movie id to reflect that the movie is back in stock.
Last, set up your exception section using appropriate error message text and data.
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.

and I'm having trouble creating this function

Your function should be named MOVIE_STOCK_SF and will be used to return a message telling the user whether a movie title is available or not based on the movie id passed to the function. The exception handling that will be needed is for NO_DATA_FOUND but we are going to set it up as a RAISE_APPLICATION_ERROR.

The following steps will help in setting up your code.

You will need to define only one parameter for the movie id number. Make sure that it matches the data type of the associated column in the database table. Also, since you will be returning a notification message you will want to make sure your RETURN statement references a data type that can handle that (Hint: variable length data type).
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 not be doing any validation so the first thing you need to do is retrieve the movie title and quantity available from the mm_movie table based on the id passed to the function.
Now you need to determine if any are available. IF the value in the quantity column is greater than zero then you will be returning a message saying something like "Star Wars is available: 0 on the shelf", ELSE if the value is zero then you should return a message saying something like "Star Wars is currently not available". Hint: A good way to return a test string is to assign it to a variable and then simply use the variable name in the RETURN clause.
Finally, set up your exception section to use a RAISE_APPLICATION_ERROR for the NO_DATA_FOUND exception handler. Assign an error number of -20001 to it and an error message that states there is no movie available for the id (be sure to include the id in the message).
Compile and check your code. If you get a FUNCTION 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 attached the sql script

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');
Background image of page 1
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)
Background image of page 2
Show entire document

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question