訂單處理的複合程序範例：資料驗證、狀態更新、紀錄日誌

今天我們要來拆解一個真實的訂單處理程序怎麼寫。這個流程有幾個步驟：資料驗證、訂單狀態更新，還有紀錄日誌。想像一下在餐廳裡，主廚、服務生跟收銀員都要配合好。我們的程序就是要實現這種步驟之間的協作邏輯。

程序任務說明

訂單處理程序要做這幾個步驟：

1. 檢查倉庫裡有沒有需要的商品。
2. 如果庫存夠，就從倉庫扣掉數量。
3. 把訂單狀態更新成「已處理」。
4. 把成功操作的資訊寫進日誌（log）。
5. 如果有任何錯誤，要把所有變更回滾到一開始。

程序實作

步驟 1. 建 schema 跟表格

在寫程序之前，先來建好會用到的表格。

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 injection。

   用嚴格型別的參數，沒必要就不要用動態 SQL。

程序擴充

實際上你可以加更多檢查，例如：

• 考慮客戶的折扣或促銷活動。
• 在處理訂單前檢查客戶的信用額度。
• 不只記錄成功，也記錄回滾的操作。