Oracle PL/SQL之EXCEPTION

Test Code:

DECLARE
BEGIN

<<test0>>
-- most normal way to handle exception.
  DECLARE
    except_test0 EXCEPTION;
  BEGIN
    RAISE except_test0;
  EXCEPTION
    WHEN except_test0 THEN
      dbms_output.put_line('test0 except_test0: SQLCODE=' || SQLCODE ||
                           ', SQLERRM=' || SQLERRM);
    WHEN OTHERS THEN
      dbms_output.put_line('test0 OTHERS: SQLCODE=' || SQLCODE ||
                           ', SQLERRM=' || SQLERRM);
  END;

<<test1>>
-- custom exception error number.
  DECLARE
    except_test1 EXCEPTION;
    -- suggested error number range: -20,NNN.
    PRAGMA EXCEPTION_INIT(except_test1, -20001);
  BEGIN
    RAISE except_test1;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('test1: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
                           SQLERRM);
  END;

<<test2>>
-- custom exception error number and error message.
  BEGIN
    raise_application_error(-20002, 'except test 2');
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -20002
      THEN
        dbms_output.put_line('test2A: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
                             SQLERRM);
        dbms_output.put_line('test2B: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
                             SQLERRM);
      ELSE
        dbms_output.put_line('test2C: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
                             SQLERRM);
      END IF;
  END;

  -- SQLCODE and SQLERRM will be re evaluated after EXCEPTION handled.
  dbms_output.put_line('test2D: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
                       SQLERRM);

<<test3>>
-- custom exception error number and error message, more readable.
  DECLARE
    except_test3 EXCEPTION;
    PRAGMA EXCEPTION_INIT(except_test3, -20001);
  BEGIN
    raise_application_error(-20001, 'except test 3');
  EXCEPTION
    WHEN except_test3 THEN
      dbms_output.put_line('test3 except_test3: SQLCODE=' || SQLCODE ||
                           ', SQLERRM=' || SQLERRM);
    WHEN OTHERS THEN
      dbms_output.put_line('test3 OTHERS: SQLCODE=' || SQLCODE ||
                           ', SQLERRM=' || SQLERRM);
  END;

<<test4>>
-- exception can be re raised.
  BEGIN
    RAISE no_data_found;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('test4: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
                           SQLERRM);
      RAISE;
  END;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('outer: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
                         SQLERRM);
END;

Output:

test0 except_test0: SQLCODE=1, SQLERRM=User-Defined Exception
test1: SQLCODE=-20001, SQLERRM=ORA-20001: 
test2A: SQLCODE=-20002, SQLERRM=ORA-20002: except test 2
test2B: SQLCODE=-20002, SQLERRM=ORA-20002: except test 2
test2D: SQLCODE=0, SQLERRM=ORA-0000: normal, successful completion
test3 except_test3: SQLCODE=-20001, SQLERRM=ORA-20001: except test 3
test4: SQLCODE=100, SQLERRM=ORA-01403: no data found
outer: SQLCODE=100, SQLERRM=ORA-01403: no data found