はじめに
以下の記事に続いて、カーソルについて復習した結果を記事としてまとめてみました。
使用した環境
- Oracle社が提供しているOracle Live SQLでOracle19cを利用しました。
カーソルとは
- データの「検索条件」と「現在位置」を保持して、複数の検索結果を1件ずつ処理するための仕組みのことです。
- 平たく言えば、「検索条件に合致するレコードを、1件ずつ取り出すための仕組み」となります。
作成したSQL
基本的なカーソル処理
- 変数と同様に、カーソルも宣言部で宣言する必要がありますが、データ型の代わりに検索条件（SELECT文）を指定します。
- カーソルの宣言を見れば、カーソルにどのようなデータが格納されるのかが分かります。
- カーソルの宣言と併せて、カーソルの1行分のデータを代入する変数を宣言します。
- 結果セットとは「メモリ上に保持された検索結果」のことです。
-
OPEN {カーソル名};で、カーソルに紐づいたSELECT文を実行して、カーソルに結果セットを保持します。 -
FETCH {カーソル名} INTO {変数名};で、カーソルの結果セットから1行読み込んで変数に代入します。 -
EXIT WHEN {終了条件};は、ループ処理の終了条件を示しています。 - SELECT文に対する結果セットが存在しない場合、あるいは結果セットに読み込むべき行が無くなった場合にはループ処理を抜けます。
- 最後にカーソルを閉じて、カーソルが保持していたメモリを開放します。
基本的なカーソル処理
-- 宣言部
DECLARE
-- カーソルの宣言
CURSOR cur IS
SELECT
T1.DNAME,
T2.ENAME,
T2.JOB
FROM
SCOTT.DEPT T1,
SCOTT.EMP T2
WHERE
T1.DEPTNO = T2.DEPTNO;
-- カーソルの結果セットの1行分を代入する変数
dept_row cur%ROWTYPE;
-- 処理部
BEGIN
-- カーソルを開く
OPEN cur;
LOOP
-- 結果セットを変数に代入する
FETCH cur INTO dept_row;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_row.DNAME || ',' || dept_row.ENAME || ',' || dept_row.JOB);
END LOOP;
-- カーソルを閉じる
CLOSE cur;
END;
実行結果
ACCOUNTING,CLARK,MANAGER
ACCOUNTING,MILLER,CLERK
ACCOUNTING,KING,PRESIDENT
RESEARCH,FORD,ANALYST
RESEARCH,SCOTT,ANALYST
RESEARCH,JONES,MANAGER
RESEARCH,SMITH,CLERK
RESEARCH,ADAMS,CLERK
SALES,WARD,SALESMAN
SALES,MARTIN,SALESMAN
SALES,TURNER,SALESMAN
SALES,JAMES,CLERK
SALES,ALLEN,SALESMAN
SALES,BLAKE,MANAGER
暗黙カーソル
- 宣言部でカーソルを定義せずに、for文の「制御リスト」にSELECT文を書くことで、ループ変数に結果セットの1行分を代入します。
- SELECT文に対する結果セットが存在しない場合には、ループ処理は発生しません。
- 例えば、下記のSQL中のSELECT文に
T1.DEPTNO > 100という検索条件を追加すると、SELECT文の実行結果として結果セットが1つも返されないため、ループ処理は発生しません。
暗黙カーソル
-- 宣言部
DECLARE
-- 処理部
BEGIN
FOR cur IN (
SELECT
T1.DNAME,
T2.ENAME,
T2.JOB
FROM
SCOTT.DEPT T1,
SCOTT.EMP T2
WHERE
T1.DEPTNO = T2.DEPTNO
) LOOP
DBMS_OUTPUT.PUT_LINE(cur.DNAME || ',' || cur.ENAME || ',' || cur.JOB);
END LOOP;
END;
実行結果
ACCOUNTING,CLARK,MANAGER
ACCOUNTING,MILLER,CLERK
ACCOUNTING,KING,PRESIDENT
RESEARCH,FORD,ANALYST
RESEARCH,SCOTT,ANALYST
RESEARCH,JONES,MANAGER
RESEARCH,SMITH,CLERK
RESEARCH,ADAMS,CLERK
SALES,WARD,SALESMAN
SALES,MARTIN,SALESMAN
SALES,TURNER,SALESMAN
SALES,JAMES,CLERK
SALES,ALLEN,SALESMAN
SALES,BLAKE,MANAGER