テクノロジー：PL/SQL

記事一覧へ戻る

PL/SQLの理解と利用に関するシリーズ記事のパート6

完璧なPL/SQLプログラムを記述したとしても、プログラムの実行時に何らかの不具合が起きてエラーが発生する可能性があるばかりか、その可能性は高いと言えます。そのエラーに対してコード内でどう対応して処理するかが、優れたアプリケーションと、ユーザーや開発者にとって問題ばかりを生むアプリケーションの違いであることも多いのです。

この記事では、PL/SQLでのエラー管理の世界を詳しく見ていきます。具体的には、発生する可能性のある例外の種類、例外発生のタイミング、原因、方法、独自の例外を定義する方法、例外が発生したときの処理方法、問題に関する情報をユーザーに通知する方法について取り上げます。

例外の概要

PL/SQLの世界における例外には、内部定義、事前定義、ユーザー定義という3つのカテゴリがあります。

内部定義例外とは、Oracle Databaseプロセスによって内部的に発生する例外のことです。この種の例外には常にエラー・コードが付与されますが、PL/SQLまたは独自のコードで名前を割り当てない限り、名前はありません。内部定義例外の例は、ORA-00060（リソース待ちの間にデッドロックが検出されたときの例外）です。

事前定義例外とは、PL/SQLによって名前が割り当てられた内部定義例外のことです。ほとんどの事前定義例外はSTANDARDパッケージ（PL/SQL言語でよく使用される多くのプログラミング要素を定義している、Oracle Database提供のパッケージ）内に定義されており、もっともよく目にする例外です。例として、ORA-00001が挙げられます。ORA-00001は、PL/SQLでDUP_VAL_ON_INDEXという名前が割り当てられており、一意索引制約違反の場合に発生します。

ユーザー定義例外とは、開発者がプログラム・ユニットの宣言セクションで宣言した例外のことです。ユーザー定義例外は、内部定義例外に関連付ける（つまり、名前のない例外に名前を付ける）か、アプリケーション固有のエラーに関連付けることができます。

すべての例外にはエラー・コードがあり、さらにエラー・メッセージがそのコードに関連付けられます。Oracle Databaseは、例外の処理時にこれらの値を取得するためのファンクションを提供しています（表1を参照）。 

CREATE OR REPLACE PROCEDURE 
process_department (
   department_id_in IN INTEGER)
IS
BEGIN
   IF department_id_in IS NULL
   THEN
      RAISE VALUE_ERROR;
   END IF;

RAISE_APPLICATION_ERROR：RAISE文は例外を発生させ、現在のブロックの継続実行を停止します。また、現在のエラー・コードとエラー・メッセージの設定も行います。ここのエラー・メッセージ（"ORA-06502: PL/SQL: numeric or value error"など）は、Oracle Databaseが提供するもので、通常は一般的な内容です。

CREATE OR REPLACE PROCEDURE 
validate_employee (
   birthdate_in IN DATE)
IS
BEGIN
   IF birthdate_in > 
      ADD_MONTHS (SYSDATE, -12 * 18)
   THEN
      RAISE_APPLICATION_ERROR (-20500
        , 'Employee must be at least 
           18 years old.'); 
   END IF;
END;

独自の例外の定義

独自の例外を定義する（ユーザー定義例外を導入する）理由は2つあります。1つは、Oracle Databaseによって名前が割り当てられていないエラーに名前を付けること、もう1つは、"Balance too low"（残高が少なすぎる）などのアプリケーション固有の例外を定義することです。

独自の例外を定義するには、次のようにEXCEPTIONデータ型を使用します。 

DECLARE
   e_balance_too_low EXCEPTION;

デフォルトでは、この例外に関連付けられるエラー・コードは1で、"User Defined Error"がそのエラー・メッセージです。ただし、EXCEPTION_INITプラグマを使用して、例外に異なるエラー・コードを関連付けることもできます。次のブロックでは、エラー・コード-20,000に対して"Balance too low"エラーを関連付けています。 

CREATE OR REPLACE PROCEDURE 
process_balance (
   balance_in IN NUMBER)
IS
   e_balance_too_low   EXCEPTION;

   PRAGMA EXCEPTION_INIT (
      e_balance_too_low, -20000);
BEGIN
   IF balance_in < 1000
   THEN
      RAISE e_balance_too_low;
   END IF;
END;

例外の処理

Oracle Databaseで内部例外または事前定義例外が発生する場合があります。また、開発者がアプリケーション用に定義した例外を明示的に発生させることもできます。この次に必要なことは、プログラムでその例外を処理する方法を決めることです。

未処理の例外をブロックやサブプログラムの外部に出したくない場合は、その例外を捕捉する例外セクションを追加する必要があります。例外セクションはEXCEPTIONキーワードで始まり、1つ以上のWHEN句により構成されます。WHEN句には1つの例外（名前により指定）、ORで連結した複数の例外、またはすべての例外を指定できます。

次に、WHEN句の例を示します。 

1. NO_DATA_FOUND例外を捕捉します。この例外は通常、SELECT-INTO文を実行して1行も見つからなかった場合に発生します。  

      WHEN NO_DATA_FOUND
   
   			THEN     

2. 事前定義例外のNO_DATA_FOUNDとDUP_VAL_ON_INDEXのいずれかを捕捉します。  

      WHEN NO_DATA_FOUND OR
   
   			DUP_VAL_ON_INDEX     
   			THEN     

3. すべての例外を捕捉します。 

      WHEN OTHERS
   
   			THEN     

例外セクションには複数のWHEN句を使用できますが、WHEN OTHERS句を使用する場合は最後に記述する必要があります。

1つ以上のWHEN句の定義は非常に簡単ですが、例外セクションの難しいところは、例外を捕捉した後に何をするかを決めることです。一般的に、例外ハンドラのコードでは、次の2つの手順を実行します。 

1. エラーを何らかのログ（通常はデータベース表）に記録する

2. 同じ例外か別の例外を発生させ、未処理のまま外側のブロックに伝播させる 

例外の再発生：エラーに関する情報を単純に記録して、その後は例外を再発生させないことも可能です。しかし、このアプローチには問題があります。それは、アプリケーションがエラーを"飲み込んでいる"ことです。ユーザー（または実行中のスクリプト）では、問題があったことを把握できません。これで十分なシナリオもあるでしょうが、非常にまれです。エラーが発生したほとんどの状況で、ユーザーまたはエラー発生元のコードを実行したジョブに、確実に通知することが望まれます。

Oracle Databaseでは、RAISE文を使用してこの通知を簡単に実行できます。実行可能セクションでRAISEを使用する場合は、発生させる例外を次のように指定する必要があります。  

RAISE NO_DATA_FOUND;

しかし、例外ハンドラの内部では、例外を指定しないでRAISEを使用することもできます。 

RAISE;

この形式の場合、現在の例外が再発生して、例外セクションから外側のブロックに伝播されます。

注意点として、例外セクション以外の場所でRAISEを使用すると、コンパイル時にエラーが発生します。 

PLS-00367: a RAISE statement with 
no exception name must be inside 
an exception handler

エラーの記録：アプリケーションで何らかの問題があり例外が発生したとしましょう。サブプログラムに例外セクションをまったく記述せずに、その例外を未処理のままユーザーにまで伝播させることも可能ではあります。その場合、ユーザーはエラー・コードとメッセージを確認し、その問題をサポート・チームに報告するか、自ら問題の修正を試みます。

しかし、ほとんどの状況で、開発者はエラーがユーザーに伝わる前にエラーに関する情報を記録しておきたいと思うでしょう。そのようにすれば、エラー・コードやエラー・メッセージなどの情報提供をユーザーに頼る必要がなくなります。

エラーを記録する場合は、表1に示す情報を含めることをお勧めします。これらの情報はすべて、Oracle Databaseが提供するファンクションをコールすることで取得できます。これらはすべて、開発者やサポート・チームのメンバーが問題の原因を診断するために役に立つ情報です。さらに、アプリケーション固有のデータ（変数や列の値など）の値を記録することもできます。

エラー情報を表に保存する場合は、例外内に直接、エラー・ログ表のINSERT文を記述することはお勧めしません。代わりに、この挿入を行うプロシージャを作成してコールしてください。このようにログの実装や生成の方法を"隠す"プロセスによって、エラーのロギングがより簡単で生産的なものになります。

これらの利点を理解するため、単純なエラー・ログ表を作成し、例外セクションで使用してみましょう。次のようなエラー・ログ表があるとします。 

CREATE TABLE error_log
(
   ERROR_CODE      INTEGER
 ,  error_message   VARCHAR2 (4000)
 ,  backtrace       CLOB
 ,  callstack       CLOB
 ,  created_on      DATE
 ,  created_by      VARCHAR2 (30)
)

この場合、リスト1のような例外ハンドラを記述することも可能ではあります。

コード・リスト1：ログ表への挿入を行う例外処理セクション 

EXCEPTION
   WHEN OTHERS
   THEN
      DECLARE
         l_code   INTEGER := SQLCODE;
      BEGIN
         INSERT INTO error_log (error_code
                              ,  error_message
                              ,  backtrace
                              ,  callstack
                              ,  created_on
                              ,  created_by)
              VALUES (l_code
                    ,  sys.DBMS_UTILITY.format_error_stack
                    ,  sys.DBMS_UTILITY.format_error_backtrace
                    ,  sys.DBMS_UTILITY.format_call_stack
                    ,  SYSDATE
                    ,  USER);

         RAISE;
      END;

プログラムでどのようなエラーが発生しても、このハンドラはそのエラーを捕捉して、そのエラーに関する非常に有用な大量の情報を表に保存します。

しかし、このような例外ハンドラは絶対に記述しないようにしてください。次のような問題があるからです。 

• コード量が多すぎる：エラー情報を保存するために大量のコードを記述する必要があります。これは、生産性の低下や例外ハンドラの減少につながります（プログラマはこのコードをすべて記述しなければならないと感じず、ハンドラを追加する必要がないと正当化します）。

• エラー・ログがビジネス・トランザクション内に含まれる：行を表に挿入したとして、この表がアプリケーションの"現実の"表（たとえば、人事管理アプリケーションのEmployees表）とは異なることは、開発者には分かります。しかし、Oracle Databaseでその区別はされません。エラーのためにロールバックが実行された場合、ログ表へのINSERTもロールバックされます。

• 不安定なコード：error_log表の構造を変更する必要がある場合に、この変更に対応するためにすべてのINSERT文を変更する必要があります。 

もっと良いアプローチがあります。それは、リスト2のように、代わりにINSERTを実行してくれるプロシージャの中にこの表を"隠す"ことです。

コード・リスト2：ログ表への挿入を行う例外処理プロシージャ  

CREATE OR REPLACE PROCEDURE record_error
IS
   l_code   PLS_INTEGER := SQLCODE;
   l_mesg  VARCHAR2(32767) := SQLERRM;
BEGIN
   INSERT INTO error_log (error_code
                        ,  error_message
                        ,  backtrace
                        ,  callstack
                        ,  created_on
                        ,  created_by)
        VALUES (l_code
              ,  l_mesg 
              ,  sys.DBMS_UTILITY.format_error_backtrace
              ,  sys.DBMS_UTILITY.format_call_stack
              ,  SYSDATE
              ,  USER);

END;

ここでは、INSERT文をプロシージャ内に移動しただけですが、そのような単純な行為が重大な結果をもたらします。ビジネス・トランザクションと一緒にエラー・ログのINSERTがロールバックされる問題を簡単に回避できるようになるのです。必要な作業は、このプロシージャを自律型トランザクションに変更することだけです。そのためには、リスト3に示すように、プラグマ文とCOMMITを追加します。

コード・リスト3：自律型トランザクションとして宣言しCOMMITを追加した例外処理プロシージャ 

CREATE OR REPLACE PROCEDURE record_error
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_code   PLS_INTEGER := SQLCODE;
   l_mesg  VARCHAR2(32767) := SQLERRM; 
BEGIN
   INSERT INTO error_log (error_code
                        ,  error_message
                        ,  backtrace
                        ,  callstack
                        ,  created_on
                        ,  created_by)
        VALUES (l_code
              ,  l_mesg 
              ,  sys.DBMS_UTILITY.format_error_backtrace
              ,  sys.DBMS_UTILITY.format_call_stack
              ,  SYSDATE
              ,  USER);

   COMMIT;
END;

プロシージャを自律型トランザクションとして宣言することで、このプロシージャ内での表に対する変更内容のコミットまたはロールバックを、セッション内の他の変更内容に影響を及ぼさずに実行できます。そのため、エラー・ログに新しい行を保存でき、その後ビジネス・トランザクションがロールバックされてもこの情報は消去されません。

このロギング・プロシージャをスキーマ内に定義することで、次のように簡単かつ迅速に例外ハンドラを記述できます。  

EXCEPTION
   WHEN OTHERS
   THEN
      record_error();
      RAISE;

これで例外ハンドラの記述にかかる時間が大幅に削減され、例外ハンドラの機能もより堅牢になりました。利点ばかりです。

宣言時に発生する例外：ブロックの宣言セクション内で例外が発生した場合、その例外は外側のブロックに伝播されます。つまり、ブロックの例外セクションは、ブロックの実行可能セクションで発生した例外しか捕捉できません。

次のブロックにはWHEN OTHERSハンドラがあります。このWHEN OTHERSハンドラは、ブロック内で発生したすべての例外を捕捉し、単純にエラー・コードを表示するはずです。  

DECLARE
  l_number   NUMBER (1) := 100;
BEGIN
  statement1;
  ...
  statementN;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLCODE);
END;

このブロックを実行すると、Oracle Databaseでl_numberに値100が代入されようとします。しかし、l_numberはNUMBER (1)として宣言されているため、100はこの変数に"収まりません"。その結果、Oracle DatabaseでORA-06502エラーが発生します。このエラーは、PL/SQLではVALUE_ERRORとして事前定義されています。

この例外は変数の宣言の過程で発生するため、例外ハンドラがこのエラーを捕捉することはありません。代わりに、次の未処理の例外が表示されます。  

ORA-06502:PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 2 

以上より、エラーが発生しないことが明白でなければ、宣言セクションで変数に値を代入することは避けてください。代わりに、実行可能セクション内で値を代入できます。そうすれば、例外ハンドラでそのエラーを捕捉して記録できます。  

DECLARE
  l_number   NUMBER (1);
BEGIN
  l_number := 100;

  statement1;
  ...
  statementN;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLCODE);
END;

例外とロールバック

未処理の例外が発生しても、セッション内での未確定の変更内容が自動的にロールバックされるわけではありません。実際は、例外セクション内に明示的にROLLBACK文をコーディングするか、例外が未処理のままホスト環境に伝播されない限り、ロールバックは実行されません。例を見てみましょう。

次の2つのデータ操作言語（DML）の操作を実行するコード・ブロックを記述するとします。 

1. Employees表から、部門20に所属するすべての従業員を削除する

2. 残りのすべての従業員について、現在の給与に200を掛けて昇給する 

これは非常に気前の良い話ですが、salary列の制約はNUMBER(8,2)として定義されています。一部の従業員の給与は、新しい給与がこの制約違反となるほど高い状態です。この制約違反により、Oracle Databaseで"ORA-01438: value larger than specified precision allowed for this column"エラーが発生します。

ここで、SQL*Plusセッションで次のブロックを実行します。  

BEGIN
   DELETE FROM employees
         WHERE department_id = 20;
   UPDATE employees
      SET salary = salary * 200;
EXCEPTION
   WHEN OTHERS
   THEN
      DECLARE
         l_count   PLS_INTEGER;
      BEGIN
         SELECT COUNT (*)
           INTO l_count
           FROM employees
          WHERE department_id = 20;

         DBMS_OUTPUT.put_line (l_count);
         RAISE;
      END;
END;
 

まとめ

PL/SQLは、エラーの捕捉や診断を行う機能や、アプリケーション固有のエラーをユーザーに通知する機能を広範に提供しています。例外セクションを使用すれば、すべての例外処理ロジックを集中させることが簡単になり、例外処理ロジックの管理がより効率的になります。

PL/SQLの基礎に関する次回の記事では、PL/SQLでのレコード・データ型について取り上げ、%ROWTYPEアンカーの使用方法、独自のレコード型の宣言方法と使用方法、レコードレベルの挿入と更新などについて説明します。

再発生がないことに関する警告について
 


Oracle Database 11g Release 1では、コンパイル時の警告サブシステムに非常に便利な次の警告が追加されました。"PLW-6009: handler does not end in RAISE or RAISE_APPLICATION_ERROR."

つまり、外側のブロックにエラーを伝播させず、エラーを"飲み込む"可能性のある例外ハンドラが、コンパイラにより自動的に検出されるようになりました。
 

次に例を示します。 

SQL> ALTER SESSION SET plsql_warnings = 'ENABLE:6009'
  2  /
Session altered.

SQL> CREATE OR REPLACE FUNCTION plw6009
  2     RETURN VARCHAR2
  3  AS
  4  BEGIN
  5     RETURN 'abc';
  6  EXCEPTION
  7     WHEN OTHERS
  8     THEN
  9        RETURN NULL;
 10  END plw6009;
 11  /

SP2-0806:Function created with compilation warnings

SQL> show errors
Errors for FUNCTION PLW6009:

LINE/COL    ERROR
--------    -------------------------------
7/9         PLW-06009: procedure 
              "PLW6009" OTHERS handler          
              does not end in
            RAISE or RAISE_APPLICATION_ERROR
これは非常に役に立つ警告ですが、注意事項が1つあります。RAISEまたはRAISE_APPLICATION_ERRORをコールして未処理の例外を伝播させるエラー・ロギング・プロシージャがある場合に、このプロシージャをコールすると、コンパイラによりこのことが認識されず、サブプログラムに対してPLW-6009警告が出されます。

クイズにチャレンジ

PL/SQLの基礎に関するそれぞれの記事では、記事の中で説明した情報の知識をテストするクイズを毎回出題しています。このクイズは次に示す他、PL/SQL Challenge（plsqlchallenge.com）にも掲載されます。PL/SQL Challengeは、PL/SQL言語のオンライン・クイズを提供するWebサイトです。このOracle Magazineの記事でクイズを読んで回答した場合、正解について次のパートで確認できます。一方、PL/SQL Challengeでクイズに挑戦した場合は、O’Reilly Media（oreilly.com）の電子書籍が当たるチャンスに応募できます。
 

質問1
次のブロックのうち、未処理のORA-00001例外が発生するものはどれですか。

 

BEGIN
   RAISE DUP_VAL_ON_INDEX;
END;
/
 

 

BEGIN
   RAISE -1;
END;
/
 

 

CREATE TABLE plch_tab (n NUMBER PRIMARY KEY)
/

BEGIN
   INSERT INTO plch_tab
        VALUES (1);
   INSERT INTO plch_tab
        VALUES (1);
END;
/
 

 

BEGIN
   RAISE DUP_VAL_ON_INDEX;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;
/
 

質問2
1つの数値型の列があるplch_tab表が作成されているとします。次のプロシージャをエラーなしでコンパイルするためには、どのように変更すれば良いですか。

 

CREATE OR REPLACE PROCEDURE plch_proc (divisor_in in NUMBER)
IS
BEGIN
   INSERT INTO plch_tab
        VALUES (100/divisor_in);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX AND NO_DATA_FOUND
   THEN
      RAISE;
 

ご意見ご感想をお寄せください。

▲ ページTOPに戻る

記事一覧へ戻る