Oracle 例外控制(Exception Control)

寫Oracle Stored Procedure時, 也和一般程式開發一樣, 也會遇到邏輯例外而需要控制。

1. Oracle已存在Exception例外處理

DECLARE
  l_row             abc%ROWTYPE;
BEGIN

  BEGIN
    select * INTO l_row from abc where abc01='123@asd';

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.put_line('--> NO_DATA_FOUND err is ' || SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('--> OTHER err is ' || SQLERRM);
  END;
END;

** 則會顯示 "--> NO_DATA_FOUND err is ORA-01403: no data found"
** 如果沒有處理"WHEN NO_DATA_FOUND THEN", 則會跑到"OTHERS"中

1.1 Oracle已存在Exception例外處理(但自定Exception名稱)

DECLARE
  l_row             abc%ROWTYPE;

  my_no_data_found_exp EXCEPTION;
  PRAGMA EXCEPTION_INIT(my_no_data_found_exp, -01403);    --no data found Exception

BEGIN

  BEGIN
    select * INTO l_row from abc where abc01='123@asd';

  EXCEPTION
    WHEN my_no_data_found_exp THEN
      DBMS_OUTPUT.put_line('--> NO_DATA_FOUND err is ' || SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('--> OTHER err is ' || SQLERRM);
  END;
END;

2. 使用者自訂例外處理1

DECLARE
  l_ex_error          EXCEPTION;
BEGIN

  BEGIN
    IF (條件式) THEN
      RAISE l_ex_error;
    END IF;

  EXCEPTION
    WHEN l_ex_error THEN
      DBMS_OUTPUT.put_line('--> l_ex_error err is ' || SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('--> other err is ' || SQLERRM);
  END;
END;

3. 使用者自訂例外處理2

如果希望自訂錯誤, 但統一在EXCEPTION OTHERS中處理, 則需要將Exception綁到error-code(oracle user defined error code range is -20999 to -20000), 再使用RAISE_APPLICATION_ERROR丟出Excpetion即可。

DECLARE
  l_ex_error          EXCEPTION;
  PRAGMA EXCEPTION_INIT(l_ex_error, -20001);
BEGIN

  BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'this is my application error!');

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('--> err is ' || SQLERRM);
  END;
END;