Oracleエンジニアを目指す
ORACLE MASTER Gold ポイント解説
第4回 明示カーソルと例外処理を学ぶ

小野寺智子
2002/7/17

　今回は、試験範囲のうち、「明示カーソル」「明示カーソルの上級編」、それに「例外処理」を解説します。また、Oracle 9iの新機能についても解説します。なお、PL/SQLの分野は、今回で終わりです。次回からは難関とされるDBA-Iを解説していきます。実際の業務では、前回と今回解説するポイントを意識することが多いと思いますが、試験では変数の宣言から例外処理まで、満遍なく出題されますので、試験対策をしっかり行っておきましょう。

■明示カーソル

　前回は2つあるカーソルのうち、暗黙カーソルについて解説しました。そこで今回は、もう1つの明示カーソルについて解説します。

　この2つのカーソルの違いは、暗黙カーソルはDML文やSELECT文について自動的に宣言されますが、明示カーソルはプログラマが宣言し、名前を付けることができる点です。PL/SQLブロック内では、SELECT文は1件だけ結果を戻し、SELECT〜INTO句を使用しました。でも、それでは複数件を処理するには何度も同じSELECT文を使用することになり、手間がかかり、さらにパフォーマンスも良くありません。

　明示カーソルは、宣言部で宣言し、複数行を処理するために使用できます。前回の復習を兼ね、暗黙カーソルを使用して処理が行われる場合と、エラーを返す場合を見てみましょう。まずは前者の場合です。

SQL> DECLARE   2      v_emp EMP%ROWTYPE;   3  BEGIN   4      SELECT * INTO v_emp FROM EMP   5       WHERE EMPNO = 7782;   6  END;   7  / PL/SQLプロシージャが正常に完了しました。

　ここでは、EMPNOは一意に割り当てられた番号としているため、結果は1行戻すので暗黙カーソルが適用されます。また、暗黙カーソルは宣言部で宣言する必要はありません。

　それに対して次の場合、DEPTNOは複数行存在するとしています。その場合、複数行を暗黙カーソルで処理しようとするため、エラーが返されます。

SQL> DECLARE   2      v_emp EMP%ROWTYPE;   3  BEGIN   4      SELECT * INTO v_emp FROM EMP   5       WHERE DEPTNO = 20;   6  END;   7  / DECLARE * エラー行: 1: エラーが発生しました。 ORA-01422: 要求よりもよりも多くの行が取り出されました。 ORA-06512: 行 4

　では、次に明示カーソルを利用すると、どのような結果になるのかを見てみましょう。なお、ここではset serveroutput onを使用して、分かりやすく表示しています。

SQL> DECLARE   2      CURSOR cur_emp IS   3  SELECT empno,ename FROM EMP   4   WHERE deptno = 20;   5   6       line_emp cur_emp%ROWTYPE;   7   8  BEGIN   9      OPEN cur_emp;  10      FETCH cur_emp INTO line_emp;  11              DBMS_OUTPUT.PUT_LINE('ENO = '||line_emp.empno||',  12                                    NAME = '||line_emp.ename);  13      CLOSE cur_emp;  14  END;  15  / ENO = 7369,                                  NAME = SMITH PL/SQLプロシージャが正常に完了しました。

　結果は、複数行を処理する暗黙カーソルは、明示カーソルを利用することによりエラーを戻すことなく処理を行えるようになりました。それでは、明示カーソルの機能はどのようになっているか、図1を見て理解してください。

図1　明示カーソルの機能

　次に、明示カーソルはどのような制御構造で、どのように使用すればよいのでしょうか。明示カーソルを使用する場合は宣言部（DECLARE）で宣言します。ここでの注意点は、この段階ではカーソルにINTO句を使用できないということです（図2）。

図2　明示カーソルの利用法

明示カーソルの属性
　明示カーソルにも属性があります（表1）。属性を利用するとカーソルの状態を取得することができます。しかし、SQL文の中ではカーソル属性を直接参照できないので、注意する必要があります。

属性 型 説明 カーソル名％ISOPEN ブール カーソルがオープンしている状態ならば評 価はTRUEになる カーソル名％NOTFOUND ブール 一番最後に評価された行が戻されなけれ ば評価はTRUEになる カーソル名％FOUND ブール 一番最後に評価された行が戻されれば評 価はTRUEになる カーソル名％ROWCOUNT 数値 現在処理が行われた行数を戻す

表1 明示カーソルの属性

カーソルFORループ
　ここまで説明してきたカーソルでは、明示結果を1行しか戻しません。では、カーソル内の複数行を処理するためにはどうしたらよいのでしょうか。そこで使用するのが「カーソルFORループ」文です。まずはLOOP文を使用した場合を見てみましょう（画面1）

画面1　LOOP文を使用した例

　次に、カーソルFORループ文を使用した場合です（画面2）。カーソルFORループ文は、カーソルがオープンされるとループを反復して1回ずつフェッチ（取り出す）します。残りの行がなくなると、カーソルは自動的にクローズされます。ですから、「OPEN→FETCH→CLOSE」という処理を、簡潔なプログラムに記述することができます。カーソルFORループ文の利点は、宣言部で宣言する必要はなく、直接実行部で記述できることです。しかし、宣言部でカーソルの宣言をしないために、明示カーソルの属性を使用することはできません（画面2）。

画面2　カーソルFORループ文を使用して、画面1と同じ問い合わせをした結果

■明示カーソル上級編

　明示カーソルに関しては、明示カーソルと明示カーソル上級編を別カテゴリとして説明する書籍が多くあります。そこで、ここでも明示カーソルと明示カーソルの上級編を別カテゴリとして扱いたいと思います。

パラメータを伴うカーソル
　明示カーソルではカーソル宣言時にパラメータを渡すことができます。このパラメータを伴ったカーソルを使用すると、異なる結果セットを戻すことができるのです。パラメータを伴うカーソルではデータ型は指定できますが、サイズは指定できません（図3）。

図3　パラメータを伴うカーソルの構造

FOR UPDATE句
　行を更新、削除する前に行にロックをかけることができます。この句を使用するとほかのユーザーからの更新処理を明示的にロックできるようになります（図4）。

図4　FOR UPDATE句の構造

WHERE CURRENT OF句
　明示カーソルからデータを指定したい場合に使用します。ただし、参照するカーソルはオープンしておくこと、カーソルの問い合わせに「FOR UPDATE句」が含まれていることが前提となります。

■例外処理

　プログラムは、必ずしも正常に動作するとは限りません。正常ではない状態で、プログラムが動作しなくなる予想外のことが生じることを「例外」と呼んでいます。この例外には3つのタイプがあります。

（1）事前定義例外
（2）非事前定義例外
（3）ユーザー定義例外

　それでは、どのような場合を例外というのでしょうか。一番最初にも取り上げましたが、次のリストをもう一度見てください。

SQL> DECLARE   2      v_emp EMP%ROWTYPE;   3  BEGIN   4      SELECT * INTO v_emp FROM EMP   5       WHERE DEPTNO = 20;   6  END;   7  / DECLARE * エラー行: 1: エラーが発生しました。 ORA-01422: 要求よりもよりも多くの行が取り出されました。 ORA-06512: 行 4

　ここでは、暗黙カーソル内で複数行を戻す場合にエラーを返します。PL/SQLプログラムではこのような例外処理を行うためのルーチンを、「例外ハンドラ」と呼びます。例外ハンドラは例外処理部で記述することができます。

　次の図5の例では、例外が発生すると処理は実行部からこの例外処理部に移ります。例外処理が終了すると制御は実行環境に移ります。もし、例外処理部に該当しないエラーが発生した場合は、実行環境にエラーを返します。

図5　例外処理の構造

　次に、先ほど挙げた3タイプの例外について解説します。この3タイプは確実に把握しておきましょう。

（1）事前定義例外
　PL/SQLで事前に定義されており、宣言をしなくともOracle Serverから暗黙的に呼び出されるエラー。

（2）非事前定義例外
　Oracle Server以外の事前定義例外として定義されていないエラー。宣言部で記述する。

（3）ユーザー定義例外
　ユーザー独自の例外で、宣言部で宣言し実行部で「RAISE文」を使用して明示的に呼び出すエラー。

　例外が発生したときに、その例外に対応するエラーコード、エラーメッセージを識別するためのファンクションが、次のSQLCODEとSQLERRMになります。

SQLCODE エラーコード用の数値を返す SQLERRM エラー番号に対応するメッセージが含まれた文字データを返す 例外が発生したときのエラーコードとエラーメッセージを識別するためのファンクション

SQLCODE値 説明 0 例外は検出されなかった +1 ユーザー定義例外 +100 NO_DATA_FOUND例外 負の値 Oracle Serverエラー番号 SQLCODEのエラーコード用の数値の例

■Oracle 9i新機能に伴う新問題

　今年（2002年）2月から、Oracle 9iに対応した試験が開始されました。それに伴ってPL/SQLの分野でも若干追加された点などが出てきたので、ここではその部分を中心に解説します。

　Oracle 9iでは、「SQL 1999」に準じて機能が拡張されました。そのため、試験ではPL/SQLで問われる部分「はSQL：1999表記法」（正式には「ISO/IEC9075-1:1999」）から出題される傾向があるようです。ここでは、そこから出題される代表的な例を取り上げます。

CASE文
　CASE文は、IF文と同じように実行できます。ただし、IF文との違いは、結果を選択して値を返すことです。ブール属性を使用しないため選択子という識別子を使用します。「IF-THEN-ELSEIF」の後継ともいえる文です。この新しいCASE文を使うことによってパフォーマンスの向上を図ることもできます。では、以前使用した分岐制御とCASE文を使用した文とを比較してみましょう（図6）。

図6　同じ構文をIF-THEN-ELSEIF文で書いた場合とCASE文で書いた場合との比較。CASE文の方がすっきりしている。なお、画面の都合でインデントをはずしている

MERGE文
　データウェアハウスのような環境で力を発揮する重要な機能です。例えば、大きな表a1表と、条件に基づいたa1表に挿入するためのa2表があった場合、ON句に指定された結合条件を評価し、a2表の各行と一致する行がa1にあるかを探します。一致するかしないかで、次のように処理が分岐されます。

一致した行があった場合 UPDATE SET句によってa1の行が更新される 一致した行がない場合 INSERT句に指定されている行が挿入される

　MARGE文の場合、表が2つあることが前提です。表がない場合は使用できません。

「連載　ORACE MASTER Goldポイント解説」