please i need help with this pl/sql program Run the script, C1_MMcreate.sql, to

Question

please i need help with this pl/sql program

Run the script, C1_MMcreate.sql, to create the tables required for this lab. The Moore Movie Rentals receives numerous requests to check whether movies are in stock. The company needs a function that retrieves movie stock information and formats a clear message to display to users requesting the information. The display returned by the function should look like the following: “Star Wars is Available: 11 on the shelf” Or “Star Wars is Not Available:” Use the Movie ID field as the input value for this function. Assume the MOVIE_QTY column in the MM_MOVIES table indicate the number of movies currently available for check out. Do not use the DBMS_OUTPUT.PUT_LINE procedure in the function. Test the function with an anonymous block using the Movie IDs: 2, 3, and 20.

Explanation / Answer

CREATE PROCEDURE IS_MOVIE_AVAILABLE( IN MovieID INT, OUT result VARCHAR(255))
BEGIN
DECLARE count_movie INT
DECLARE name VARCHAR(255)

SELECT MOVIE_QTY INTO count_movie, MOVIE_NAME INTO name FROM MM_MOVIES WHERE MOVIE_ID = MovieID

If (count_movie > 0) then
   set result = CONCAT(name , “ is available:” , count_movie , “ on the shelf.”);
Else
   set result = CONCAT(name , “ is not available: ”)
Endif;
END

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.