Search code examples
oracle-databaseif-statementplsqlskip

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


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

Solution

  • 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