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 |
ここでは、EMPNOは一意に割り当てられた番号としているため、結果は1行戻すので暗黙カーソルが適用されます。また、暗黙カーソルは宣言部で宣言する必要はありません。
それに対して次の場合、DEPTNOは複数行存在するとしています。その場合、複数行を暗黙カーソルで処理しようとするため、エラーが返されます。
SQL> DECLARE |
では、次に明示カーソルを利用すると、どのような結果になるのかを見てみましょう。なお、ここではset serveroutput onを使用して、分かりやすく表示しています。
SQL> DECLARE |
結果は、複数行を処理する暗黙カーソルは、明示カーソルを利用することによりエラーを戻すことなく処理を行えるようになりました。それでは、明示カーソルの機能はどのようになっているか、図1を見て理解してください。
図1 明示カーソルの機能 |
次に、明示カーソルはどのような制御構造で、どのように使用すればよいのでしょうか。明示カーソルを使用する場合は宣言部(DECLARE)で宣言します。ここでの注意点は、この段階ではカーソルにINTO句を使用できないということです(図2)。
図2 明示カーソルの利用法 |
明示カーソルの属性
明示カーソルにも属性があります(表1)。属性を利用するとカーソルの状態を取得することができます。しかし、SQL文の中ではカーソル属性を直接参照できないので、注意する必要があります。
|
|||||||||||||||
表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 |
ここでは、暗黙カーソル内で複数行を戻す場合にエラーを返します。PL/SQLプログラムではこのような例外処理を行うためのルーチンを、「例外ハンドラ」と呼びます。例外ハンドラは例外処理部で記述することができます。
次の図5の例では、例外が発生すると処理は実行部からこの例外処理部に移ります。例外処理が終了すると制御は実行環境に移ります。もし、例外処理部に該当しないエラーが発生した場合は、実行環境にエラーを返します。
図5 例外処理の構造 |
次に、先ほど挙げた3タイプの例外について解説します。この3タイプは確実に把握しておきましょう。
(1)事前定義例外
PL/SQLで事前に定義されており、宣言をしなくともOracle Serverから暗黙的に呼び出されるエラー。
(2)非事前定義例外
Oracle Server以外の事前定義例外として定義されていないエラー。宣言部で記述する。
(3)ユーザー定義例外
ユーザー独自の例外で、宣言部で宣言し実行部で「RAISE文」を使用して明示的に呼び出すエラー。
例外が発生したときに、その例外に対応するエラーコード、エラーメッセージを識別するためのファンクションが、次のSQLCODEとSQLERRMになります。
|
|
■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にあるかを探します。一致するかしないかで、次のように処理が分岐されます。
|
MARGE文の場合、表が2つあることが前提です。表がない場合は使用できません。
@IT自分戦略研究所は2014年2月、@ITのフォーラムになりました。
現在ご覧いただいている記事は、既掲載記事をアーカイブ化したものです。新着記事は、 新しくなったトップページよりご覧ください。
これからも、@IT自分戦略研究所をよろしくお願いいたします。