PL/SQL EXCEPTION (例外)

「例外」とは、プログラム実行中に起こるPL/SQLエラーである。例外が発生すると、呼び出した環境によって例外が処理されるが、PL/SQLのEXCEPTIONブロックに独自の処理を記述することもできる。

PL/SQLの例外ハンドラ

PL/SQLのプログラム内で例外が発生した場合、例外ハンドラに処理が移る。例外ハンドラの中で発生した例外をキャッチして、発生した例外ごとに異なるエラー処理を実行することができる。

EXCEPTION
  WHEN exception THEN statements
 [WHEN exception THEN statements ...]

exception

事前定義済み例外の識別子、ユーザー定義例外の識別子又はストアドパッケージ内で定義されているストアドパッケージ固有例外の識別子を指定する。

また、最後のWHEN節には、その他の例外を表すOTHERSを指定することができる。

statements

exceptionで指定した例外が発生したときの処理を記述する。

例外ハンドラを記述したPL/SQLプログラムの例を示す。

DECLARE
  per NUMBER(3,1);
BEGIN
  SELECT price / earnings INTO per FROM stocks WHERE code = 4716;
  INSERT INTO stats (code, ratio) VALUES (4716, per);
  COMMIT;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
      INSERT INTO stats (code, ratio) VALUES (4716, NULL);
      COMMIT;
  WHEN OTHERS THEN
      ROLLBACK;
END;

WHEN 文には例外が発生したときの処理を指定するが、特定の例外が発生したときに何もしないようにするには NULL 文を使用する。

EXCEPTION
  WHEN NO_DATA_FOUND THEN
      NULL;  -- 何もしない

発生した例外をもう一度発生させて、コールした環境に例外を通知するには、 RAISE 文を使用する。

EXCEPTION
  WHEN NO_DATA_FOUND THEN
      RAISE;  -- 呼び出し元に同じ例外情報を伝播する

OR 演算子を用いて、複数の例外を対象とすることもできる。

EXCEPTION
  WHEN DUP_VAL_ON_INDEX OR NO_DATA_FOUND THEN
      RETURN -1;

PL/SQLの事前定義済み例外

Oracleデータベースで定義されている例外の一覧を次に示す。

ACCESS_INTO_NULL

プログラムが未初期化（アトミックNULL）オブジェクトの属性に値を代入しようとしたときに発生する例外

CASE_NOT_FOUND

CASE式のWHEN句が何も選択されておらず、ELSE句もないときに発生する例外

COLLECTION_IS_NULL

プログラムがEXISTS 以外のコレクション・メソッドを未初期化（アトミックNULL）のNESTED TABLE またはvarrayに適用しようとしたか、または未初期化のNESTED TABLE またはvarray の要素に値を代入しようとしたときに発生する例外

CURSOR_ALREADY_OPEN

すでにオープンされているカーソルをオープンしようとしたときに発生する例外。カーソルをオープンするには、一度クローズする必要がある。カーソルFORループは、参照するカーソルを自動的にオープンするため、ループの内側ではカーソルをオープンできない。

DUP_VAL_ON_INDEX

UNIQUE索引によって制約されているデータベース列に、重複した値を格納しようとしたときに発生する例外

INVALID_CURSOR

オープンされていないカーソルをクローズするなど、不正なカーソル操作を実行しようとしたときに発生する例外

INVALID_NUMBER

SQL文の中で文字列が正しい数値を表していなかったために、文字列から数値への変換が失敗したときに発生する例外

LOGIN_DENIED

不正なユーザー名又はパスワードでOracleデータベースにログオンしようとしたときに発生する例外

NO_DATA_FOUND

SELECT INTO文が行を戻さなかったときに発生する例外

COUNTやMAXなどの集合関数を使用した場合、NO_DATA_FOUND例外は発生しない。たとえば、次のようなSQL文である。

SELECT MAX(sal) INTO max_salary FROM emp WHERE deptno = 2

NOT_LOGGED_ON

Oracle に接続していないプログラムが、データベース・コールを発行したときに発生する例外

PROGRAM_ERROR

PL/SQLに内部的な問題が発生したときに発生する例外

ROWTYPE_MISMATCH

1つの代入の中に含まれるホスト・カーソル変数とPL/SQL カーソル変数の戻り型に互換性がない場合に発生する例外

SELF_IS_NULL

NULL インスタンスでMEMBER メソッドをコールしようとしたときに発生する例外

STORAGE_ERROR

PL/SQL のメモリーが足りない場合、またはメモリーが破壊されている場合に発生する例外

SUBSCRIPT_BEYOND_COUNT

コレクション中の要素数より大きい索引番号を使用してNESTED TABLE またはvarray の要素を参照した場合に発生する例外

SUBSCRIPT_OUTSIDE_LIMIT

有効範囲外（たとえば-1）の索引番号を使用してNESTED TABLE またはvarray の要素を参照した場合に発生する例外

SYS_INVALID_ROWID

文字列が正しいROWID を表していなかったために、文字列から汎用ROWID への変換が失敗した場合に発生する例外

TIMEOUT_ON_RESOURCE

Oracle がリソースを求めて待機しているときにタイムアウトが発生した場合に発生する例外

TOO_MANY_ROWS

SELECT INTO文が複数の行を戻したときに発生する例外

VALUE_ERROR

算術エラー、変換エラー、切捨てエラー又はサイズ制約エラーが発生したときに発生する例外

ZERO_DIVIDE

数値をゼロで割ろうとしたときに発生する例外

ユーザー定義例外

独自の例外を定義して、例外を明示的に発生させたり、その例外が発生したときに行う処理を記述することができる。

ユーザ定義例外を使用するには、次の手順で行う。

1. ユーザ定義例外を宣言する
2. ユーザ定義例外を発生させる
3. ユーザ定義例外をキャッチする

DECLARE
  v_code NUMBER := 9999;
  v_name VARCHAR2(32) := 'Test';
  -- ユーザー定義例外を宣言する
  example_exception EXCEPTION;
BEGIN
  UPDATE tokyo1 SET name = v_name WHERE code = v_code;
  IF SQL%NOTFOUND THEN
      -- ユーザー定義例外を発生させる
      RAISE example_exception
  END IF;
EXCEPTION
  WHEN example_exception THEN
      -- ユーザー定義例外が発生したときの処理
      DBMS_OUTPUT.PUT_LINE('No such code');
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;
/

ユーザー定義例外を宣言する

ユーザー定義例外を使用する場合は、DECLAREブロックでユーザー定義例外の宣言を行う。ユーザー定義例外の宣言を行う構文を次に示す。

identifier EXCEPTION;

identifier には、ユーザー定義例外の識別子を指定する。

ユーザー定義例外の宣言を行う例を次に示す。

DECLARE
  exsample_exception EXCEPTION;

ユーザ定義例外を発生させる

RAISE文でユーザー定義例外を発生させることができる。

RAISE identifier;

identifier には、 DECLARE ブロックで宣言済みのユーザー定義例外識別子を指定する。

SQLCODE

SQLCODEは、直前に呼び出された例外に対応付けられている番号コードを戻り値として返す組み込みファンクションである。例外ハンドラの中でのみ有効で、例外ハンドラの外では常に0を戻す。

SQLCODE

SQLCODEの使用例を示す。

DECLARE
  sql_code NUMBER;
BEGIN
  ...
EXCEPTION
  WHEN OTHERS THEN
      sql_code := SQLCODE;
END;

SQLERRM

SQLERRMは、SQLCODEに対応付けられているエラーメッセージを戻り値として返す組み込みファンクションである。

SQLERRM

SQLERRM(number)

number

エラーメッセージを取得するSQLCODEを指定する。numberの指定を省略した場合は、SQLCODEの現在値に対応付けられているエラーメッセージを戻す。

BEGIN
  ...
EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;