e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第6回 「SELECT INTO文の例外処理」

2011.12.26

前回まで、SELECT INTO文について説明してきました。
それでは、早速、簡単かつ実用的なプログラム例として以下のような記述を適当なファイルにコピーして、SQL*Plusや、SQL*Developerなどから実行してみましよう。
ここでは、以下の記述を TEST.sqlというファイルに記述して、scottユーザで実行してみましょう。皆さんもSQL*Plusなどで、scottユーザに接続できる環境があれば以下の記述をファイルにコピーして簡単に実行できます。

▼▼▼ TEST.sqlファイルの内容 ▼▼▼
set serveroutput on
set verify off
DECLARE
   V_EMPNO   EMP.EMPNO%TYPE := &社員番号;
   V_ENAME   EMP.ENAME%TYPE;
   V_DEPTNO  DEPT.DEPTNO%TYPE;
   V_DNAME   DEPT.DNAME%TYPE;
BEGIN
   /* 指定された社員番号をもとに社員名と部門番号を取得する */
   SELECT ENAME, DEPTNO INTO V_ENAME, V_DEPTNO
   FROM   EMP
   WHERE  EMPNO = V_EMPNO;
  /*その部門番号で、部門表を問い合わせて、部門名を取得する*/
   SELECT DNAME INTO V_DNAME FROM DEPT
   WHERE  DEPTNO = V_DEPTNO;
  DBMS_OUTPUT.PUT_LINE('問合せの社員名は ' || V_ENAME || ' です。' ||
                       '所属部門は ' || V_DNAME || ' です。');
END;
/
▲▲▲ ここまで ▲▲▲

では早速TEST.sqlファイルを実行すると、コード内に「&社員番号」という記述があるので、それを指定した文字列(以下の例では「7934」)に置換してからサーバーに送信してます。
その結果、サーバーから戻った社員の情報を画面表示しているものです。

▼▼▼▼ TEST.sqlの実行結果 ▼▼▼
SQL> @TEST
社員番号に値を入力してください: 7934  ←入力
問合せの社員名は MILLER です。所属部門は ACCOUNTING です。 ←出力

PL/SQLプロシージャが正常に完了しました。
▲▲▲ ここまで ▲▲▲

このプログラムは、最初に社員番号を入力して、それをもとにSELECT INTO文で、社員名と部門番号を変数に取得し、次のSELECT INTO文で、その部門番号をもとに部門名を取得し、最後にその社員の社員名と所属部門名を画面表示しています。

しかし、このプログラムには問題があります。それは、存在しない社員番号を指定すると、SELECT INTO文が0件となるため、システムエラーとなってしまうことです。SELECT INTO文は1行以外の結果になるとエラーになります。
早速、やってみます。

▼▼▼ ここから ▼▼▼
SQL> @TEST
社員番号に値を入力してください: 9999  ←存在しない社員番号
DECLARE
*
行1でエラーが発生しました。:
ORA-01403: データが見つかりません。
ORA-06512: 行8
▲▲▲ ここまで ▲▲▲

このプログラムがユーザにリリースされたプログラムだとすると、ユーザの観点からは、エラーの理由がわかりません。このような場合、「社員番号 9999 は存在しません」といったわかりやすいエラーメッセージを表示したいものです。そのためには例外処理部を追加します。
以下は例外処理部を設けた修正後のTEST.sqlファイルです。

▼▼▼▼ 修正後のTEST.sqlファイル ▼▼▼▼▼▼▼▼▼▼
set serveroutput on
set verify off
DECLARE
   V_EMPNO   EMP.EMPNO%TYPE := &社員番号;
   V_ENAME   EMP.ENAME%TYPE;
   V_DEPTNO  DEPT.DEPTNO%TYPE;
   V_DNAME   DEPT.DNAME%TYPE;
BEGIN
   /* 指定された社員番号をもとに社員名と部門番号を取得する */
   SELECT ENAME, DEPTNO INTO V_ENAME, V_DEPTNO
   FROM   EMP
   WHERE  EMPNO = V_EMPNO;
  /*その部門番号で、部門表を問い合わせて、部門名を取得する*/
   SELECT DNAME INTO V_DNAME FROM DEPT
   WHERE  DEPTNO = V_DEPTNO;
  DBMS_OUTPUT.PUT_LINE('問合せの社員名は ' || V_ENAME || ' です。' ||
                       '所属部門は ' || V_DNAME || ' です。');
  /* 以下は追加した例外処理部 */
EXCEPTION
   WHEN  NO_DATA_FOUND  THEN
     DBMS_OUTPUT.PUT_LINE('社員番号 ' || V_EMPNO || ' は存在しません');
END;
/
▲▲▲ ここまで ▲▲▲

これを実行すると以下のようになります。

▼▼▼ ここから ▼▼▼
SQL> @TEST
社員番号に値を入力してください: 9999
社員番号 9999 は存在しません

PL/SQLプロシージャが正常に完了しました。
▲▲▲ ここまで ▲▲▲

これで、存在しない社員番号を指定しても、エラーではなく、社員番号が存在しない旨の通知メッセージを出力できます。

修正前後でなにが違うかというと、例外処理部(「EXCEPTION」以下の記述)が設けられていることです。例外処理部がない場合、実行部でエラーが発生すると、そのまま異常終了です。しかし、例外処理部があれば、例外処理をすることで、正常終了できます。

今回のエラーは、「ORA-01403: データが見つかりません。」ですが、この「ORA-01403」エラーについては、「NO_DATA_FOUND」という名前がついています。
このようにエラーにつけた名前のことを例外名といいます。
すべてのエラーに例外名がつけられているわけではなく、よくあるエラーについて例外名がつけられています。
これを事前定義例外といいます。例外処理部では例外名を使って例外ハンドラという記述をします。
では、例外処理部の一般的な構文を説明します。

EXCEPTION
     WHEN  例外名1  THEN  ← 例外ハンドラ1
           処理1・・・
     WHEN  例外名2  THEN  ← 例外ハンドラ2
           処理2・・・
・・・・・・
     WHEN  OTHERS  THEN   ← OTHERSハンドラ
           その他の処理・・・
END;  (←PL/SQLブロックの終わり)

ご覧のように複数の例外ハンドラが可能です。その順番は任意です。
エラーが発生したときに実際に実行される例外ハンドラはひとつだけです。例外ハンドラを実行すると、PL/SQLブロックは正常終了となります。
また、一番最後に「WHEN OTHERS THEN」という記述が可能ですが、この部分をOTHERSハンドラといいます。
OTHERSハンドラは該当する例外ハンドラがなかった場合に、処理される例外ハンドラです。例えば、例外名のついていないエラーについてはOTHERSハンドラで例外処理をすることが可能です。例外名のついていないエラーに例外名をつけることもできます。非事前定義例外といいますがそれについては別の機会に説明します。

また、余談ですが、今回プログラムらしくするために、最初に社員表(EMP)をSELECT INTO で問合せ、その結果を引き継いで次に部門表(DEPT)をSELECT INTO で問い合わせていますが、本当は、以下のように、社員表(EMP)と部門表(DEPT)を結合した ひとつのSELECT INTO文を使うほうが、望ましいといえます。

SELECT E.ENAME , D.DNAME INTO V_ENAME, V_DNAME
    FROM   EMP E,  DEPT D
    WHERE  E.DEPTNO = D.DEPTNO  AND
           E.EMPNO = V_EMPNO;

プログラムで細かくSELECT 文を分けるよりも、可能であればひとつの SELECT 文にしたほうが、実行される文の回数が少なくなるので効率がよくなるケースがほとんどです。
しかし、それもケースバイケースであり、ひとつにまとめるために大変複雑なSELECT文になるようでしたら、無理せずプログラムでSELECT文を分けてもよいでしょう。
いずれにしろPL/SQLブロックは無名ブロックでも、プロシージャでも、一部のオラクル製品を除き、必ずデータベース上で実行しますので、文を分けても分けなくてもネットワークに対する負荷にはほとんど影響がありません。
これがリモートのアプリケーションから単独でSQL文を発行する場合と比較したときのPL/SQLの大きなメリットですね。

それでは今回はここまでにします。また次回、ご期待ください。

先頭へ戻る