MySQL | SQLite経験者向け、30日で習得するMySQL：実務SQL力 － Day12 ストアドプロシージャ②

SQL MySQL
スポンサーリンク

Day12 後半のゴール

「“テーブルの行を1件ずつ処理する”イメージを持つ」

前半で IF / CASE と WHILE の形はつかめました。
後半では、いよいよ「実際のテーブルの行を1件ずつ処理する」世界に入ります。

ここでのゴールはこうです。

カーソル（CURSOR）という仕組みの役割を理解する
カーソル＋ループで「1行ずつ処理する」流れをイメージできる
“ループでやるべきこと／やるべきでないこと”の線引きを持つ

ここまで行けば、「SQLの中でプログラムを書く」感覚がかなりはっきりしてきます。

カーソルとは何か

「SELECT の結果を“1行ずつ読むためのハンドル”」

まず、カーソルのイメージから。

普通の SELECT は「結果セットを一気に返す」ものです。
一方、ストアドプロシージャの中で「1行ずつ処理したい」ときには、

結果セットを1行ずつ順番に取り出す仕組み

が必要になります。
その役割を持つのがカーソル（CURSOR）です。

イメージとしては、

SELECT の結果に対して「しおり」を挟んでおき、
FETCH で1行ずつ読み進めていく

という感じです。

カーソル＋ループの基本パターン

「DECLARE → OPEN → FETCH → CLOSE の4ステップ」

カーソルを使うときの流れは、だいたいこうなります。

カーソルを宣言する（DECLARE CURSOR）
カーソルを開く（OPEN）
ループの中で1行ずつFETCHする
読み終わったらカーソルを閉じる（CLOSE）

これを、実際のコードで見ていきます。

例：未処理のログを1件ずつ処理済みにする

「“1行ずつUPDATEする”典型パターン」

次のようなテーブルを想像します。

CREATE TABLE process_logs (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  message   VARCHAR(255) NOT NULL,
  processed TINYINT(1)   NOT NULL DEFAULT 0
) ENGINE=InnoDB;
SQL

やりたいことは、

processed = 0 の行を1件ずつ取り出して、
何か処理をしたあと、processed を 1 に更新する

というものです。

実務では「何か処理」の部分に、
外部API呼び出しや、別システムへの連携などが入るイメージです。

これをカーソル＋ループで書くと、こうなります。

DELIMITER //

CREATE PROCEDURE process_logs_one_by_one()
BEGIN
  DECLARE v_id      INT;
  DECLARE v_message VARCHAR(255);
  DECLARE done      TINYINT(1) DEFAULT 0;

  DECLARE cur_logs CURSOR FOR
    SELECT id, message
    FROM process_logs
    WHERE processed = 0
    ORDER BY id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET done = 1;

  OPEN cur_logs;

  read_loop: WHILE done = 0 DO
    FETCH cur_logs INTO v_id, v_message;

    IF done = 1 THEN
      LEAVE read_loop;
    END IF;

    UPDATE process_logs
      SET processed = 1
    WHERE id = v_id;
  END WHILE;

  CLOSE cur_logs;
END //

DELIMITER ;
SQL

一気に情報量が増えたので、重要なところを分解していきます。

CONTINUE HANDLER と done フラグ

「“もう行がない”を検知する仕組み」

カーソルで一番つまずきやすいのがここです。

DECLARE done TINYINT(1) DEFAULT 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET done = 1;
SQL

これは、

FETCH したときに「もう取る行がない」状態になったら、
done というフラグを 1 にセットする

という意味です。

NOT FOUND は「結果が見つからなかった」という状態を表すシグナルで、
カーソルで FETCH したときに「もう行がない」ときに発生します。

このハンドラのおかげで、

FETCH したあとに done を見れば、
「まだ行があるか」「もう終わりか」が分かる

という仕組みになっています。

カーソルの宣言とOPEN

「“どのSELECT結果を1行ずつ読むか”を決める」

次の部分を見てみます。

DECLARE cur_logs CURSOR FOR
  SELECT id, message
  FROM process_logs
  WHERE processed = 0
  ORDER BY id;
SQL

ここで、

cur_logs という名前のカーソルを宣言し、
「processed = 0 のログを id 順に読む」というSELECTを紐づけています。

そのあとで、

OPEN cur_logs;
SQL

とすることで、
このカーソルを「読み取り可能な状態」にします。

ここまでで、

「未処理ログの結果セットにしおりを挟んだ状態」

ができあがったイメージです。

FETCH とループ本体

「1行ずつ取り出して、処理して、次へ進む」

ループ部分をもう一度見てみます。

read_loop: WHILE done = 0 DO
  FETCH cur_logs INTO v_id, v_message;

  IF done = 1 THEN
    LEAVE read_loop;
  END IF;

  UPDATE process_logs
    SET processed = 1
  WHERE id = v_id;
END WHILE;
SQL

ここで起きていることは、こうです。

WHILE の条件は「done = 0」、つまり「まだ終わっていない」間ループする
FETCH で、次の1行を v_id, v_message に読み込む
もし行がもうなければ、さっきのハンドラが動いて done = 1 になる
done が 1 なら LEAVE でループを抜ける
そうでなければ、その行に対して UPDATE を実行する

この「done フラグ＋FETCH＋LEAVE」の組み合わせが、
カーソル＋ループの基本パターンです。

CLOSE でカーソルを閉じる

「開いたら必ず閉じる、をセットで覚える」

最後に、

CLOSE cur_logs;
SQL

でカーソルを閉じています。

カーソルは「サーバー側のリソース」を使うので、
開いたら必ず閉じる、をセットで覚えておくのが大事です。

プログラミングでいう「ファイルを開いたら閉じる」と同じ感覚です。

ループ＋カーソルをどこで使うか

「“どうしても1行ずつ外部とやり取りしたい”場面」

ここまで見ると、こう思うかもしれません。

「これ、UPDATE 1発でよくない？」

たとえば、さっきの例は本当はこう書けます。

UPDATE process_logs
SET processed = 1
WHERE processed = 0;
SQL

その通りです。
「単にフラグを立てるだけ」なら、カーソルは不要です。

カーソル＋ループが必要になるのは、
だいたいこんな場面です。

各行ごとに外部APIを叩く必要がある
各行ごとに複雑なロジックを実行したい
各行ごとに別のテーブルにINSERTするなど、処理内容がバラバラ

つまり、

「1行ずつ“何か特別なこと”をしたい」

ときにだけ、カーソル＋ループを検討すれば十分です。

条件分岐とループを組み合わせる

「“特定条件の行だけ特別扱いする”パターン」

カーソル＋ループの中で、IF を使うと、
「特定条件の行だけ特別扱いする」ことができます。

さっきの例を少し変えて、

message に “ERROR” を含むログだけ、
別テーブルにコピーしてから processed を 1 にする

という処理を考えてみます。

DELIMITER //

CREATE PROCEDURE process_error_logs()
BEGIN
  DECLARE v_id      INT;
  DECLARE v_message VARCHAR(255);
  DECLARE done      TINYINT(1) DEFAULT 0;

  DECLARE cur_logs CURSOR FOR
    SELECT id, message
    FROM process_logs
    WHERE processed = 0
    ORDER BY id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET done = 1;

  OPEN cur_logs;

  read_loop: WHILE done = 0 DO
    FETCH cur_logs INTO v_id, v_message;

    IF done = 1 THEN
      LEAVE read_loop;
    END IF;

    IF v_message LIKE '%ERROR%' THEN
      INSERT INTO error_logs (log_id, message)
      VALUES (v_id, v_message);
    END IF;

    UPDATE process_logs
      SET processed = 1
    WHERE id = v_id;
  END WHILE;

  CLOSE cur_logs;
END //

DELIMITER ;
SQL

ここでは、

カーソルで1行ずつ読みつつ
IF で「ERROR を含むかどうか」を判定し
含む場合だけ別テーブルにINSERTする

という形になっています。

このように、

カーソル（1行ずつ読む）
＋
IF / CASE（条件分岐）

を組み合わせることで、
かなり柔軟な処理が書けるようになります。

セキュリティ・運用の視点からの注意

「“重いループ”をDBに押し込むときは慎重に」

最後に、セキュリティ・運用の視点も少しだけ。

ストアドプロシージャ＋ループは強力ですが、
DBサーバーの中で「重い処理」を長時間回すことにもなり得ます。

大量の行をカーソルで1件ずつ処理する
外部API呼び出しを大量に行う
トランザクションを長時間握ったままループする

こういった処理は、

他のクエリをブロックしたり
DBサーバーの負荷を一気に上げたり

するリスクがあります。

実務では、

本当にDBの中でやるべき処理か
バッチサーバーやアプリ側に出した方がよくないか
1回の実行で何件まで処理するか（小分けにできないか）

といった観点も、必ずセットで考えます。

「DBにロジックを寄せる」のは強い選択ですが、
同時に「DBに負荷を集中させる」ことにもなるので、
そこは意識的でいた方がいいです。

Day12 後半のまとめ

テーブルの行を1件ずつ処理したいとき、ストアドプロシージャではカーソル（CURSOR）を使い、DECLARE cur CURSOR FOR SELECT ... で「どのSELECT結果を1行ずつ読むか」を宣言し、OPEN cur で開き、FETCH cur INTO 変数... をループの中で繰り返し、最後に CLOSE cur で閉じる、という「DECLARE → OPEN → FETCH → CLOSE」の流れが基本パターンになる。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; のようなハンドラを用意しておくことで、「FETCHしたときにもう行がなかった」タイミングで done フラグを立てられ、WHILE done = 0 のループの中で FETCH → IF done = 1 THEN LEAVE → 行ごとの処理、という形で「結果セットを最後まで順番に処理する」ことができる。
カーソル＋ループは、単にフラグをまとめて更新するような処理には不要であり、「各行ごとに外部APIを叩く」「各行ごとに別テーブルへINSERTする」「各行ごとに複雑な条件分岐をしたい」といった“1行ずつ特別なことをしたい”場面にだけ使うのがよく、同時に「大量行を長時間ループさせてDBに負荷を集中させていないか」「本当にDB内でやるべき処理か」を意識することが、実務でストアドプロシージャを安全に使ううえで重要になる。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました