93%(44)41 out of 44 people found this document helpful
This preview shows page 10 out of 10 pages.
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-2ALTER TABLE mm_movieADD stk_flag CHAR(1);DECLARECURSOR cur_movie ISSELECT movie_id, stk_flag, movie_value*movie_qty stockfrom mm_movieFOR UPDATE NOWAIT;lv_promo_txt CHAR(1);BEGINFOR rec_movie IN cur_movie LOOPIF rec_movie.stk_flag = '*' AND rec_movie.stock < 75 THENUPDATE mm_movieSET stk_flag = NULLWHERE CURRENT OF cur_movie;END IF;IF rec_movie.stock >= 75 THENUPDATE mm_movieSET stk_flag = '*'WHERE CURRENT OF cur_movie;END IF;END LOOP;COMMIT;END;/