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ポイント解説」

自分戦略研究所、フォーラム化のお知らせ

@IT自分戦略研究所は2014年2月、@ITのフォーラムになりました。

現在ご覧いただいている記事は、既掲載記事をアーカイブ化したものです。新着記事は、 新しくなったトップページよりご覧ください。

これからも、@IT自分戦略研究所をよろしくお願いいたします。