SQLのカーソルについてまとめています。
SQLのカーソルの使い方サンプル集
SQLのCURSORは、SQL標準（SQL92）で定義されています。そのため、主要データベースではCURSORまわりの記述方法はほぼ同じになっています。
【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？
MySQLのカーソルコードサンプル
カーソルを同時に２つ使用して、ループ処理をおこなうサンプルです。
ストアドプロシジャとして作成する箇所など、一部MySQL独自の部分があります。
DELIMITER //
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;
//
DELIMITER ;
【関連記事】
▶SQLのfetchは、select結果を1件づつ取り出す NOT FOUND判定はDB差あり
カーソルの DECLARE
カーソル名前と、紐付けするselectクエリを宣言します。
DECLARE cursor_name CURSOR FOR select_statement
なお、宣言直後のカーソルはCLOSEされた状態になっています。
DECLARE CURSORを実行しても、SQL STATE値は返されません。そのため、宣言が成功したかどうかをリターンコードで判定することはできません。
複数のカーソルを同時にDECLAREで宣言しておくことが可能です。
カーソルの OPEN
宣言済みのカーソルをオープンします。事前にDeclareで宣言しておく必要があります。
OPEN cursor_name
FETCHでデータを取得する前に、OPENを実行しないとデータの取得ができません。
カーソルの FETCH
指定のカーソル位置に対応するデータを1件取得します。
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
データをプログラム的に1件ずつループさせて処理することが可能です。しかし、大量データをバッチ処理のように加工しようとすると実行速度が非常に遅いことが多いです。対象データが多い場合は、インデックスを使ったSQLで代替処理できないかどうか検討してみるのが良いでしょう。
カーソルの CLOSE
CLOSE cursor_name
オープン済みのカーソルをクローズします。CLOSE文でクローズされない場合は、カーソルが宣言されたBEGIN~ENDブロックの最後に閉じられます。
しかし、意図しない不具合を避けるために、明示的にCLOSEを使用するのが良いでしょう。
OracleのPL/SQLのカーソルコードサンプル
OralcenoPL/SQLでカーソルを使うサンプルです。
社員全員に、カーソルを使ったループで賞与（bonus）を割り当てています。ループ内では、フェッチの結果をチェックしてデータが返ってこなかったらループを抜ける処理をおこなっています。
1 DECLARE 2 l_total INTEGER := 10000; 3 4 CURSOR employee_id_cur 5 IS 6 SELECT employee_id 7 FROM plch_employees 8 ORDER BY salary ASC; 9 10 l_employee_id employee_id_cur%ROWTYPE; 11 BEGIN 12 OPEN employee_id_cur; 13 14 LOOP 15 FETCH employee_id_cur INTO l_employee_id; 16 EXIT WHEN employee_id_cur%NOTFOUND; 17 18 assign_bonus (l_employee_id, l_total); 19 EXIT WHEN l_total <= 0; 20 END LOOP; 21 22 CLOSE employees_cur; 23 END;
SQLServerのカーソルコードサンプル
SQLServerのカーソルサンプル。カーソルから1件データを読み込むシンプルならサンプルです。
DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;
DECLARE CURSOR (Transact-SQL) – SQL Server | Microsoft Docs
PostgreSQLのカーソルコードサンプル
関数定義を使ってデータをフェッチするサンプル。FETCH ALLで全ての行を取得しています。
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
また、以下はFORとLOOPを使ってFETCHでループしながら、取得したデータを表示するサンプルコード。
PostgreSQLではループ部分をシンプルに記述できます。
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE
('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
結果:
Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...
Name = Walsh, Job = SH_CLERK
まとめ
- CURSOR（カーソル）は、クエリの結果を1件ずつ取得する
- FETCHとループ処理を組み合わせることで、バッチ処理が可能
- 大容量データの処理にFETCHを使うと、速度が遅くなるケースあり
