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

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

第8回 「明示カーソル処理(詳細1)」

2012.01.30

前回は、部門番号を指定して、その部門の社員の明細を画面表示する明示カーソル処理のPL/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
▲▲▲ ここまで ▲▲▲

これをファイル(例 test.sql)に書いて(ただし行番号は除く)SQL*PlusやSQL*Developerなどから実行できます。以下はSQL*Plusの場合です。

▼▼▼ ここから ▼▼▼
SQL> @test.sql
部門番号に値を入力してください: 20
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD PL/SQLプロシージャが正常に完了しました。
▲▲▲ ここまで ▲▲▲

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

では、今回はこの一連のカーソル処理で、カーソル宣言の部分と、そのカーソルから行を取得するためのレコード型の変数宣言の部分について詳しく解説します。サンプル例の行番号でいうと、5~9行目の部分です。

そもそもカーソルというものは、SQLで内部的に使われている仕組みです。SQLは一つのコマンドで複数行を処理できる特徴がありますが、内部的にはカーソルという仕組みで1行ずつ処理しています。このカーソルをプログラムで明示的に制御することで、複数行結果を返すSELECT文から1行ずつ取り出すことができるのです。このようにプログラムで明示的に操作をするので正式には明示カーソルといいますが、以下、単にカーソルと表現します。

では、PL/SQLプログラムで複数行を返すSELECT文を扱うためには、宣言部でそのSELECT文をもとにカーソルを宣言しなければなりません(5-7行)。 正確にいうと、カーソルとして明示的に宣言しなくても複数行のSELECT文から1行ずつ取得できるのですが、それはいわば応用編の話であり、基本的にはカーソルの宣言は必要です。

では、早速、カーソルの宣言構文です。以下の通りです。

<宣言部で>
CURSOR カーソル名 IS SELECT文;

まず、先頭に「CURSOR」というキーワードが必要です。
次にカーソル名ですが、なにか適当に名前をつけます。名前についてのルールは変数などと同様ですが、「先頭が数字であってはいけない」、「長さは30バイトまで」などがあります。
次に「IS」です。「AS」ではないので注意してください。PL/SQLでは「IS」または「AS」の両方がOKということが多いのですが、カーソルについては「IS」です。
次にSELECT文ですが、複雑なSELECT文でももちろんOKです。ただ、気をつけていただきたいのは、SELECT句に列名でなく式や関数を使っている場合は、簡単な列別名を指定してください。コンパイルエラーにはならないのですが、そうしないとその列を参照するプログラミングが少し面倒になります。

例えば以下の例は好ましくありません。

<好ましくないカーソル宣言>
CURSOR CUR1 IS SELECT EMPNO, upper(ENAME), SAL * 12 FROM EMP;
上のカーソル宣言は「upper(ENAME)」や「SAL * 12」に対して列別名を指定していません。

<好ましいカーソル宣言>
CURSOR CUR1 IS SELECT EMPNO, upper(ENAME) UPNAME , SAL * 12 ANNSAL FROM EMP;
上のカーソル宣言では「upper(ENAME)」に対して「UPNAME」という別名をつけ、「SAL * 12」に対して「ANNSAL」という列別名をつけてます。これでその後の操作で単純に列別名でアクセスできます。

では、カーソル宣言についてはここまでにしておきます。

次にレコード型変数の宣言です。カーソルから取得した行を格納するレコード型変数が必要です。
レコード型変数を明示的に宣言せずに使う方法もあるのですが、それは応用編の話であり、基本的にはレコード型変数を明示的に宣言する必要があります。
では最初にそもそもレコード型変数とは?ということについて説明します。
本メルマガの3回目で、「変数とは値を格納する箱のようなもの」と説明しましたが、レコード型変数も、やはり箱のイメージです。ただし、単純な箱ではなく内部構造を持っています。イメージとしては、表や問合せの1行を思い浮かべてください。表や問合せの1行は列の値が横に並んでいますが、それがまさにレコード型のイメージです。
したがってレコード型変数は単純なひとつの箱ではなく、複数の箱が横に並んでいるイメージです。
レコード型変数でも全体をまとめて一つの変数として扱うので、いわば大きな一つの箱の中に小さな箱が横に並んで格納されているイメージです。ちなみに、この小さな箱をフィールドといいます。

では、次にカーソルの行の列と同じ名前のフィールドをもつレコード型変数の宣言の仕方を以下に説明します。(サンプルの9行目 変数名 REC)

<カーソルの行と同じ型のレコード型変数の宣言>
レコード型変数名 カーソル名%ROWTYPE;

たいへん簡単ですね。注意点としてはこの宣言よりも前に(つまり上の方で)、カーソルを宣言する必要があります。そのカーソル宣言を引き継いでそのカーソルのSELECT句の列名、もしくは列別名と同じ名前のフィールドを持つレコード型変数が作成されます。いわばカーソルの行と同じ型のレコード型変数です。

参考までに、サンプルにはありませんが、表の行と同じ型のレコード型変数の宣言も可能です。
<カーソルの行と同じ型のレコード型変数の宣言>
レコード型変数名 表名%ROWTYPE;

カーソルを元にする場合と同じですね。

レコード型変数のフィールドにアクセスするには、その変数に対して「.フィールド名」をつけます。(サンプルの17行目 REC.EMPNO やREC.ENAMEという記述);
例えば、サンプルにはありませんが、レコード型変数のフィールドへの代入は以下のようになります。

REC.EMPNO := 100;

レコード型変数RECのEMPNOフィールドに100を代入している例ですね。

サンプルにはありませんが、表やカーソルと無関係に、レコード型変数を個別に宣言する方法もあります。その場合は先に「型」だけを宣言し、次にその型の変数を宣言する2段階の宣言となるのですが、詳しくは別の機会で紹介します。

それでは今回はここまでです。今回はいわば、カーソルについて、宣言部での詳細を中心に解説しました。次回は実行部でのカーソル処理の詳細について解説します。ではご期待ください。

先頭へ戻る