FROM mm_movie m, mm_rental r
WHERE m.movie_id = r.movie_id
AND m.movie_id = :g_movie_id
GROUP BY m.movie_title;
DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count);
EXCEPTION
WHEN INVALID_NUMBER THEN
dbms_output.put_line('Invalid Input. Movie Id should be a number');
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Movie not found');
WHEN OTHERS THEN
dbms_output.put_line('Unexpected Error');
end;
Output:
Copyright ©2009 Cardean Learning Group LLC. All rights reserved.

Invalid Input. Movie Id should be a number
Another set of input
SET SERVEROUTPUT ON
VARIABLE g_movie_id number
BEGIN
:g_movie_id := 98;
END;
/
DECLARE
v_count NUMBER;
v_title mm_movie.movie_title%TYPE;
BEGIN
SELECT m.movie_title, COUNT(r.rental_id)
INTO v_title, v_count
FROM mm_movie m, mm_rental r
WHERE m.movie_id = r.movie_id
AND m.movie_id = :g_movie_id
GROUP BY m.movie_title;
DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count);
EXCEPTION
WHEN INVALID_NUMBER THEN
dbms_output.put_line('Invalid Input. Movie Id should be a number');
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Movie not found');
WHEN OTHERS THEN
dbms_output.put_line('Unexpected Error');
end;
Output
Movie not found
Copyright ©2009 Cardean Learning Group LLC. All rights reserved.
