Day11 後半のゴール
「ストアドプロシージャの“基本パーツ”を一通り触れる」
前半で「ストアドプロシージャ＝DBの中の関数」というイメージと、CREATE PROCEDURE と CALL の流れを押さえました。
後半のゴールはこうです。
IN / OUT / INOUT パラメータの違いを理解する
プロシージャ内で変数を使うイメージを持つ
IF / CASE で“条件によって処理を変える”形を体験する
ここまで行けば、「とりあえず書ける」状態になります。
パラメータの種類を押さえる
「IN / OUT / INOUT の役割の違い」
前半では IN p_user_id INT だけを使いましたが、
MySQL のストアドプロシージャには3種類のパラメータがあります。
IN
呼び出し側から値を受け取る（読み取り専用）
OUT
プロシージャ側から値を“書き出す”（戻り値のようなもの）
INOUT
呼び出し側から受け取った値を、プロシージャ内で書き換えて返す
まずは IN と OUT の組み合わせを例で見てみます。
例：合計金額を OUT パラメータで返す
「結果セットではなく“1つの値”を返したいとき」
「ユーザーの注文合計金額を1つの数値として返したい」というケースを考えます。
結果セット（行の一覧）ではなく、単純な数値が欲しいイメージです。
DELIMITER //
CREATE PROCEDURE get_user_total(
IN p_user_id INT,
OUT p_total INT
)
BEGIN
SELECT COALESCE(SUM(o.total), 0)
INTO p_total
FROM orders o
WHERE o.user_id = p_user_id;
END //
DELIMITER ;
ここでのポイントは二つです。
OUT p_total INT というパラメータ
プロシージャ内で SELECT ... INTO p_total と書いていること
SELECT ... INTO 変数 は、「SELECT の結果を変数に代入する」という構文です。
この例では、SUM(o.total) の結果を p_total に入れています。
呼び出し側は、こうなります。
SET @total := 0;
CALL get_user_total(1, @total);
SELECT @total;
@total は「セッション変数」で、
プロシージャの外からも見える変数です。CALL の後で @total を見ると、ユーザー1の合計金額が入っています。
「結果セットとして返す」のではなく、
「OUT パラメータに値を詰めて返す」というパターンを、ここで一度体験しておきましょう。
プロシージャ内のローカル変数
「DECLARE と SET で“その中だけの変数”を持つ」
プロシージャの中で一時的に使う変数は、
ローカル変数として宣言できます。
構文はこうです。
DECLARE 変数名 型;
SET 変数名 = 値;
先ほどの例を、ローカル変数を使って書き換えてみます。
DELIMITER //
CREATE PROCEDURE get_user_total2(
IN p_user_id INT,
OUT p_total INT
)
BEGIN
DECLARE v_sum INT;
SELECT SUM(o.total)
INTO v_sum
FROM orders o
WHERE o.user_id = p_user_id;
SET p_total = COALESCE(v_sum, 0);
END //
DELIMITER ;
ここでは、
v_sum がプロシージャ内だけで使うローカル変数p_total が呼び出し側に返す OUT パラメータ
という役割分担になっています。
ローカル変数は、
「途中計算の結果を一時的に持っておきたい」ときに使います。
IF で処理を分岐する
「“条件によってメッセージを変える”シンプルな例」
ストアドプロシージャの強みは、
SQLの中で「条件分岐」ができることです。
IF の基本構文はこうです。
IF 条件 THEN
処理1;
ELSE
処理2;
END IF;
例として、「ユーザーの合計金額に応じてランクを返す」プロシージャを作ってみます。
DELIMITER //
CREATE PROCEDURE get_user_rank(
IN p_user_id INT,
OUT p_rank VARCHAR(20)
)
BEGIN
DECLARE v_total INT;
SELECT COALESCE(SUM(o.total), 0)
INTO v_total
FROM orders o
WHERE o.user_id = p_user_id;
IF v_total >= 100000 THEN
SET p_rank = 'GOLD';
ELSEIF v_total >= 50000 THEN
SET p_rank = 'SILVER';
ELSE
SET p_rank = 'BRONZE';
END IF;
END //
DELIMITER ;
呼び出し側はこうです。
SET @rank := '';
CALL get_user_rank(1, @rank);
SELECT @rank;
ここでやっていることは、
アプリ側のコードで書く if 文とほぼ同じです。
「合計金額を計算する」
「その値に応じてランクを決める」
という2ステップを、DB側に寄せています。
CASE を使った書き方
「IF が増えてきたら CASE で整理する」
IF が増えてくると、
ネストが深くなって読みにくくなります。
そんなときは CASE を使うとスッキリします。
さっきのランク判定を CASE で書くと、こうなります。
SET p_rank = CASE
WHEN v_total >= 100000 THEN 'GOLD'
WHEN v_total >= 50000 THEN 'SILVER'
ELSE 'BRONZE'
END;
IF でも CASE でも結果は同じですが、
「値に応じて分岐する」パターンは CASE の方が読みやすいことが多いです。
Day11 の段階では、
IF は「条件が少ないとき」
CASE は「値に応じた分岐が増えてきたとき」
くらいの感覚で使い分けられれば十分です。
トランザクションとストアドプロシージャの関係
「“ひとかたまりの処理”をDB側で完結させる準備」
本格的なトランザクション制御は Day12 でやりますが、
ストアドプロシージャとトランザクションは相性が良い、という話だけ触れておきます。
例えば、
注文を作る
在庫を減らす
ログを残す
という3つの操作を、
1つのプロシージャの中でこう書けます。
BEGIN
START TRANSACTION;
-- 注文INSERT
-- 在庫UPDATE
-- ログINSERT
COMMIT;
END
途中でエラーが起きたら ROLLBACK する、という形も取れます。
重要なのは、
「この3つは絶対にセットで成功するか、全部失敗するかにしたい」
という“ひとかたまりの処理”を、
ストアドプロシージャの中に閉じ込められる、ということです。
SQLite 時代は、
こういう制御をアプリ側でやっていたはずですが、
MySQL では「DB側に寄せる」という選択肢が増えます。
どこまでストアドに寄せるかという感覚
「“DBに置いた方が一貫性を守りやすい処理”から乗せていく」
最後に、設計の感覚の話を少しだけ。
ストアドプロシージャは強力ですが、
何でもかんでも入れると、逆に管理がつらくなります。
DB側に寄せると気持ちいい処理は、だいたいこんな性質を持っています。
複数テーブルにまたがる更新を、必ず同じ順番・同じルールでやりたい
どのアプリから呼ばれても、同じバリデーション・同じログ記録をしたい
セキュリティ的に「この入り口からしか更新させたくない」
逆に、
画面ごとの細かい表示ロジックや、
UIに近い条件分岐は、アプリ側に置いておいた方が読みやすいことが多いです。
Day11 の時点では、
ストアドプロシージャ＝「DB側に置く、共通の入り口」
というイメージを持ちつつ、
「どこまで寄せるか」は少しずつ探っていく、くらいで十分です。
Day11 後半のまとめ
ストアドプロシージャのパラメータには、呼び出し側から値を受け取る IN、プロシージャ側から値を書き出す OUT、その両方を兼ねる INOUT があり、OUT p_total のようなパラメータに対して SELECT ... INTO p_total と書くことで、集計結果などの“1つの値”を戻り値のように返すことができる。
プロシージャ内では DECLARE v_sum INT; のようにローカル変数を宣言し、SET v_sum = ...; で代入しながら処理を組み立てられ、さらに IF ... THEN ... ELSEIF ... ELSE ... END IF; や CASE WHEN ... THEN ... END を使うことで、「合計金額に応じてランクを決める」といったアプリ側の if 文に相当するロジックをDB側に寄せることができる。
こうして「複数のSQL＋条件分岐＋（次回扱う）トランザクション制御」を1つのストアドプロシージャにまとめることで、「この処理は必ずこの順番・このルールで実行される」という一貫性をDB側で保証できるようになり、特に複数テーブルにまたがる更新や、セキュリティ的に“この入り口からしか触らせたくない処理”をストアドに乗せると、設計として気持ちよく回り始める。