5-1.例外処理の基本

2020-01-11

・例外処理とは

　例外処理とは、仕様で想定していないデータが連携された場合や誤った環境設定が行われていた場合などに行う処理のことです。
　大抵の場合は、その例外となった処理中のデータ内容をログに出力します。
　場合によってはその時点で処理を強制終了させるかもしれません。

・例外処理でやってはいけないこと

　ここで一番やってはいけないことは、例外データを処理したにも関わらず、何もせずにそのまま処理を続行してしまうことです。
　例外はデータに起因するとは限りません。
　環境の設定や、システム障害に起因して発生する場合もあります。
　その場合も、プログラム中で障害を検知し、障害の内容に応じた処理を行う必要があります。
　そしてそのためには、どのプログラムにおいても記述されるべきです。

・例外処理ですべきこと

　必ずすべきことはどのような問題が発生したのかを、何らかの形で出力することです。
　この出力結果は、運用担当、もしくはエンドユーザーが見て、対応を判断するための重要な情報になります。
　そのためには、最低限、次の内容が出力されているべきです。

• エラーの内容
• エラーの発生箇所

　あらかじめ想定された例外であれば、その対処方法も出力されているべきです。
（「○○区分の入力値が不正です(ZZZ)。」
　→「○○区分の入力値が不正です(ZZZ)。○○区分は1桁の英数字で入力してください」）

・例外処理のコーディング

　例外処理の記述は、各々のプログラマに任されるべきではなく、システムとして統一された内容であるべきです。
　それではPL/SQLの例外処理はどのように統一されるべきでしょうか。

　まず検討すべきポイントは、例外処理の結果をどのように呼び出し元に戻すべきかです。
　個々のプロシージャで発生した例外をそのプロシージャ内で処理するのか、例外処理に必要な情報だけを取得して呼出元のプロシージャでまとめて例外処理を行うのか、またどこからどこまでを共通化するのか、定数を使うなどコーディング上のルールも必要になるかもしれません。
　これはプログラムの記述全般に関わる問題であり、後から変更しようとした場合、システム全体への影響を及ぼしますので、設計と並行で検討を進め、コーディングが開始されることには方針が決定されている必要があります。

・不十分な例外処理のサンプルコード

　少し長くなりますが、サンプルとして以下のような処理を記述しました。
　データを取得し、取得したデータを別のテーブルにINSERTするだけのシンプルな処理です。
　例外処理は、OTHERSに記述されたシステム的な例外をRAISEし、上位に戻すだけの記述です。
　そのままではエラーになりませんので、salを100倍してエラーが発生するようにします。
—
事前に次のDDLを実行してテーブルを作成してください。

CREATE TABLE emp_sample
AS
SELECT e.empno, e.ename, e.deptno, d.dname, e.sal, e.comm
FROM   emp e INNER JOIN dept d ON (e.deptno = d.deptno)
WHERE 1=0;

以下がサンプルコードになります。

CREATE OR REPLACE PACKAGE expt_sample
IS
  PROCEDURE main(iv_dept IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY expt_sample
IS
  CURSOR c_emp(iv_dept IN VARCHAR2)
  IS
  -- salを100倍して、オーバーフローが発生するSQL
  SELECT e.empno, e.ename, e.deptno, d.dname, e.sal*100 AS sal, e.comm
  FROM   emp e INNER JOIN dept d ON (e.deptno = d.deptno)
  WHERE  e.deptno = iv_dept;
--
  -- INSERT文を発行するプロシージャ
  PROCEDURE ins(ir_emp IN c_emp%ROWTYPE)
  IS
  BEGIN
    INSERT INTO emp_sample
    VALUES(ir_emp.empno, ir_emp.ename, ir_emp.deptno, ir_emp.dname, ir_emp.sal, ir_emp.comm);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      RAISE;
  END ins;
--
  -- カーソルでデータを取得するプロシージャ
  PROCEDURE sel(iv_dept IN VARCHAR2)
  IS
    r_emp c_emp%ROWTYPE;
  BEGIN
    FOR r_emp IN c_emp(iv_dept)
    LOOP
      ins(r_emp);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      RAISE;
  END sel;
--
  -- mainプロシージャ
  PROCEDURE main(iv_dept IN VARCHAR2)
  IS
  BEGIN
    sel(iv_dept);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      RAISE;
  END main;
END expt_sample;
/

実行すると、「ORA-01438: この列に許容される指定精度より大きな値です」というエラーが発生しますが、それでは、ソースコードのどこを修正すれば良いでしょうか。
このくらいのソースであれば、ORA-01438はINSERT文の発行時に発生したことは予測できるかもしれませんが、予測に過ぎませんし、より複雑なコードが記述されている場合にはエラー箇所を明確に特定することは困難かもしれません。

・エラーの発生箇所を出力する例外処理のサンプルコード

エラーメッセージの出力は、文言だけではなく、どこでエラーが発生したのかがわかる情報を出力すべきです。
旧来(Oracle11gまで)の記述であれば、下記のように定数を保持し、パッケージ名やプロシージャ名をログ出力していました。

CREATE OR REPLACE PACKAGE BODY expt_sample
IS
  cv_package_name CONSTANT VARCHAR2(30) := 'expt_sample';  -- パッケージ名を定義(固定値)
  -- salを100倍して、オーバーフローが発生するSQL
  CURSOR c_emp(iv_dept IN VARCHAR2)
  IS
  SELECT e.empno, e.ename, e.deptno, d.dname, e.sal*100 AS sal, e.comm
  FROM   emp e INNER JOIN dept d ON (e.deptno = d.deptno)
  WHERE  e.deptno = iv_dept;
--
  -- INSERT文を発行するプロシージャ
  PROCEDURE ins(ir_emp IN c_emp%ROWTYPE)
  IS
    cv_prog_name CONSTANT VARCHAR2(30) := 'ins';    -- プロシージャ名を定義(固定値)
  BEGIN
    INSERT INTO emp_sample
    VALUES(ir_emp.empno, ir_emp.ename, ir_emp.deptno, ir_emp.dname, ir_emp.sal, ir_emp.comm);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(cv_package_name || '.' || cv_prog_name || ':' || SQLERRM);
      RAISE;
  END ins;
--
  -- カーソルでデータを取得するプロシージャ
  PROCEDURE sel(iv_dept IN VARCHAR2)
  IS
    r_emp c_emp%ROWTYPE;
    cv_prog_name CONSTANT VARCHAR2(30) := 'sel';    -- プロシージャ名を定義(固定値)
  BEGIN
    FOR r_emp IN c_emp(iv_dept)
    LOOP
      ins(r_emp);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(cv_package_name || '.' || cv_prog_name || ':' || SQLERRM);
      RAISE;
  END sel;
--
  -- mainプロシージャ
  PROCEDURE main(iv_dept IN VARCHAR2)
  IS
    cv_prog_name CONSTANT VARCHAR2(30) := 'main';    -- プロシージャ名を定義(固定値)
  BEGIN
    sel(iv_dept);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(cv_package_name || '.' || cv_prog_name || ':' || SQLERRM);
      RAISE;
  END main;
END expt_sample;
/

以下のようなログが出力されます。

----------------------------------------
expt_sample.ins:ORA-01438: この列に許容される指定精度より大きな値です
expt_sample.sel:ORA-01438: この列に許容される指定精度より大きな値です
expt_sample.main:ORA-01438: この列に許容される指定精度より大きな値です

このように記述することで、最初にinsプロシージャでエラーが発生したことがわかります。
ただ、この方法ではパッケージ名とプロシージャ名を表す定数を都度定義する必要があり、その出力処理も冗長になりがちでした。
（これも、例がシンプルなコードなので記述を省きましたが、実際のコーディングではステップ番号のような情報も出力する必要があります）

・CALL_STACKを使用した例外処理

Oracle12cから拡張されたutl_call_stackパッケージでは、このような宣言をすることなく、同じ内容を出力することができます。
各プロシージャのutl_call_stack呼び出しは共通プロシージャ化してあります。
実際の開発における実装は、更に共通化されるべきでしょう。

CREATE OR REPLACE PACKAGE BODY expt_sample
IS
--  cv_package_name CONSTANT VARCHAR2(30) := 'expt_sample';
  CURSOR c_emp(iv_dept IN VARCHAR2)
  IS
  SELECT e.empno, e.ename, e.deptno, d.dname, e.sal*100 AS sal, e.comm
  FROM   emp e INNER JOIN dept d ON (e.deptno = d.deptno)
  WHERE  e.deptno = iv_dept;
--
  -- 例外処理の共通プロシージャ
  PROCEDURE common_err(err_dpth IN PLS_INTEGER, bt_dpth IN PLS_INTEGER, subprg_unit IN VARCHAR2)
  IS
    ln_errdpt PLS_INTEGER := 0;
  BEGIN
    dbms_output.put_line(
         RPAD(subprg_unit, 30)
      || RPAD(TO_CHAR(utl_call_stack.backtrace_line(bt_dpth), '99'), 8)
      || RPAD(utl_call_stack.error_number(err_dpth), 6)
      || utl_call_stack.error_msg(err_dpth)
      );
  END common_err;
--
  PROCEDURE ins(ir_emp IN c_emp%ROWTYPE)
  IS
--    cv_prog_name CONSTANT VARCHAR2(30) := 'ins';
  BEGIN
    INSERT INTO emp_sample
    VALUES(ir_emp.empno, ir_emp.ename, ir_emp.deptno, ir_emp.dname, ir_emp.sal, ir_emp.comm);
  EXCEPTION
    WHEN OTHERS THEN
--      dbms_output.put_line(cv_package_name || '.' || cv_prog_name || ':' || SQLERRM);
      dbms_output.put_line('SUBPROGRAM/BT_LINE/ERR_NUM/ERR_MSG');
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END ins;
--
  PROCEDURE sel(iv_dept IN VARCHAR2)
  IS
    r_emp c_emp%ROWTYPE;
--    cv_prog_name CONSTANT VARCHAR2(30) := 'sel';
  BEGIN
    FOR r_emp IN c_emp(iv_dept)
    LOOP
      ins(r_emp);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END sel;
--
  PROCEDURE main(iv_dept IN VARCHAR2)
  IS
--    cv_prog_name CONSTANT VARCHAR2(30) := 'main';
  BEGIN
    sel(iv_dept);
  EXCEPTION
    WHEN OTHERS THEN
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END main;
END expt_sample;
/

エラー内容の出力結果は、次のようになります。

BT_DEPTH/ERR_NUM/BT_LINE/SUBPROGRAM/ERR_MSG
1    1438   28     EXPT_SAMPLE.INS               この列に許容される指定精度より大きな値です
2    1438   36     EXPT_SAMPLE.SEL               この列に許容される指定精度より大きな値です
2    1438   52     EXPT_SAMPLE.MAIN              この列に許容される指定精度より大きな値です

定数の宣言等を行うことなく、Oracleが提供するパッケージutl_call_stackの機能で次のような情報を出力することができます。

ここでは、エラーが発生したプロシージャ、発生箇所の行番号、エラーメッセージ番号、エラーメッセージ文字列を出力しています。
他にもいくつかの情報を出力することができます。
出力可能な情報は下記リファレンスで確認することができます。

Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1)
https://docs.oracle.com/cd/E57425_01/121/ARPLS/u_call_stack.htm

・チェックロジックで処理を続行する場合の例外処理

それでは次は、チェックロジックを入れてみましょう。
　salの値が1000を超える場合は、例外「expt_sal_over_range」を発生させ、警告メッセージを出力して処理を続行します。
　insプロシージャ内のINSERT文を発行する前に、IF文でsalの値が1000を超えたら例外「expt_sal_over_range」に飛ばすよう、制御を追加し、それに合わせた例外処理も追加します。

CREATE OR REPLACE PACKAGE BODY expt_sample
IS
--  cv_package_name CONSTANT VARCHAR2(30) := 'expt_sample';
  expt_sal_over_range EXCEPTION;  --例外の定義
--
  CURSOR c_emp(iv_dept IN VARCHAR2)
  IS
  SELECT e.empno, e.ename, e.deptno, d.dname, e.sal*100 AS sal, e.comm
  FROM   emp e INNER JOIN dept d ON (e.deptno = d.deptno)
  WHERE  e.deptno = iv_dept;
--
  -- 例外処理の共通プロシージャ
  PROCEDURE common_err(err_dpth IN PLS_INTEGER, bt_dpth IN PLS_INTEGER, subprg_unit IN VARCHAR2)
  IS
    ln_errdpt PLS_INTEGER := 0;
  BEGIN
    dbms_output.put_line(
         RPAD(subprg_unit, 30)
      || RPAD(TO_CHAR(utl_call_stack.backtrace_line(bt_dpth), '99'), 8)
      || RPAD(utl_call_stack.error_number(err_dpth), 6)
      || utl_call_stack.error_msg(err_dpth)
      );
  END common_err;
--
  PROCEDURE ins(ir_emp IN c_emp%ROWTYPE)
  IS
--    cv_prog_name CONSTANT VARCHAR2(30) := 'ins';
  BEGIN
    -- salの値チェック
    IF ir_emp.sal > 1000 THEN
      RAISE expt_sal_over_range;
    END IF;
--
    -- 問題が無ければINSERT
    INSERT INTO emp_sample
    VALUES(ir_emp.empno, ir_emp.ename, ir_emp.deptno, ir_emp.dname, ir_emp.sal, ir_emp.comm);
  EXCEPTION
    -- salの金額チェックエラー。文言出力して処理継続
    WHEN expt_sal_over_range THEN
      dbms_output.put_line('SAL value over limited value(' || ir_emp.empno || ' / ' || ir_emp.sal || ')');
    WHEN OTHERS THEN
--      dbms_output.put_line(cv_package_name || '.' || cv_prog_name || ':' || SQLERRM);
      dbms_output.put_line('SUBPROGRAM/BT_LINE/ERR_NUM/ERR_MSG');
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END ins;
--
  PROCEDURE sel(iv_dept IN VARCHAR2)
  IS
    r_emp c_emp%ROWTYPE;
--    cv_prog_name CONSTANT VARCHAR2(30) := 'sel';
  BEGIN
    FOR r_emp IN c_emp(iv_dept)
    LOOP
      ins(r_emp);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END sel;
--
  PROCEDURE main(iv_dept IN VARCHAR2)
  IS
--    cv_prog_name CONSTANT VARCHAR2(30) := 'main';
  BEGIN
    sel(iv_dept);
  EXCEPTION
    WHEN OTHERS THEN
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END main;
END expt_sample;
/

次のような警告メッセージが出力されます。

SAL value over limited value(7566 / 297500)
SAL value over limited value(7788 / 300000)
SAL value over limited value(7902 / 300000)
SAL value over limited value(7369 / 80000)
SAL value over limited value(7876 / 110000)

ここでは、条件に該当したら例外「expt_sal_over_range」へ飛ばし、エラーメッセージ出力後は
RAISEはせずに、制御を呼び出し元に戻します。
例外に飛ばしているため、後続のINSERT文はスキップされます。

・チェックロジックで処理を強制終了する場合の例外処理

　それでは続いて、salが1000を超えたらそこで処理を異常終了させる場合を考えましょう。
　salの値が1000を越えた場合は例外「expt_sal_over_range」を発生させるようにロジックを追加します。
　プロシージャinsで例外をハンドリングしたら、そのままRAISEし、上位プロシージャのEXCEPTIONセクションへ処理を飛ばします。

CREATE OR REPLACE PACKAGE BODY expt_sample
IS
--  cv_package_name CONSTANT VARCHAR2(30) := 'expt_sample';
  expt_sal_over_range EXCEPTION;  --例外の定義
--
  CURSOR c_emp(iv_dept IN VARCHAR2)
  IS
  SELECT e.empno, e.ename, e.deptno, d.dname, e.sal*100 AS sal, e.comm
  FROM   emp e INNER JOIN dept d ON (e.deptno = d.deptno)
  WHERE  e.deptno = iv_dept;
--
  -- 例外処理の共通プロシージャ
  PROCEDURE common_err(err_dpth IN PLS_INTEGER, bt_dpth IN PLS_INTEGER, subprg_unit IN VARCHAR2)
  IS
    ln_errdpt PLS_INTEGER := 0;
  BEGIN
    dbms_output.put_line(
         RPAD(subprg_unit, 30)
      || RPAD(TO_CHAR(utl_call_stack.backtrace_line(bt_dpth), '99'), 8)
      || RPAD(utl_call_stack.error_number(err_dpth), 6)
      || utl_call_stack.error_msg(err_dpth)
      );
  END common_err;
--
  PROCEDURE ins(ir_emp IN c_emp%ROWTYPE)
  IS
--    cv_prog_name CONSTANT VARCHAR2(30) := 'ins';
  BEGIN
    -- salの値チェック
    IF ir_emp.sal > 1000 THEN
      RAISE expt_sal_over_range;
    END IF;
--
    -- 問題が無ければINSERT
    INSERT INTO emp_sample
    VALUES(ir_emp.empno, ir_emp.ename, ir_emp.deptno, ir_emp.dname, ir_emp.sal, ir_emp.comm);
  EXCEPTION
    -- salの金額チェックエラー。文言出力して処理継続
    WHEN expt_sal_over_range THEN
      dbms_output.put_line('SAL value over limited value(' || ir_emp.empno || ' / ' || ir_emp.sal || ')');
      RAISE;
    WHEN OTHERS THEN
--      dbms_output.put_line(cv_package_name || '.' || cv_prog_name || ':' || SQLERRM);
      dbms_output.put_line('SUBPROGRAM/BT_LINE/ERR_NUM/ERR_MSG');
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END ins;
--
  PROCEDURE sel(iv_dept IN VARCHAR2)
  IS
    r_emp c_emp%ROWTYPE;
--    cv_prog_name CONSTANT VARCHAR2(30) := 'sel';
  BEGIN
    FOR r_emp IN c_emp(iv_dept)
    LOOP
      ins(r_emp);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END sel;
--
  PROCEDURE main(iv_dept IN VARCHAR2)
  IS
--    cv_prog_name CONSTANT VARCHAR2(30) := 'main';
  BEGIN
    sel(iv_dept);
  EXCEPTION
    WHEN OTHERS THEN
      common_err(utl_call_stack.error_depth, utl_call_stack.backtrace_depth, 
      utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(utl_call_stack.error_depth)));
      RAISE;
  END main;
END expt_sample;
/

実行すると、次のように出力されます。

SAL value over limited value(7566 / 297500)
EXPT_SAMPLE.SEL                41     6510  PL/SQL: ユーザー定義の例外が発生しましたが、処理されませんでした
EXPT_SAMPLE.MAIN               63     6510  PL/SQL: ユーザー定義の例外が発生しましたが、処理されませんでした

プロシージャselとmainの例外処理ではSQLERRMを出力しているため、
今回定義した「expt_sal_over_range」に該当するメッセージが存在
しないため、上記のような出力結果となります。

—————————————-
かつては、同じエラーを複数回出力させないために制御が必要だったのですが、utl_call_stackの拡張のおかげでシンプルに制御できるようになりました。

FORALL文をSAVE EXCEPTIONS句とともに使用する場合以外はこの方法が望ましいでしょう。
SAVE EXCEPTIONS句を使用する場合は、これまで通りSQLERRMを使用する必要があります。