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

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

第46回 「動的SQL(複数行返す問い合わせの場合)」

2013.01.21

こんにちは。インストラクターの蓑島です。
前回は1行を返す問い合わせを動的SQLで実行する方法を解説しました。
今回は複数行を返す問い合わせを動的SQLで行う方法を解説します。

前回の最後に少し触れましたが、複数行の問い合わせの場合だけ、例外的に EXECUTE IMMEDIATE を用いません。
そのかわり、カーソル変数というもの使用することで、動的SQLを可能にしてます。

では復習の意味で、複数行を返す問い合わせを、動的SQLではなく、通常の埋め込みSQLで行う方法を確認して、それから動的SQLの場合を解説します。

まず、通常の埋め込みSQLで複数行を返す問い合わせを行うには、明示カーソルを使います。
(明示カーソルについては、バックナンバー 第7回~第11回で解説しています)

以下のケース1の例をご覧ください。

<<< ケース1 埋め込みSQL 明示カーソル処理>>>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  /*********************************************/
  3  -- 明示カーソルの宣言
  4  /*********************************************/
  5    CURSOR CUR_EMP IS
  6       SELECT * FROM EMP WHERE DEPTNO = 10;
  7  /*********************************************/
  8  -- レコード型変数の宣言
  9  /*********************************************/
 10      REC    EMP%ROWTYPE;
 11  BEGIN
 12  /*********************************************/
 13  -- カーソルをオープン
 14  /*********************************************/
 15     OPEN CUR_EMP;
 16  /*********************************************/
 17  -- 1行ずつ取得しながら画面表示
 18  /*********************************************/
 19     LOOP
 20       FETCH CUR_EMP INTO REC;
 21       EXIT WHEN CUR_EMP%NOTFOUND;
 22       DBMS_OUTPUT.PUT_LINE(REC.EMPNO || ' ' || REC.ENAME);
 23     END LOOP;
 24  /*********************************************/
 25  -- カーソルをクローズ
 26  /*********************************************/
 27      CLOSE CUR_EMP;
 28  END;
 29  /
7782 CLARK
7839 KING

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

上記は、社員表(EMP表)から部門番号が10の社員を問い合わせて、その1行1行を画面に表示している例です。

ポイントは以下の通りです。

まず、複数行となる問い合わせの「SELECT * FROM EMP WHERE DEPTNO = 10」に対して、5-6行目で明示カーソルを定義してます。ここで定義したカーソル名は「CUR_EMP」です。
次のこのCUR_EMPカーソルを15行目でオープンしています。
これにより実際に問い合わせが実行され、メモリ上に問い合わせ結果の行の集合(結果セット)ができます。

次に20行目のFETCH文によりこの結果セットから、1行を取り出しレコード変数に格納します。
22行目でそのレコード変数の内容を画面に表示します。
19行目と23行目が、 LOOP とEND LOOP; なのでこの範囲を繰り返し処理しますが、21行目で、FETCHして行がなかったときに%NOTFOUNDがTRUEとなり繰り返しから抜けます。

最後に27行目のCLOSE文で結果セットをメモリから解放します。

以上が典型的な明示カーソル処理の流れです。
では、これとまったく同じことを動的SQLにした場合、どのようになるでしょうか? 
実は修正はほんのわずかです。
ケース2がその解答例です。コメント以外で修正した箇所だけを赤い字にしています。

<<<ケース2 動的SQL カーソル変数を使った処理 >>>
SQL> DECLARE
  2  /*********************************************/
  3  -- カーソル変数の宣言
  4  /*********************************************/
  5    TYPE CUR_EMP_TYPE IS REF CURSOR;
  6    CUR_EMP CUR_EMP_TYPE;
  7  /*********************************************/
  8  -- レコード型変数の宣言
  9  /*********************************************/
 10      REC EMP%ROWTYPE;
 11  BEGIN
 12  /*********************************************/
 13  -- カーソルをオープン
 14  /*********************************************/
 15     OPEN CUR_EMP FOR 'SELECT * FROM EMP WHERE DEPTNO = 10';
 16  /*********************************************/
 17  -- 1行ずつ取得しながら画面表示
 18  /*********************************************/
 19     LOOP
 20       FETCH CUR_EMP INTO REC;
 21       EXIT WHEN CUR_EMP%NOTFOUND;
 22       DBMS_OUTPUT.PUT_LINE(REC.EMPNO || ' ' || REC.ENAME);
 23     END LOOP;
 24  /*********************************************/
 25  -- カーソルをクローズ
 26  /*********************************************/
 27     CLOSE CUR_EMP;
 28  END;
 29  /
7782 CLARK
7839 KING

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

上記の二つの例はコーディングにほとんど差がありません。
差がある部分を赤で強調して違いをわかりやすくしました。

まず、違いは大きく二つあります。
一つ目は、カーソルではなく、カーソル変数を定義することです。(5-6行目)

カーソル変数の定義の仕方は2段階となり、以下の構文です。

①  TYPE  型名  IS  REF  CURSOR;   
(※IS  REF  CURSOR は固定)
②  カーソル変数名  上記①の型名;

ここでは、カーソル変数名を CUR_EMPという名前で定義してます。

違いの二つ目は、15行目のOPEN処理です。
カーソル変数に対して、OPEN処理を行うのですが、この時にFOR句を使って問い合わせ(SELECT文)と関連づけます。
そして、もっとも注目していただきたのは、『FORで指定するSELECT文が動的SQL(文字列のSQL)である』という点です。
すなわち、SELECT文がシングルコーテーションで囲まれています。つまり、文字列のSELECT文を対象に処理ができるわけです。
まとめると、OPEN処理の構文は以下の通りです。

③  OPEN  カーソル変数名  FOR  'SELECT・・・・・';

そして、いったん、OPEN処理を行えばその後の処理は、まったく通常の明示カーソル処理と同じになります。
ケース1においては、「CUR_EMP」という名前は、明示カーソル名ですが、ケース2においては、「CUR_EMP」という名前はカーソル変数名です。
定義の仕方や、OPENの仕方に違いはありますが、OPEN後の操作にはまったく違いがありません。
すなわち、FETCHの仕方(21行目)、%NOTFOUDNによる終了判定の仕方(21行目)、CLOSEの仕方(27行目)は、明示カーソルでもカーソル変数でもまったく同じであることがわかります。

このように、明示カーソルのもとになっているSELECT文が文字列のSELECT文(動的SQL)である場合は、カーソル変数を用いることにより、通常の明示カーソル処理と同じ処理が可能になるのです。

なお、このカーソル変数というものは、問い合わせ結果の行の集合(結果セット)に対する現在行へのポインタであり、その本来の目的はポインタアクセスにより結果セットを複数のプログラムで共有することにあります。
そして今回、解説していませんが、カーソル変数の定義の仕方によって、「弱いカーソル変数」、「強いカーソル変数」とあり、「弱いカーソル変数」の場合、特定の問い合わせの型に制限されることはないので、動的SQLでも対応できるのです。
上記で解説したカーソル変数は、当然「弱いカーソル変数」です。

ということで、カーソル変数というものは今回解説していない複雑な背景があるのですが、要はそれを使えば、ケース2のように動的SQLでも明示カーソル処理が可能になるのです。

それでは今回はここまでにいたします。

次回も動的SQLについて解説しますが「動的SQLでのバインド変数の使用」について解説したいと思います。
パフォーマンスの向上などに役立ちます。ご期待ください。

先頭へ戻る