注文処理のための複合プロシージャ例：データ検証、ステータス更新、ログ記録

今日は実際の注文処理プロシージャの作り方を見ていくよ。いくつかのステップがあって、データのバリデーション、注文ステータスの更新、ログ記録が含まれてる。レストランでシェフ、ウェイター、レジ係が連携して動くイメージ。今回のプロシージャでも、各ステップがうまく連携するロジックを実装するよ。

プロシージャの課題説明

注文処理プロシージャは次のステップを実行する必要がある：

1. 必要な商品が在庫にあるかチェックする。
2. 在庫が十分なら、その分だけ在庫を減らす。
3. 注文のステータスを「処理済み」に更新する。
4. 成功した操作の情報をログ（journal）に記録する。
5. エラーが発生したら、すべての変更を最初に戻す（ロールバック）。

プロシージャの実装

ステップ1. 作業用のスキーマとテーブルを作成

プロシージャを書く前に、使うテーブルを作成しよう。

ordersテーブル — 注文

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    status TEXT DEFAULT 'Pending'
);

このテーブルは注文を保存する。各注文には顧客名、商品ID、数量、ステータス（デフォルトは「処理待ち」）があるよ。

inventoryテーブル — 在庫

CREATE TABLE inventory (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL UNIQUE,
    stock INT NOT NULL CHECK (stock >= 0)
);

在庫リストのテーブル。各商品は現在の在庫数（stock）を持ってる。

order_logsテーブル — 操作ログ

CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    log_message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

このログは注文処理のステータス情報を記録するために使うよ。

ステップ2. プロシージャの構造

マルチステッププロシージャの構造はこんな感じ：

1. リクエストされた商品が在庫にあって、十分な数があるかチェック。
2. 在庫が十分なら、inventoryテーブルの在庫数を減らす。
3. 注文のステータスを「処理済み」に変更。
4. 成功した結果をorder_logsテーブルに記録。
5. エラーがあれば、すべての変更をロールバック。

ステップ3. プロシージャを書く

上記のステップを実行するprocess_orderプロシージャを書いてみよう。

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
    v_product_id INT;
    v_quantity INT;
    v_stock INT;
BEGIN
    -- ステップ1: 注文情報を取得
    SELECT product_id, quantity
    INTO v_product_id, v_quantity
    FROM orders
    WHERE order_id = $1;

    -- 注文が存在するかチェック
    IF NOT FOUND THEN
        RAISE EXCEPTION '注文ID % は存在しません。', $1;
    END IF;

    -- ステップ2: 在庫があるかチェック
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION '商品ID % は在庫にありません。', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION '商品ID % の在庫が足りません。リクエスト: %, 在庫: %。',
            v_product_id, v_quantity, v_stock;
    END IF;

    -- ステップ3: 在庫数を減らす
    UPDATE inventory
    SET stock = stock - v_quantity
    WHERE product_id = v_product_id;

    -- ステップ4: 注文ステータスを「処理済み」に更新
    UPDATE orders
    SET status = 'Processed'
    WHERE order_id = $1;

    -- ステップ5: 成功した処理をログに記録
    INSERT INTO order_logs (order_id, log_message)
    VALUES ($1, '注文が正常に処理されました。');

EXCEPTION
    WHEN OTHERS THEN
        -- 失敗時にエラーをログに記録
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, '注文処理中にエラー発生: ' || SQLERRM);

        -- すべての変更をロールバック
        RAISE;
END;
$$ LANGUAGE plpgsql;

このプロシージャを分解してみよう。

1. 検証ステップ：

   ordersテーブルに指定された注文が存在するかチェックする。なければ、詳細なメッセージ付きで例外を投げる。同じく、在庫の有無と数量もチェック。

2. 在庫操作ステップ：

   在庫が十分なら、UPDATEで在庫数を減らす。

3. 注文ステータス変更ステップ：

   「Processed」（処理済み）にステータスを変更して、注文が完了したことを示す。

4. ログ記録ステップ：

   注文処理が成功したら、order_logsテーブルにメッセージを追加して、操作情報を保存する。

5. 例外処理：

   何か問題が起きたら、EXCEPTIONブロックでエラー詳細をログに記録し、すべての変更をロールバックする。

使用例

プロシージャの動作確認用にテストデータを作成しよう。

-- 在庫に商品を追加
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);

-- 注文を追加
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
    ('Alice', 1, 2),
    ('Bob', 2, 1),
    ('Charlie', 1, 20); -- この注文はエラーになるはず

さあ、プロシージャをテストしよう：

-- Aliceの注文を処理
SELECT process_order(1);

-- Bobの注文を処理
SELECT process_order(2);

-- Charlieの注文を処理しようとする（エラーになる）
SELECT process_order(3);

結果：

• AliceとBobの注文は正常に処理され、ログに記録され、在庫も減る。
• Charlieの注文は在庫不足でエラーになり、エラーログが記録される。

クエリ実行後のテーブルを確認しよう：

SELECT * FROM inventory; -- 在庫の変化
SELECT * FROM orders; -- 注文ステータスの変化
SELECT * FROM order_logs; -- ログの記録

よくあるミスとアドバイス

1. ミス：SELECT INTOの後にNOT FOUNDをチェックし忘れる。

   クエリが空結果を返す場合を必ず処理しよう。そうしないと予期しない例外が発生するよ。

2. ミス：EXCEPTIONブロックを追加しない。

   エラーハンドラがないと、例外発生時にトランザクションが中途半端になったり、ロジックが壊れることがある。

3. アドバイス：SQLインジェクションに注意。

   型付きパラメータを使って、動的SQLは本当に必要な場合だけにしよう。

プロシージャの拡張

実際の現場では、もっといろんなチェックを追加できるよ：

• 顧客向けの割引やキャンペーンを考慮する。
• 注文処理前に顧客のクレジットリミットをチェックする。
• 成功だけじゃなく、ロールバックもログに記録する。