I\'m taking a PL/SQL course and need help writing the code for this exercise. Mo
ID: 3627121 • Letter: I
Question
I'm taking a PL/SQL course and need help writing the code for this exercise.
More Movies, a video rental store, receives numerous requests checking if movies are in stock. The company needs a FUNCTION that retrieves the movie stock information and formats a friendly message to display for user requests. The display should resemble the following: Star Wars is Available: 11 on the shelf.
Use movie_id as the input value for this function. Assume that the MOVIE_QTY column in the MM_MOVIE table indicates the number of movies currently available for checkout.
Below is the MM_MOVIE table:
select * from mm_movie;
MOVIE_ID MOVIE_TITLE MOVIE_CAT_ID MOVIE_VALUE MOVIE_QTY
1 Alien 1 10 5
2 Bladerunner 1 8 3
3 Star Wars 1 15 11
4 Chainsaw Massacre 2 7 2
5 Jaws 2 7 1
6 Good, Bad, Ugly 3 7 2
7 Silverado 3 7 1
8 Duck Soup 4 5 1
9 Planes, Trains, Autos 4 5 3
10 Waking Ned Devine 4 12 4
11 Deep Blue Sea 5 14 3
12 The Fifth Element 5 15 5
desc mm_movie;
Name Null? Type
MOVIE_ID NOT NULL NUMBER(4)
MOVIE_TITLE VARCHAR2(40)
MOVIE_CAT_ID NOT NULL NUMBER(2)
MOVIE_VALUE NUMBER(5,2)
MOVIE_QTY NUMBER(2)
This is not an advanced course, so please keep the solution as simplistic as possible. This problem is Case Project 6-2 in the text Oracle 10g Developer (Joan Casteel).
I need the solution before 11pm CST. That is the deadline for my online course.
Thanks in advance for your help!
Explanation / Answer
create or replace function getMoviedetails(movie_id number) return NUMBER as
l_count number:=0;
l_movie_count number:=0;
l_movie_name varchar2(100):=null;
begin
select count(*) into l_count from mm_movie;
if l_count > 0 then
select MOVIE_QTY,MOVIE_TITLE into l_movie_count,l_movie_name from mm_movie;
dbms_output.put_line(l_movie_name || ' is available:'||l_movie_count);
end if;
return 0;
end getMoviedetails;