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!