SQLカーソルのサンプルコード LOOPと組み合わせてバッチ処理が可能

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;

Part 12：カーソルの操作 | Oracle 日本

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;

PostgreSQL 公式：カーソル

また、以下は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を使うと、速度が遅くなるケースあり