The ex_prod_update exception is a user defined exception which means that the exception is not
related to an Oracle error.
In this case, if the product description update does not succeed a message needs
to be displayed to inform the user that an invalid product id was entered.
An update that does not affect
any rows does not raise an Oracle error, therefore, a user defined exception needs to be created and
explicitly raised in the executable section of the block if appropriate.
Block 2:
The NO_DATA_FOUND exception is a predefined Oracle exception.
If the SELECT statement
in the executable section does not return any rows Oracle will raise an error. This error is so common, the
Oracle system has already defined the exception NO_DATA_FOUND and associated it with the
appropriate Oracle error number.
The WHEN OTHERS exception handler allows unexpected errors that
occur to be trapped and handled.
Case 4-2
ALTER TABLE mm_movie
ADD stk_flag CHAR(1);
DECLARE
CURSOR cur_movie IS
SELECT movie_id, stk_flag, movie_value*movie_qty stock
from mm_movie
FOR UPDATE NOWAIT;
lv_promo_txt CHAR(1);
BEGIN
FOR rec_movie IN cur_movie LOOP
IF rec_movie.stk_flag = '*' AND rec_movie.stock < 75 THEN
UPDATE mm_movie
SET stk_flag = NULL
WHERE CURRENT OF cur_movie;
END IF;
IF rec_movie.stock >= 75 THEN
UPDATE mm_movie
SET stk_flag = '*'
WHERE CURRENT OF cur_movie;
END IF;
END LOOP;
COMMIT;
END;
/
