The exprodupdate exception is a user defined exception which means that the

The exprodupdate exception is a user defined

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-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; /
Image of page 10

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture