MySQLで学ぶ受注管理システム開発（3）「ビューとストアドプロシージャを作ろう」

開発・動作確認環境：GitHub Codespaces（本ブログで構築した環境を使用）

はじめに
ビューとストアドプロシージャの違い
ビューを作ろう
1. 注文明細の結合ビュー
2. 顧客別の注文集計ビュー
ストアドプロシージャを作ろう
1. 顧客の注文履歴を取得するプロシージャ
2. 注文登録プロシージャ（トランザクション処理を共通化する）
動作確認
GitHubへの反映
まとめ
コードのダウンロード
1. 関連記事
2. 関連

はじめに

前回は、「MySQLで学ぶ受注管理システム開発（2）「CRUD操作を極めよう」」にて、INSERT・UPDATE・DELETEとトランザクションの実践を行った。

前回のSQLを振り返ると、「顧客名・商品名・注文明細を結合して表示する」「注文をトランザクションでまとめて登録する」といった処理を、毎回SELECT文やSQL文の並びを手で書いていたことに気づくだろう。
今回は、こうしたよく使う処理をあらかじめデータベース側に定義しておく、ビュー（VIEW）とストアドプロシージャを学ぼう。

ビューとストアドプロシージャの違い

どちらも「よく使うSQL処理に名前を付けて再利用できるようにする」という点では似ているが、役割が異なる。

ビュー（VIEW）
SELECT文に名前を付けたもの。実体を持たない「仮想的な表」であり、普段のテーブルと同じようにSELECTできる

ストアドプロシージャ
一連のSQL処理（複数のSELECT・INSERT・UPDATEや、条件分岐）に名前を付けたもの。CALLで呼び出す

簡単に言えば、「データを見るだけ」ならビュー、「複数の処理をまとめて実行する」ならストアドプロシージャ、という使い分けになる。

ビューを作ろう

注文明細の結合ビュー

前回・前々回で何度も書いてきた「注文・顧客・商品を結合して表示する」SELECT文を、ビューとして定義しておこう。

USE order_db;

CREATE VIEW order_full_details AS
SELECT
    o.order_id,
    o.order_date,
    o.status,
    c.customer_id,
    c.customer_name,
    p.product_id,
    p.product_name,
    od.quantity,
    od.unit_price_at_order,
    (od.quantity * od.unit_price_at_order) AS subtotal
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;

-- 通常のテーブルと同じようにSELECTできる
SELECT * FROM order_full_details WHERE customer_id = 'C001';

実行例

mysql> SELECT * FROM order_full_details WHERE customer_id = 'C001';
+----------+---------------------+-----------+-------------+---------------+------------+--------------------+----------+----------------------+----------+
| order_id | order_date          | status    | customer_id | customer_name | product_id | product_name       | quantity | unit_price_at_order  | subtotal |
+----------+---------------------+-----------+-------------+---------------+------------+--------------------+----------+----------------------+----------+
| O003     | 2026-01-10 10:00:00 | キャンセル| C001        | 山田太郎      | P002       | ワイヤレスマウス   |        1 |              2500.00 |  2500.00 |
+----------+---------------------+-----------+-------------+---------------+------------+--------------------+----------+----------------------+----------+
1 row in set (0.00 sec)

第2回でO001は削除済み（DELETE）、O004はC004の注文であるため、C001の注文としてはO003（キャンセル済み）の1件だけが該当する。

一度作ってしまえば、以降は複雑なJOINを毎回書かずに、order_full_detailsという1つのテーブルであるかのように扱える。

顧客別の注文集計ビュー

続けて、顧客ごとの注文回数・合計金額を集計するビューも作成しておこう。

CREATE VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    COALESCE(SUM(od.quantity * od.unit_price_at_order), 0) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status <> 'キャンセル'
LEFT JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.customer_name;

SELECT * FROM customer_order_summary ORDER BY total_amount DESC;

実行例

mysql> SELECT * FROM customer_order_summary ORDER BY total_amount DESC;
+-------------+---------------+-------------+--------------+
| customer_id | customer_name | order_count | total_amount |
+-------------+---------------+-------------+--------------+
| C004        | 高橋健太      |           1 |      7600.00 |
| C002        | 佐藤花子      |           1 |      6000.00 |
| C001        | 山田太郎      |           0 |         0.00 |
| C003        | 鈴木一郎      |           0 |         0.00 |
+-------------+---------------+-------------+--------------+
4 rows in set (0.00 sec)

ポイント
status <> 'キャンセル'という条件をJOINの中に入れているため、キャンセルされた注文は集計から除外される。前回学んだ論理削除（ステータス変更）の考え方が、こういう集計の場面でも活きてくる。

ストアドプロシージャを作ろう

顧客の注文履歴を取得するプロシージャ

「特定の顧客の注文履歴を見たい」という処理を、引数付きのストアドプロシージャにしておこう。

DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN p_customer_id VARCHAR(10))
BEGIN
    SELECT order_id, order_date, status, product_name, quantity, subtotal
    FROM order_full_details
    WHERE customer_id = p_customer_id
    ORDER BY order_date DESC;
END //

DELIMITER ;

CALL GetCustomerOrders('C001');

実行例

mysql> CALL GetCustomerOrders('C001');
+----------+---------------------+-----------------+--------------------------+----------+----------+
| order_id | order_date          | status          | product_name             | quantity | subtotal |
+----------+---------------------+-----------------+--------------------------+----------+----------+
| O003     | 2026-07-29 10:16:54 | キャンセル      | ワイヤレスマウス         |        1 |  2500.00 |
+----------+---------------------+-----------------+--------------------------+----------+----------+
1 row in set (0.01 sec)

ポイント
DELIMITERをいったん//に変更しているのは、ストアドプロシージャの中に;（本来の区切り文字）を複数含められるようにするためである。プロシージャ定義が終わったら、DELIMITER ;で元に戻すのを忘れないようにしよう。

注文登録プロシージャ（トランザクション処理を共通化する）

前回、「注文ヘッダー登録 → 明細登録 → 在庫減算」という3ステップを、START TRANSACTION〜COMMITで手作業で書いた。この一連の流れをストアドプロシージャにまとめ、在庫不足時は自動でロールバックするようにしよう。

DELIMITER //

CREATE PROCEDURE CreateOrder(
    IN p_order_id VARCHAR(10),
    IN p_customer_id VARCHAR(10),
    IN p_product_id VARCHAR(10),
    IN p_quantity INT
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_price DECIMAL(10, 2);

    -- エラー発生時は自動的にロールバックする
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- 在庫と単価を確認
    SELECT stock_quantity, unit_price INTO v_stock, v_price
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE;

    -- 在庫不足の場合はエラーを発生させる
    IF v_stock < p_quantity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '在庫が不足しています';
    END IF;

    -- 注文ヘッダーを登録
    INSERT INTO orders (order_id, customer_id, status)
    VALUES (p_order_id, p_customer_id, '受付');

    -- 注文明細を登録
    INSERT INTO order_details (order_id, product_id, quantity, unit_price_at_order)
    VALUES (p_order_id, p_product_id, p_quantity, v_price);

    -- 在庫を減算
    UPDATE products
    SET stock_quantity = stock_quantity - p_quantity
    WHERE product_id = p_product_id;

    COMMIT;
END //

DELIMITER ;

ポイント

DECLARE EXIT HANDLER FOR SQLEXCEPTIONは、プロシージャ内でエラーが発生した際に自動的にROLLBACKする仕組みである。前回は「アプリケーション側で在庫をチェックしてからROLLBACKする」という手動の判断だったが、今回はデータベース側でこの判断を一元化できる
SIGNAL SQLSTATE '45000'は、意図的にエラーを発生させる文である。「在庫が足りない」という業務ルール上のエラーを、MySQLの仕組みとして表現している
FOR UPDATEは、在庫を確認している間に他の注文が同時に割り込んで在庫を減らしてしまう（同時実行による不整合）ことを防ぐための行ロックである

呼び出してみよう。

CALL CreateOrder('O005', 'C003', 'P002', 3);

実行例

mysql> CALL CreateOrder('O005', 'C003', 'P002', 3);
Query OK, 1 row affected (0.02 sec)

在庫が足りないケースも試してみる。

CALL CreateOrder('O006', 'C003', 'P001', 9999);

実行例

mysql> CALL CreateOrder('O006', 'C003', 'P001', 9999);
ERROR 1644 (45000): 在庫が不足しています

意図した通りエラーとなり、注文ヘッダーも明細も登録されないことが確認できる。

動作確認

作成したプロシージャとビューの一覧を確認しておこう。

SHOW FULL TABLES IN order_db WHERE TABLE_TYPE = 'VIEW';
SHOW PROCEDURE STATUS WHERE Db = 'order_db';

GitHubへの反映

mysql-learning/
├── order-management/
│   ├── 01_schema.sql
│   ├── 02_crud.sql
│   └── 03_view_procedure.sql   ← 今回作成したVIEW・ストアドプロシージャ

git add order-management/
git commit -m "ビューとストアドプロシージャを追加（第3回）"
git push

まとめ

今回は、以下を学んだ。

ビュー（VIEW）で、よく使うJOIN・集計処理に名前を付けて再利用する方法
ストアドプロシージャで、複数のSQL処理を1つの処理としてまとめる方法
DECLARE EXIT HANDLERとSIGNALによる、プロシージャ内でのエラー処理とロールバック
FOR UPDATEによる、同時実行時の行ロック

次回は、注文明細が追加されたタイミングで自動的に処理を実行するトリガーと、閲覧専用ユーザーを作成するユーザー権限管理を紹介しよう。

コードのダウンロード

今回のコードは、私のGitHubリポジトリからダウンロードできる。

▶ Rocky-Seven/mysql-learning/order-management

MySQLで学ぶ受注管理システム開発（1）「テーブル設計とER図を描こう」
MySQLで学ぶ受注管理システム開発（2）「CRUD操作を極めよう」

月	火	水	木	金	土	日
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31