【PL/SQL】例外処理完全ガイド｜21種事前定義例外・WHEN OTHERS正しい使い方・BACKTRACE・自律トランザクション・実務パターン

例外発生
  ↓（同じブロックにEXCEPTION句があれば）
EXCEPTION句で WHEN 例外名 THEN をマッチング
  ↓（マッチしたらブロック終了、しなければ）
呼び出し元のブロックへ伝播
  ↓
親ブロックのEXCEPTION句でマッチング
  ↓（すべて不発なら）
クライアント側（SQL*Plus/JDBC/アプリ）へエラー返却

重要：マッチして処理した後は「処理済み」とみなされ、
     そのブロックは正常終了扱いで親へ戻る（伝播しない）

DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM users WHERE id = 9999;

  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'ユーザーが見つかりません');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('データなし: ' || SQLERRM);
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('複数行検出: ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('予期せぬエラー: ' || SQLCODE || ' / ' || SQLERRM);
    RAISE;    -- 握りつぶさず親へ伝播
END;
/

DECLARE
  v_name users.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM users WHERE id = p_id;
  -- 通常処理
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'ID=' || p_id || ' のユーザーが見つかりません');
  WHEN TOO_MANY_ROWS THEN
    RAISE_APPLICATION_ERROR(-20002, 'ID=' || p_id || ' で複数ユーザー検出 (データ不整合)');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('予期せぬエラー: ' || SQLCODE || ' / ' || SQLERRM);
    RAISE;
END;

DECLARE
  e_invalid_amount EXCEPTION;
  v_amount NUMBER := -100;
BEGIN
  IF v_amount < 0 THEN
    RAISE e_invalid_amount;
  END IF;
EXCEPTION
  WHEN e_invalid_amount THEN
    DBMS_OUTPUT.PUT_LINE('金額がマイナス');
END;

-- 特定のORAエラーを例外名で扱えるようにする
DECLARE
  -- ORA-02292（外部キー参照整合性違反）に名前付け
  e_child_record_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_child_record_found, -2292);

  -- ORA-02291（親キー不在）
  e_parent_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_parent_not_found, -2291);

  -- ORA-00054（リソースビジー）
  e_resource_busy EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
BEGIN
  DELETE FROM departments WHERE id = p_dept_id;
EXCEPTION
  WHEN e_child_record_found THEN
    RAISE_APPLICATION_ERROR(-20101, '子レコードが存在するため削除できません');
  WHEN e_resource_busy THEN
    RAISE_APPLICATION_ERROR(-20102, 'ロック待ちでタイムアウト、少し待って再試行してください');
END;

BEGIN
  -- ビジネスルール違反を例外として通知
  IF :new.salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, '給与はマイナスにできません');
  END IF;
  
  -- エラー番号は -20000〜-20999 の範囲（ユーザー定義専用）
  -- メッセージは2048バイトまで
  -- 第3引数TRUEでエラースタック保持
  IF :new.department_id NOT IN (10, 20, 30) THEN
    RAISE_APPLICATION_ERROR(
      -20002,
      '無効な部門ID: ' || :new.department_id,
      TRUE    -- エラースタックに積む
    );
  END IF;
END;

EXCEPTION
  WHEN OTHERS THEN
    -- ORAエラー番号（負数）、ユーザー定義は-20000〜-20999
    DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);

    -- エラーメッセージ
    DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

    -- エラースタック（複数エラーを全て含む）
    DBMS_OUTPUT.PUT_LINE('STACK:'  || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_STACK);

    -- エラー発生行まで辿れるトレースバック（重要！）
    DBMS_OUTPUT.PUT_LINE('TRACE:'  || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

    -- 呼び出しスタック（12cR1以降）
    DBMS_OUTPUT.PUT_LINE('CALL:'  || CHR(10) || DBMS_UTILITY.FORMAT_CALL_STACK);
END;

-- コード
CREATE OR REPLACE PROCEDURE proc_a IS
BEGIN
  proc_b();       -- Line 3
END;
/

CREATE OR REPLACE PROCEDURE proc_b IS
  v NUMBER;
BEGIN
  v := 1/0;       -- Line 4: ZERO_DIVIDE
END;
/

-- 呼び出し側
BEGIN
  proc_a();
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

-- 出力
-- ORA-06512: at "SCHEMA.PROC_B", line 4
-- ORA-06512: at "SCHEMA.PROC_A", line 3
-- ORA-06512: at line 2
-- ↑どの行でエラーが起きたか完全に追跡できる

BEGIN
  -- 重い処理
  process_data();
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- 既知の例外はビジネスロジックで処理
    log_and_continue();

  WHEN OTHERS THEN
    -- 予期しないエラーはログを残してから再送出
    INSERT INTO error_log(error_time, error_code, error_msg, error_backtrace)
    VALUES (SYSDATE, SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    COMMIT;
    RAISE;    -- 重要：握り潰さない
END;

-- ❌ 最悪：何もしない
EXCEPTION
  WHEN OTHERS THEN
    NULL;    -- 例外窒息（Smothering）
END;

-- ❌ ログを出すだけで握り潰す
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('エラー: ' || SQLERRM);
    -- ↑ DBMS_OUTPUTは本番では消えることが多い
    -- 呼び出し元には何も伝わらず、正常終了として処理される
END;

-- ❌ 部分的な情報しか残さない
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO error_log(msg) VALUES (SQLERRM);
    -- ↑ SQLCODE/BACKTRACE無し → どの行でエラーか不明
END;

EXCEPTION
  -- 既知の例外は個別処理
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'データが存在しません');

  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20002, '既に登録済みです');

  -- 未知の例外は完全にログして再送出
  WHEN OTHERS THEN
    pkg_error_log.log_error(
      p_code      => SQLCODE,
      p_message   => SQLERRM,
      p_backtrace => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
      p_context   => 'proc_xxx(id=' || p_id || ')'
    );
    RAISE;    -- ← 最重要：呼び出し元へ伝える
END;

CREATE OR REPLACE PROCEDURE log_error(
  p_code      IN NUMBER,
  p_message   IN VARCHAR2,
  p_backtrace IN VARCHAR2,
  p_context   IN VARCHAR2 DEFAULT NULL
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- 重要：独立したトランザクション
BEGIN
  INSERT INTO error_log(
    error_time, error_code, error_msg,
    error_backtrace, context, username, client_info
  ) VALUES (
    SYSTIMESTAMP, p_code, p_message,
    p_backtrace, p_context, USER, SYS_CONTEXT('USERENV', 'CLIENT_INFO')
  );
  COMMIT;    -- 独立COMMITで確定（呼び出し元がROLLBACKしても残る）
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    -- ログ保存すら失敗する最悪ケース（DB障害等）
    -- ここでは握り潰さず呼び出し元に伝える
    RAISE;
END;
/

BEGIN
  UPDATE orders SET status = 'processed' WHERE id = p_id;
  process_payment(p_id);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    log_error(
      p_code      => SQLCODE,
      p_message   => SQLERRM,
      p_backtrace => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
      p_context   => 'process_order(' || p_id || ')'
    );
    ROLLBACK;    -- ビジネスデータはロールバック
    -- ↑ ログは自律トランザクションで既にCOMMIT済みなので残る
    RAISE;
END;

DECLARE
  v_success_count NUMBER := 0;
  v_error_count   NUMBER := 0;
BEGIN
  FOR rec IN (SELECT id, amount FROM orders_queue) LOOP
    SAVEPOINT sp_order;
    BEGIN
      UPDATE accounts SET balance = balance - rec.amount WHERE id = rec.account_id;
      INSERT INTO transactions(order_id, amount) VALUES (rec.id, rec.amount);
      v_success_count := v_success_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK TO sp_order;    -- このレコードだけロールバック
        v_error_count := v_error_count + 1;
        log_error(
          p_code     => SQLCODE,
          p_message  => SQLERRM,
          p_context  => 'order_id=' || rec.id
        );
    END;
  END LOOP;

  COMMIT;    -- 成功分だけ確定
  DBMS_OUTPUT.PUT_LINE('成功: ' || v_success_count || ', 失敗: ' || v_error_count);
END;

DECLARE
  TYPE t_amounts IS TABLE OF NUMBER;
  v_ids    t_amounts := t_amounts(1, 2, 3, 4, 5);
  v_amounts t_amounts := t_amounts(100, -50, 200, 300, NULL);
  --                                         ↑負数      ↑NULL

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
    UPDATE accounts SET balance = balance + v_amounts(i)
    WHERE id = v_ids(i);

  DBMS_OUTPUT.PUT_LINE('全件成功');
EXCEPTION
  WHEN e_bulk_errors THEN
    -- SQL%BULK_EXCEPTIONSで失敗レコードを個別処理
    DBMS_OUTPUT.PUT_LINE('エラー件数: ' || SQL%BULK_EXCEPTIONS.COUNT);
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
        ' → ORA-' || LPAD(SQL%BULK_EXCEPTIONS(i).ERROR_CODE, 5, '0') ||
        ': ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
      );
    END LOOP;
    COMMIT;    -- 成功分だけ確定
END;

CREATE OR REPLACE PACKAGE pkg_errors IS
  -- ユーザー／認証系 -20001〜-20099
  ERR_USER_NOT_FOUND        CONSTANT NUMBER := -20001;
  ERR_DUPLICATE_EMAIL       CONSTANT NUMBER := -20002;
  ERR_INVALID_PASSWORD      CONSTANT NUMBER := -20003;

  -- 注文／決済系 -20100〜-20199
  ERR_ORDER_NOT_FOUND       CONSTANT NUMBER := -20100;
  ERR_INSUFFICIENT_BALANCE  CONSTANT NUMBER := -20101;
  ERR_INVALID_AMOUNT        CONSTANT NUMBER := -20102;

  -- バリデーション系 -20200〜-20299
  ERR_REQUIRED_FIELD        CONSTANT NUMBER := -20200;
  ERR_INVALID_FORMAT        CONSTANT NUMBER := -20201;

  -- ヘルパー
  PROCEDURE raise_error(
    p_code IN NUMBER,
    p_msg  IN VARCHAR2
  );
END pkg_errors;
/

CREATE OR REPLACE PACKAGE BODY pkg_errors IS
  PROCEDURE raise_error(
    p_code IN NUMBER,
    p_msg  IN VARCHAR2
  ) IS
  BEGIN
    RAISE_APPLICATION_ERROR(p_code, p_msg, TRUE);
  END;
END pkg_errors;
/

PROCEDURE create_user(p_email VARCHAR2, p_password VARCHAR2) IS
  v_count NUMBER;
BEGIN
  IF p_email IS NULL THEN
    pkg_errors.raise_error(pkg_errors.ERR_REQUIRED_FIELD, 'メールアドレスは必須');
  END IF;

  IF LENGTH(p_password) < 8 THEN
    pkg_errors.raise_error(pkg_errors.ERR_INVALID_PASSWORD, 'パスワードは8文字以上');
  END IF;

  SELECT COUNT(*) INTO v_count FROM users WHERE email = p_email;
  IF v_count > 0 THEN
    pkg_errors.raise_error(pkg_errors.ERR_DUPLICATE_EMAIL, '既に登録済み: ' || p_email);
  END IF;

  INSERT INTO users(email, password_hash) VALUES (p_email, hash_pw(p_password));
END;

FUNCTION get_user_name(p_id NUMBER) RETURN VARCHAR2 IS
  v_name users.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM users WHERE id = p_id;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;    -- 0件ならNULLを返す仕様
  -- TOO_MANY_ROWSは発生させない（主キー検索だから）
END;

PROCEDURE upsert_user(p_email VARCHAR2, p_name VARCHAR2) IS
BEGIN
  INSERT INTO users(email, name) VALUES (p_email, p_name);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- 既存なら更新
    UPDATE users SET name = p_name WHERE email = p_email;
END;
-- MERGE文使う方が性能は良いが、単発INSERT/UPDATEならこのパターンもOK

BEGIN
  UPDATE orders SET status = 'processing' WHERE id = p_id;
  call_external_api(p_id);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 'process_order');
    ROLLBACK;    -- 不整合を残さない
    RAISE;       -- 呼び出し元（アプリ）に通知
END;

DECLARE
  v_cursor SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR 'SELECT ... ';
  -- 処理
  CLOSE v_cursor;
EXCEPTION
  WHEN OTHERS THEN
    IF v_cursor%ISOPEN THEN
      CLOSE v_cursor;
    END IF;
    RAISE;
END;

DECLARE
  v_errors VARCHAR2(4000);
BEGIN
  IF p_email IS NULL THEN v_errors := v_errors || 'メール必須; '; END IF;
  IF p_age IS NULL OR p_age < 18 THEN v_errors := v_errors || '年齢要件NG; '; END IF;
  IF LENGTH(p_password) < 8 THEN v_errors := v_errors || 'パスワード桁数NG; '; END IF;

  IF v_errors IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20200, '入力エラー: ' || v_errors);
  END IF;

  INSERT INTO users VALUES (p_email, p_age, hash_pw(p_password));
END;

CREATE OR REPLACE TRIGGER trg_validate_order
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
  IF :NEW.amount IS NULL OR :NEW.amount <= 0 THEN
    RAISE_APPLICATION_ERROR(-20102, '注文金額は正の数');
  END IF;

  IF :NEW.status NOT IN ('pending', 'paid', 'shipped', 'delivered') THEN
    RAISE_APPLICATION_ERROR(-20103, '無効なステータス: ' || :NEW.status);
  END IF;
END;

DECLARE
  e_parent_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_parent_not_found, -2291);
  e_child_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_child_found, -2292);
BEGIN
  INSERT INTO orders(user_id, amount) VALUES (p_user_id, p_amount);
EXCEPTION
  WHEN e_parent_not_found THEN
    RAISE_APPLICATION_ERROR(-20300, 'ユーザーID=' || p_user_id || ' が存在しません');
END;

FOR rec IN (SELECT id, data FROM import_queue) LOOP
  SAVEPOINT sp_item;
  BEGIN
    process_item(rec.id, rec.data);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO sp_item;
      log_error(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
                'import_id=' || rec.id);
  END;
END LOOP;
COMMIT;