Week 3: Procedures and Functions - iLab Print This Page Connect to the iLab here. Lab 3:

Week 3: Procedures and Functions - iLabPrint This PageConnect to the iLab here.Lab 3: Procedures and Functions (50 Points)See Syllabus "Due Dates for Assignments & Exams" for due date information. Submit your assignment to the Dropbox located on the silver tab at the top of this page.L A B O V E R V I E WScenario/SummaryThis week, we will expand the concepts used in Lab 2 and write a couple of procedures and a function that can be stored in the database. For all three of these program units, you will need to define parameters that will allow data to be passed back and forth. You will also be embedding SQL commands in the application code so that the program can communicate with the database.For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. To successfully test your procedures and functions, you will need to execute several test cases so that all of the functionality can be evaluated. It is conceivable that you will have more files in the end than you did for Lab 2 so consider placing all the files when finished into a single ZIP file to submit to the Dropbox. Remember to spool your output to a file named with your last name plus lab 3 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab3.txt. Submit all the spooled output files AND the script file for grading of the lab.IMPORTANT: Be sure that you keep a copy of the script that you use to create your program units as you will be using it in the next lab.L A B S T E PStep 1: Creating the First ProcedureYour 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 finding 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 IDs 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 checkout 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.Step 2: Testing the First ProcedureYou will need to test for scenarios that will allow both a clean movie rental and test each exception. This means that you will need to run at least five test cases. One each for the following:No movie for the ID supplied (use 13, 10, and 2 for the parameters).No member for the ID supplied (use 10, 20, and 2 for the parameters).No payment method for the ID supplied (use 10, 10, and 7 for the parameters).A successful rental (use 5, 10, and 2 for the parameters).No movie available for the ID supplied (use 5, 11, and 2 for the parameters). Since there is only one movie available for ID 5, you will get this exception.Your output from the testing should look similar to (this would be the output for the first test above):exec movie_rent_sp(13, 10, 2);Output:There is no movie with id: 13 Cannot proceed with rental PL/SQL procedure successfully completed.Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.Step 3: Creating the Second ProcedureYour 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.Step 4: Testing the Second ProcedureYou will need to test for scenarios that will allow both a clean rental return and test each exception. This means that you will need to run at least three test cases. One each for the following:No rental for the ID supplied (use 20 for the parameter).A successful rental return (use 1 for the parameter).Try to return the same rental in Step 2.You output from the testing should look similar to (this would be the output for the first test above):exec movie_return_sp(20);Output:There is no rental record with id: 20 Cannot proceed with return PL/SQL procedure successfully completed.Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.Step 5: Creating the FunctionYour 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.Step 6: Testing the FunctionYou will need to test for all three possible scenarios.Test for a movie in stock using movie ID 11.Test for a movie not in stock using movie ID 5 (from your tests of the second procedure above, the quantity should be 0).Test for an invalid movie ID using movie ID 20.For test number 2, you may need to manipulate the quantity amount in the database, which will be fine.Test your function by using a select statement against the DUAL table like in the example below:select movie_stock_sf(20) from dual;Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.This concludes the Lab for Week 3.DeliverablesYour deliverable submission should consist of your Lab 3 script file and the spooled output files described at the beginning of the lab. 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_typeNUMBER(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),VARCHAR(14),CONSTRAINT payment_methods_id_pk PRIMARY KEY (payment_methods_id));CREATE TABLE mm_member(member_id NUMBER(4),VARCHAR(12),VARCHAR(8),VARCHAR(9),VARCHAR(2),credit_card VARCHAR(12),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_movieNUMBER(4),movie_title VARCHAR(40),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),NUMBER(4),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;