カーソルについて

1 カーソルについて

SQLでは、大量のデータを１つの命令で一括処理することができますが、一方で個々のデータを1行づつ処理したい場合もあります。そのような場合にカーソルを使用します。

1.カーソルとは何か？

カーソルとはクエリの結果セットに、一時的に蓄えておくための仮想的な作業領域のことです。 SQL はそのままではデータを1行ずつ処理していくことができません。そのため対象となるデータをまとめて表から取り出し、その結果セットをカーソルというオブジェクトに一旦蓄えておいた上で、ループ処理を実行しながらその中の行を一行ずつ取り出し処理していくのです。この場合のカーソルはワープロや表計算ソフトなどで、現在位置を示すカーソルに似ています。カーソルにはカーソルの中での現在位置を示すポインターが用意されています。ポインターはカーソルに蓄えられている行の集合の中から、取り出す対象となる行を指し示します。ループ処理が実行されるたびにポインターを1行ずつ進んで、取り出す対象となる行を更新します。

2.カーソルを使う場合の作業手順

カーソルを使う場合には一般的に次のような手順に従って作業を行います。

・カーソルの宣言する
・カーソルを開く
・ループ処理を実行し一行ずつデータを取り出していく
・作業が終了したらカーソルを閉じる

●.カーソルの宣言
カーソルを使う場合にまず最初に行う作業は、カーソルを宣言することです。カーソルを宣言するにはDECRARE CURSOR文を使います。

【Oracle】
[DECRARE] CURSOR <カーソル名> (<引数>,<データ型>,・・・) IS <SELECT文>

【SQL Server】
DECRARE <カーソル名> CURSOR FOR <SELECT文>

●.カーソルを開く
カーソルを宣言した後に行うのが、カーソルを開くことです。カーソルを開くにはOPEN文を使います。

【Oracle】
OPEN <カーソル名> (<引数>,・・・)

【SQL Server】
OPEN <カーソル名>

●.カーソルからデータを取り出す
カーソルを開いたら、カーソルからデータを取り出すことができます。カーソルからデータを取り出すには一般に次の作業を行うことが必要です。

(1).LOOP文または、WHILE文を使って、ループ処理を行う。
(2).ループ処理の中で、FETCH文を実行し、カーソルから1行づつデータを取り出す。
(3).最後の行を取り出したら、ループ処理を終了する。

カーソルに蓄えられた結果セットから、1行づつデータを取り出すには、FETCH文を使います。

【Oracle】
FETCH <カーソル名> INTO <変数リスト>

【SQL Server】
FETCH NEXT FROM <カーソル名> INTO <変数リスト>

データを取り出すカーソルの名前と、取り出したデータを代入する変数のリストを指定します。

●.カーソルを閉じる
カーソルの作業が終了したら、カーソルを閉じる必要があります。カーソルを閉じるにはCLOSE文を使います。SQL Serverの場合は、CLOSE文の後にDEALLOCATE文を実行し、メモリ領域を明示的に開放する必要があります。

【Oracle】
CLOSE <カーソル名>

【SQL Server】
CLOSE <カーソル名>
DEALLOCATE <カーソル名>

4.カーソルを使ったストアドプロシージャ

カーソルを使ったストアドプロシージャ実際に作成し実行してみます。まず次のようなテーブルを用意します。

テスト用テーブル
Table1
CREATE TestDB.TABLE TABLE1
(
ID INT,DATA_KBN INT,VAL INT,
)

データ
INSERT INTO TABLE1 VALUES(1,1,100);
INSERT INTO TABLE1 VALUES(2,2,90);
INSERT INTO TABLE1 VALUES(3,3,75);
INSERT INTO TABLE1 VALUES(4,1,28);
INSERT INTO TABLE1 VALUES(5,2,228);
INSERT INTO TABLE1 VALUES(6,3,1278);
INSERT INTO TABLE1 VALUES(7,1,4);
INSERT INTO TABLE1 VALUES(8,2,2800);
INSERT INTO TABLE1 VALUES(9,3,88);

実際のテーブルの中身
Table1

次にカーソルを使ったストアドプロシージャを作成します。コードは以下のとおりとなります。
最初に簡単に説明すると、Table1を読み込み、データ区分(DATA_KBN)が１であるレコードに対して、VALの値に10,000を加算していくとなります。(対象は3レコード)

ストアドプロシージャ名：SP_SAMPLE1

このストアドプロシージャの動きは以下のとおりです。

(1).カーソルの宣言(11~12行目)
カーソルを宣言するとともにSELECT文でTable1の内容を全件全行取得します。

(2).カーソルのオープン(16行目)
カーソルをオープンします。

(3).ループ処理(20~45行目)
WHILE文で繰り返し処理と終了条件を定義します。終了条件は、(@@FETCH_STATUS = 0)で、意味はFETCHステータスが正常である間（すなわちデータがある間）、処理を繰り返すという意味になります。実際の処理は、BEGIN~ENDの間に記述します。
FETCH文では、1行づつつーぶるの内容である結果セットを変数(@ID、@DATA_KBN、@VAL)に代入していきます。
対象データの判定はIF文で記述します。(28行目)、@change_idは後述しますが、実行時にユーザによって指定された値が設定されています。(今回は”1”)、ですのでこのIF文はFETCH文で取り出された1行づつのレコード情報の内、データ区分が”１”であれば、IF文の中の処理を行うことになります。
対象レコードであった場合にまず、IDを退避させます(@TAIHI_ID)。そして、テーブルのVALの値に10000を加算し(34行目)、UPDATE文で更新します。UPDATE文はレコードがユニークになるように、DATA_KBNと退避したIDで判定しています(39行目)。

(4).カーソルを閉じる(51~52行目)
カーソルを閉じ(51行目)、メモリ領域を解放します。(52行目)、メモリの開放はSQL Serverでは必要ですが、Oracleでは必要ありません。

実際の実行
オブジェクトエクスプローラーから実行するストアドプロシージャを指定し、変更するデータ区分の値を指定します。