PL/SQL カーソルを使用してSELECT結果のループ処理を行う
PL/SQLでカーソルを使用してSELECT結果の件数分ループ処理を行う方法を紹介します。
サンプルのSQLはPL/SQL SELECTした結果を変数にセットするで扱ったものに対して修正しています。
以下のテーブルがあるとします。
| id | val |
|---|---|
| 1 | 3 |
| 1 | 4 |
valの値をループ処理で集計します。
カーソルではなくGROUP BYを使用して簡単に集計できますが、説明の都合であえてカーソルを使用しています。
CREATE OR REPLACE PROCEDURE FOOBAR(IN_PARAM1 IN CHAR, IN_PARAM2 IN NUMBER, OUT_PARAM1 OUT CHAR) AS
/*変数を宣言*/
MON NUMBER := 0;
NEW_DATE CHAR(8);
/*カーソルを宣言*/
CURSOR C_CONTROL IS
SELECT
val
FROM
control
WHERE
id = 1
;
L_CONTROL C_CONTROL%ROWTYPE; /*カーソルのROWTYPEで宣言*/
BEGIN
OPEN C_CONTROL; /*カーソルオープン*/
LOOP /*ループ処理（無限ループ）*/
FETCH C_CONTROL INTO L_CONTROL; /*１行読込*/
EXIT WHEN C_CONTROL%NOTFOUND; /*レコードがない場合はループを抜ける*/
MON := MON + L_CONTROL.val; /*valを集計*/
END LOOP;
CLOSE C_CONTROL; /*カーソルクローズ*/
IF IN_PARAM2 = 1 THEN
NEW_DATE := TO_CHAR(ADD_MONTHS(TO_DATE(IN_PARAM1), MON), 'YYYYMMDD');
ELSIF IN_PARAM2 = 2 THEN
NEW_DATE := TO_CHAR(ADD_MONTHS(TO_DATE(IN_PARAM1), -MON), 'YYYYMMDD');
ELSE
NEW_DATE := NULL;
END IF;
OUT_PARAM1 := NEW_DATE;
END;
/OPEN
OPEN カーソル名でカーソルに定義したSQLが実行され、結果がメモリに保持されます。（結果セットと呼ぶことが多いです）
FETCH
FETCH カーソル名 INTO 変数名でOPENされたカーソルの結果セットを１行だけ変数にセットします。
そのため、変数にはカーソルのSQLで取得したレコードのROWTYPE型がセットされるので、変数はカーソルのROWTYPE型で定義することになります。
最初のFETCHは１行目のレコードがセットされ、２回目にFETCHを行うと２行目のレコードがセットされます。
最終レコードまでFETCHした状態でもう１度FETCHを行うと、カーソル名%NOTFOUNDの判定がTRUEになります。
LOOP
LOOPとEND LOOPで囲ってループ処理を行えます。
LOOP処理で１回ずつFETCHしていき、最終レコードまで１レコードごとに何かしらの処理を行うことが、よくあるカーソルの使い方です。
EXITなどでループを抜ける条件を指定しないと無限ループになってしまいます。
上記例の場合は最終レコードまでループ処理を行いたいので、カーソル名%NOTFOUNDがTRUEになったらEXITするようにしています。
CLOSE
CLOSE カーソル名でメモリに保持していた結果セットを削除します。