MySQL | SQLite経験者向け、30日で習得するMySQL：実務SQL力 － Day15 中間課題

SQL MySQL
スポンサーリンク

Day15 後半のゴール

「create_order を頭から最後まで“筋の通った1本の処理”として書けるようにする」

前半で、注文処理に必要なテーブルと「注文確定時に何が起きるか」を分解しました。
後半では、それを実際のストアドプロシージャとして形にしていきます。

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

トランザクション付きの create_order プロシージャの全体像を理解する
在庫チェック → 注文作成 → 在庫更新を“ひとかたまり”として書けるイメージを持つ
エラー時に ROLLBACK して「中途半端な注文」を残さない感覚をつかむ

コードを「丸暗記」する必要はありません。
流れが筋として理解できていれば十分です。

前提となるテーブル構造の再確認

「ヘッダ・明細・商品・ユーザーの役割をもう一度整理する」

まずは、前半で出てきたテーブルをもう一度だけ確認します。

CREATE TABLE users (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE products (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  price     INT          NOT NULL,
  stock_qty INT          NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT          NOT NULL,
  total      INT          NOT NULL,
  created_at DATETIME     NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  order_id   INT          NOT NULL,
  product_id INT          NOT NULL,
  quantity   INT          NOT NULL,
  unit_price INT          NOT NULL,
  CONSTRAINT fk_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_items_product
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;
SQL

ここで意識しておきたいのは、

orders は「1件の注文そのもの」
order_items は「その注文の中身」
products は「商品と在庫」

という役割分担です。

この3つを、トランザクションの中で一気につないでいくのが create_order の仕事になります。

シンプル版 create_order の設計方針

「まずは“1商品だけの注文”に絞って流れを固める」

いきなり「複数商品・カート対応」をやろうとすると、配列や一時テーブルが絡んで難易度が一気に上がります。
ここでは、まず「1回の注文で1種類の商品だけを買う」ケースに絞って、流れを固めます。

今回の create_order は、こういうインターフェースにします。

ユーザーID（user_id）
商品ID（product_id）
数量（quantity）

を受け取って、

在庫が足りていれば注文を作成し、在庫を減らす
在庫が足りなければ注文を作らずにエラー扱いにする

という動きを目指します。

シンプル版 create_order のコード

「トランザクション＋在庫チェック＋INSERT＋UPDATE の一連の流れ」

まずはコードを一気に出します。そのあとで細かく分解します。

DELIMITER //

CREATE PROCEDURE create_order_simple(
  IN  p_user_id    INT,
  IN  p_product_id INT,
  IN  p_quantity   INT
)
BEGIN
  DECLARE v_price     INT;
  DECLARE v_stock     INT;
  DECLARE v_total     INT;
  DECLARE v_order_id  INT;

  START TRANSACTION;

  SELECT
    price,
    stock_qty
  INTO
    v_price,
    v_stock
  FROM products
  WHERE id = p_product_id
  FOR UPDATE;

  IF v_stock < p_quantity THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = '在庫不足のため注文を確定できません';
  END IF;

  SET v_total = v_price * p_quantity;

  INSERT INTO orders (user_id, total, created_at)
  VALUES (p_user_id, v_total, NOW());

  SET v_order_id = LAST_INSERT_ID();

  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (v_order_id, p_product_id, p_quantity, v_price);

  UPDATE products
  SET stock_qty = stock_qty - p_quantity
  WHERE id = p_product_id;

  COMMIT;
END //

DELIMITER ;
SQL

一見長く見えますが、やっていることは前半で分解した流れそのものです。
順番に見ていきます。

在庫チェック部分の深掘り

「FOR UPDATE で“在庫行をロックしてから”判断する」

最初の重要ポイントはここです。

START TRANSACTION;

SELECT
  price,
  stock_qty
INTO
  v_price,
  v_stock
FROM products
WHERE id = p_product_id
FOR UPDATE;
SQL

ここでやっていることは、

トランザクションを開始する
対象商品の価格と在庫数を読み込む
同時に、その商品の行にロックをかける

という3つです。

FOR UPDATE がついているのがポイントで、

このトランザクションが終わるまで、
他のトランザクションはこの商品の行を更新できない

という状態になります。

これによって、

在庫チェックをした直後に、別の注文が在庫を減らしてしまう

といった「すり抜け」を防げます。

在庫数は v_stock に入るので、次のIFでチェックします。

IF v_stock < p_quantity THEN
  ROLLBACK;
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '在庫不足のため注文を確定できません';
END IF;
SQL

ここでは、

在庫が足りなければ、その場でトランザクションを ROLLBACK
同時に SIGNAL でエラーを投げて、呼び出し側に「在庫不足」と伝える

という動きをしています。

SIGNAL SQLSTATE '45000' は「ユーザー定義エラーを投げる」ための書き方です。
アプリ側から見ると、「在庫不足」というメッセージ付きのエラーとして受け取れます。

ここが、実務的にかなり重要なポイントです。

在庫が足りない注文は、
「中途半端な注文を残さず、きれいに失敗させる」

この感覚を、ここでしっかり持っておいてください。

注文ヘッダと明細のINSERT

「LAST_INSERT_ID() で“今作った注文ID”を拾う」

在庫が足りている場合は、注文作成に進みます。

SET v_total = v_price * p_quantity;

INSERT INTO orders (user_id, total, created_at)
VALUES (p_user_id, v_total, NOW());

SET v_order_id = LAST_INSERT_ID();
SQL

ここでは、

単価 × 数量 で合計金額を計算し
orders に1行INSERTし
そのときに採番された注文IDを LAST_INSERT_ID() で取得

という流れです。

LAST_INSERT_ID() は、「このセッションで直前にAUTO_INCREMENTされたID」を返してくれる関数です。
トランザクションの中でも普通に使えます。

続いて、注文明細をINSERTします。

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (v_order_id, p_product_id, p_quantity, v_price);
SQL

ここで、

order_items.order_id に、さっき取得した v_order_id を入れることで、
「この明細は、さっき作った注文に属している」という関係が作られます。

ヘッダと明細が、ここで1本の線でつながります。

在庫更新とCOMMIT

「最後に在庫を減らして、トランザクションを確定させる」

最後のステップがこれです。

UPDATE products
SET stock_qty = stock_qty - p_quantity
WHERE id = p_product_id;

COMMIT;
SQL

ここで、

在庫を注文数量分だけ減らし
全体のトランザクションを COMMIT

しています。

この COMMIT の瞬間に、

orders のINSERT
order_items のINSERT
products のUPDATE

が「まとめて正式に反映される」ことになります。

途中のどこかでエラーが起きていたら、
ROLLBACK されて、これらの変更は全部なかったことになります。

ここまでの流れを、あえて日本語だけで言い直すと、

トランザクション開始
在庫行をロックして在庫チェック
足りなければROLLBACK＋エラー
足りていれば合計金額計算
注文ヘッダINSERT
注文ID取得
注文明細INSERT
在庫UPDATE
COMMIT

という一本のストーリーです。

呼び出しイメージと動作確認

「実際に叩いて“成功パターン／失敗パターン”を体で覚える」

この create_order_simple をどう呼ぶかも、一度イメージしておきましょう。

例えば、products にこんなデータが入っているとします。

INSERT INTO products (name, price, stock_qty)
VALUES ('サンプル商品', 1000, 5);

この状態で、次のように呼び出します。

CALL create_order_simple(1, 1, 2);
SQL

ユーザー1が、商品1を2個買う、という意味です。

在庫は5個あるので、

在庫チェックOK
注文作成
在庫は 5 → 3 に減る

という動きになります。

逆に、在庫3の状態でこう呼ぶとどうなるか。

CALL create_order_simple(1, 1, 10);
SQL

在庫3に対して10個の注文なので、

在庫チェックNG
ROLLBACK
エラー「在庫不足のため注文を確定できません」

という動きになります。

このとき、orders や order_items に中途半端な行が残っていないこと、
products の在庫も変わっていないことを、実際に SELECT して確認すると、
トランザクションの感覚が一気にリアルになります。

ここから先に広げていくとしたら

「複数商品・ログ・トリガー…“実務寄り”への入り口」

今回の create_order_simple は、あくまで「1商品だけ」のシンプル版です。
でも、ここまで書ければ、あとは拡張の方向性が見えてきます。

複数商品に対応するなら、
一時テーブルや JSON で「商品IDと数量の一覧」を渡す

注文確定時にトリガーで監査ログを残すなら、
orders に AFTER INSERT トリガーを付ける

エラーコードやメッセージをもう少し整理するなら、
SIGNAL の SQLSTATE や MESSAGE_TEXT をパターン化する

こういった拡張は、Day15 以降のテーマにもつながっていきます。

大事なのは、

トランザクション
在庫チェック
ヘッダ＋明細のINSERT
在庫更新
エラー時のROLLBACK

という「注文処理の背骨」が、今あなたの中に1本通ったことです。

Day15 後半のまとめ

create_order_simple のようなストアドプロシージャでは、START TRANSACTION から始めて、まず SELECT price, stock_qty ... FOR UPDATE で対象商品の価格と在庫を読みつつ行ロックをかけ、v_stock < p_quantity の場合はその場で ROLLBACK と SIGNAL SQLSTATE '45000' によるエラー送出を行うことで、「在庫が足りない注文は中途半端な状態を一切残さずにきれいに失敗させる」ことができる。
在庫が足りている場合は、単価と数量から合計金額を計算し、orders にINSERTして LAST_INSERT_ID() で採番された注文IDを取得し、それを使って order_items に明細行をINSERTし、最後に products の stock_qty を注文数量分だけ減らして COMMIT することで、「注文ヘッダ」「注文明細」「在庫更新」という3つの更新を1つのトランザクションとしてまとめて確定させられる。
この流れを通して、「注文確定」というビジネス操作を、トランザクション・ロック・ストアドプロシージャを組み合わせて“ひとかたまりの処理”としてDB側に表現できるようになることが、Day1〜Day15で学んできた要素を実務レベルの形に組み上げる中間地点であり、ここから先は「複数商品対応」「監査ログのトリガー」「エラー設計の整理」など、同じ背骨の上に肉付けしていくフェーズに入っていく。

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