Oracle PL/SQL - Exit begin end block if condition is not met

Viewed 1656

Is it possible to exit/skip from a begin end block if a condition is not met?

Example:

DECLARE
    my_var BOOLEAN := TRUE;

BEGIN
    IF my_var THEN
        EXIT_BEGIN_END_HERE;  -- Exits the block but continue execution after it!
    END IF;

    -- Other stuff happens here. Won't be executed if IF above is true
    INSERT INTO asdf
    VALUES ('asdf', 'asdf');
END;

-- Other stuff happens here
3 Answers

Use GOTO with a label:

BEGIN
  DECLARE
    my_var BOOLEAN := TRUE;
  BEGIN
    IF my_var THEN
      GOTO skip_insert;
    END IF;

    -- Other stuff happens here. Won't be executed if IF above is true
    DBMS_OUTPUT.PUT_LINE( 'This should be skipped' );
  END;
  <<skip_insert>>
    DBMS_OUTPUT.PUT_LINE( 'Continue from here.' );
END;
/

Or use IF ... THEN ... ELSE ... END IF:

DECLARE
  my_var BOOLEAN := TRUE;
BEGIN
  IF my_var THEN
    NULL
  ELSE
    -- Other stuff happens here. Won't be executed if IF above is true
    DBMS_OUTPUT.PUT_LINE( 'This should be skipped' );
  END IF;

  DBMS_OUTPUT.PUT_LINE( 'Continue from here.' );
END;
/

Which both output:

Continue from here.

db<>fiddle here

I think the following is a better example (and solution) that illustrates what you are trying to achieve.

DECLARE
    my_var BOOLEAN := TRUE;
BEGIN
    BEGIN
        IF my_var THEN
            RAISE PROGRAM_ERROR;
        END IF;
        -- Other stuff happens here. Won't be executed if IF above is true
        DBMS_OUTPUT.PUT_LINE('Not here!');
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
    -- Other stuff happens here
    DBMS_OUTPUT.PUT_LINE('Continuing.');
END;

You only want to exit the inner BEGIN-END, right? So you can use the RAISE statement.
If you run the above code, it will display Continuing.

If one wants to exit from the outermost block then RETURN can be used:

DECLARE
    my_var BOOLEAN := TRUE;

BEGIN
    IF my_var THEN
        RETURN;  -- Exits the block but continue execution after it!
    END IF;

    -- Other stuff happens here. Won't be executed if IF above is true
    INSERT INTO asdf
    VALUES ('asdf', 'asdf');
END;

-- Other stuff happens here

Note that as pointed by @MT0, since this exits from the outermost block then the INSERT INTO asdf bellow won't be executed:

DECLARE
    my_var BOOLEAN := TRUE;

BEGIN
    BEGIN
        IF my_var THEN
            RETURN;  -- Exits the outermost block but continue execution after it!
        END IF;

        INSERT INTO qwerty
        VALUES ('qwerty', 'qwerty');
    END;

    -- Other stuff happens here. Won't be executed if IF above is true
    INSERT INTO asdf
    VALUES ('asdf', 'asdf');
END;

-- Other stuff happens here

RETURN can also be used inside procedures.

See similar question: Abort a PL/SQL program

Related