第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行目)をまとめて、サーバーに送信する
今回はここまでです。次回、ご期待ください。