In this lab assignment, you will create a stored procedure and a function. Make sure that you retain scripts with the CREATE statements for this assignment-you will need them in the following lab assignments when you will put them all in a package.
While you test your program units, you will probably change some data in your More Movie Rentals database. This okay and no cause for concern. Below is the list of things that you need to do for this assignment:
1. Create two stored PL/SQL procedures that process movie rental and movie return, based on the movie id and member id. More specifically:
? Procedure MOVIE_RENT_SP takes in three parameters: the movie id and the member id, and a payment method. It adds a new record to the MM_RENTAL table. It should also update the movie inventory, which is the MOVIE_QTY column of the MM_MOVIE table.
? Procedure MOVIE_RETURN_SP takes in two parameters: the movie id and the member id. Based on these two values, it identifies the rental record in the MM_RENTAL table and records the current date in the CHECKIN_DATE column. It also needs to update the movie inventory in the MM_MOVIE table.
? Make sure you validate the parameters: The movie id and member id must indicate an existing movie and member, respectively, and the payment method must be one of the valid payment method codes.
Run tests that include various cases of calling these procedures with valid and invalid parameter values to prove that your code identifies the case and reacts properly.
2. Write a function that retrieves the movie stock information and formats it in a friendly message to display for user requests. The display should resemble the following: "Star Wars is available: 11 on the shelf".
More specifically, create a function named MOVIE_STOCK_SF that takes in a movie id as a parameter. It should retrieve from the MM_MOVIE table the movie title and quantity information, build the output string and return it.
Again, make sure that your function behaves properly when given a non-existing movie id. Also, make sure that if a movie exists, but there are no copies available, you display a message like: "Star Wars is currently not available" rather than "Star Wars is available: 0 on the shelf".
Run tests with function calls that cover all possible scenarios including existing and non-existing movie ids, and available and unavailable movies. You probably will need to change some data in your tables to create a particular scenario.
This solution deals with Movie rental database. It has a two stored procedures and a stored function.