One of the major confusions for a PL/SQL programming beginners is how to exit a code block when a specific exception happens. Today we will see how we can handle these situation using user defined exceptions.
A developer can define as many exceptions for the code block and raise them as and when needed, than the default exception block for a BEGIN..END; block
Here I am trying to explain how a PL/SQL developer could exit the execution as soon as the first exception happens.
SET SERVEROUTPUT ON; DECLARE l_number NUMBER := 11; myexp EXCEPTION; BEGIN BEGIN IF l_number = 10 THEN RAISE myexp; END IF; EXCEPTION WHEN myexp THEN dbms_output.put_line('Okay, let us exit'); RETURN; END; BEGIN dbms_output.put_line('Looks like there were no exceptions, let us show this!'); END; END;
The above example is trying to demonstrate how the exceptions are handled by dividing the code into multiple BEGIN..END blocks. So here I am doing a check whether the “l_number” is 10 and to raise an user defined exception and by merely calling RETURN exit the complete PL/SQL block! As simple as it looks here.
Give it a try by change the l_number value to 10 at the declaration level and see yourself. Happy coding!