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

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

第7回 「明示カーソル処理」

2012.01.16

年も改まり、2012年になりました。今年もよろしくお願いします。

さて、「PL/SQLを使ってみよう!」今回は7回目ですね。
今回は、題して「明示カーソル処理」です。SELECT文で、1行、1行、結果を取得する方法です。
前回までは、「SELECT INTO 文」でした。これは、1行だけ結果を返すSELECT文でないとエラーになってしまいますので、ほとんど主キーアクセスの場合しか使わないでしょう。しかし、主キー以外を条件とする問合せでは、複数行になるのが普通ですから、そういった場合は、これから説明する「明示カーソル処理」を使います。

それでは、さっそく「使ってみよう!」 

今回は、主キー以外の条件指定として、部門番号を指定し、その部門に所属する社員の詳細を画面に表示します。
以下のようなスクリプトファイルを作成し、SQL*Plusまたは、SQL*Developerから実行してみてください。ファイル名を仮にtest.sqlとします。scottユーザで接続して実行します。

▼▼▼ ここから test.sqlファイルの内容  行番号は説明の便宜上のものです ▼▼▼▼
1 set serveroutput on
2 set verify off
3 DECLARE
4   /* 明示カーソルの宣言 */
5   CURSOR  CUR_EMP IS
6      SELECT EMPNO, ENAME FROM EMP
7      WHERE  DEPTNO = &部門番号;
8  /* レコード型変数の宣言 */
9     REC    CUR_EMP%ROWTYPE;
10 BEGIN
11   /* カーソルをオープン */
12   OPEN  CUR_EMP;
13   /* 1行ずつ取得しながら画面表示 */
14   LOOP
15     FETCH CUR_EMP INTO REC;
16     EXIT  WHEN  CUR_EMP%NOTFOUND;
17     DBMS_OUTPUT.PUT_LINE(REC.EMPNO || ' ' || REC.ENAME);
18   END LOOP;
19   /* カーソルをクローズ */
20    CLOSE  CUR_EMP;
21 END;
22 /
23 set verify on
▲▲▲ ここまで ▲▲▲

このファイルを実行すると以下のようになります。

▼▼▼ ここから ▼▼▼
SQL> @test.sql
部門番号に値を入力してください: 20
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD

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

ここでは、部門番号に20を指定して、その部門の社員の詳細を表示したわけです。

今回は、ポイントだけを解説し、詳細は次回以降に説明します。

まず、複数行を返すSELECT文を扱うために、宣言部で、そのSELECT文をもとにカーソルを宣言します。(5-7行) また、そのSELECT文の1行と同じ構造のレコード型変数も宣言します。(9行目) 

次に実行部でそのカーソルをオープンします。(12行目) その後、そのカーソルから1行を取得し変数に代入します。(15行目) その変数を画面表示します。(17行目)  行の処理をループ処理で繰り返します。 (14行目~18行目) これにより、カーソルから1行、1行処理することができます。最後の行を取得済みの状態でさらに行を取得しようとしたときに、ループから抜けます。(16行目) 最後にカーソルをクローズします。(20行目)

どうですか? 各項目の詳細は次回、説明しますが、これを見ながら、十分実用的なプログラムが書けると思います。

最後におまけの補足説明です。いずれも、直接PL/SQLの文法とは関係ありませんが、SQL*PlusやSQL*Developerの機能です。スクリプトファイルのPL/SQLブロック以外の部分です。

1行目  set serveroutput on
        DBMS_OUTPUTの画面出力を有効にする(SQL*Plus, SQL*Developer等で有効)
2行目  set verify off
        7行目の置換変数(&部門番号)の文字列置換の前後のメッセージを抑制する
7行目  &部門番号
サーバーに送信する前に、他の文字列に置換する(この機能を置換変数という)

22行目 /(スラッシュ)   PL/SQLブロック部分(3-21行目)をまとめて、サーバーに送信する

今回はここまでです。次回、ご期待ください。

先頭へ戻る