カーソルで1レコードずつ処理(CURSOR,OPEN,FETCH,CLOSE)

2022/11/13

カーソル(cursor)を利用すると、SELECTで取得したレコードをループ処理で1レコードずつ処理することができます。ここでは、カーソルを利用したプロシージャの作成方法を紹介します。

行いたいこと

sampleテーブル に格納されてるレコードを1レコードずつ処理して、sample_aテーブルとsample_bテーブルにレコード挿入するプロシージャを定義してみます。

カーソルを利用したプロシージャを定義

DELIMITER //

-- プロシージャ定義
CREATE PROCEDURE test_insert_proc()
BEGIN
  -- 変数を宣言(カーソル定義前に行う必要あり)
  -- カーソルから読み出した値を格納する変数を宣言
  DECLARE currentId INT;
  DECLARE currentA VARCHAR(255);
  DECLARE currentB VARCHAR(255);

  -- カーソルがデータセットの最後に達したか判定するための変数を宣言
  DECLARE done INT DEFAULT FALSE;

  -- カーソルを定義
  DECLARE myCursor CURSOR FOR
    SELECT `id`, `a`, `b` 
    FROM `sample`;
    
  -- カーソルがデータセットの最後に達したときの動作を制御  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- カーソルをオープン
  OPEN myCursor;
  
  -- ループで1行ずつ処理
  read_loop: LOOP
    -- カーソルから1行読み出し
    FETCH myCursor INTO currentId, currentA, currentB;

    -- カーソルからの読み出しが最後に達していればループを抜ける
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- 別テーブルにインサート
    INSERT INTO `sample_a` VALUES (currentId, currentA);
    INSERT INTO `sample_b` VALUES (currentId, currentB);
  END LOOP;
  
  -- カーソルを閉じる
  CLOSE myCursor;
END//

DELIMITER ;

プロシージャ呼び出し

呼び出し前の状態

mysql> SELECT * FROM `sample`;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 | aaa  | 111  |
|  2 | bbb  | 222  |
|  3 | ccc  | 333  |
+----+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `sample_a`;
Empty set (0.00 sec)

mysql> SELECT * FROM `sample_b`;
Empty set (0.01 sec)

プロシージャ呼び出し

mysql> CALL test_insert_proc();
Query OK, 0 rows affected (0.01 sec)

呼び出し後の状態

mysql> SELECT * FROM `sample`;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 | aaa  | 111  |
|  2 | bbb  | 222  |
|  3 | ccc  | 333  |
+----+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `sample_a`;
+----+------+
| id | a    |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `sample_b`;
+----+------+
| id | b    |
+----+------+
|  1 | 111  |
|  2 | 222  |
|  3 | 333  |
+----+------+
3 rows in set (0.00 sec)