用巢狀交易實作的實戰範例

今天我們的任務是要寫一個 function，它會：

1. 檢查客戶餘額。在扣款前要先確認錢夠不夠。
2. 從餘額扣款。如果錢夠就扣。
3. 記錄成功和失敗的操作。所有動作都會寫進 log 表，方便之後分析。

這不只是無聊的扣款 function。我們會用巢狀交易，讓你如果哪裡出錯（像是錢不夠或寫 log 失敗）可以回滾。你會發現 SAVEPOINT 的好處，也會學到怎麼讓程序更防呆。

建立初始資料表

在開始寫 function 之前，先把資料庫準備好。我們需要三個表：

1. clients — 存客戶資料和他們的餘額。
2. payments — 記錄成功的交易。
3. logs — 存所有支付嘗試（不管成功還失敗）的資訊。

-- 客戶表
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL DEFAULT 0
);

-- 成功支付表
CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    amount NUMERIC(10, 2) NOT NULL,
    payment_date TIMESTAMP DEFAULT NOW()
);

-- 日誌表
CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    message TEXT NOT NULL,
    log_date TIMESTAMP DEFAULT NOW()
);

來幫 clients 表加點測試資料

INSERT INTO clients (full_name, balance)
VALUES 
    ('Otto Song', 100.00),
    ('Maria Chi', 50.00),
    ('Anna Vel', 0.00);

現在我們有三個客戶：Otto 帳戶有 100，Maria 有 50，Anna 則是 0。

實作商業邏輯：PROCEDURE vs FUNCTION

重點：

• 如果是「全有全無」的商業操作，用 function 就夠了。
• 如果你要分段控管交易、部分 commit、回滾、記錄錯誤，請用 procedure（CREATE PROCEDURE）。

為什麼不用 function？因為在 PostgreSQL 17 裡，function 裡面你不能用 COMMIT、SAVEPOINT、ROLLBACK。所有變更都會在外部交易裡一次完成。

只有 procedure（CREATE PROCEDURE ... LANGUAGE plpgsql）可以用 SAVEPOINT、COMMIT、ROLLBACK，但有幾個重點要注意：

• procedure 裡允許 SAVEPOINT、COMMIT、RELEASE SAVEPOINT。
• ROLLBACK TO SAVEPOINT 在 PL/pgSQL 裡是禁止的（會報錯），要用 BEGIN ... EXCEPTION ... END 區塊來做「虛擬 savepoint」。

部分回滾的主要技巧：

BEGIN
    -- 你的程式碼
EXCEPTION
    WHEN OTHERS THEN
        -- 這個區塊如果出錯會回滾裡面所有變更！
        -- 可以在 log 裡記錄資訊：
        INSERT INTO logs (...) VALUES (...);
END;

寫一個有部分回滾和記錄 log 的支付 procedure

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- 取得客戶餘額
    SELECT balance INTO current_balance
    FROM clients
    WHERE client_id = in_client_id;

    IF NOT FOUND THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '找不到客戶，操作被拒絕');
        RAISE EXCEPTION '找不到 ID 為 % 的客戶', in_client_id;
    END IF;

    -- 檢查錢夠不夠
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '餘額不足，無法扣款 ' || in_payment_amount || ' 元。');
        -- 結束 procedure
        RETURN;
    END IF;

    -- 原子變更區塊；出錯就回滾（虛擬 savepoint）
    BEGIN
        -- 扣款
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- 新增成功支付紀錄
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- 記錄成功
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '成功扣款 ' || in_payment_amount || ' 元。');

    EXCEPTION
        WHEN OTHERS THEN
            -- 這個區塊裡的所有變更都會被取消
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, '支付時發生錯誤：' || SQLERRM);
            -- （不用寫 ROLLBACK TO SAVEPOINT — 這個在這裡不能用也不需要）
    END;
END;
$$;

簡單說明流程：

• 如果錢不夠或找不到客戶，就記錄 log 然後結束。
• 所有關鍵程式碼都放在 BEGIN ... EXCEPTION ... END 區塊裡。
• 只要這個區塊裡有任何錯誤，所有變更都會自動回滾，然後把錯誤寫進 log。
• 沒有直接用 SAVEPOINT 和 ROLLBACK TO SAVEPOINT，這是正常的，PL/pgSQL 只靠 EXCEPTION 區塊來做。

呼叫 procedure

注意： procedure 要用 CALL ... 指令呼叫，而且外部連線要在 autocommit 模式或不要包在大交易裡！

CALL process_payment(1, 30.00);   -- 成功支付
CALL process_payment(2, 100.00);  -- 餘額不足
CALL process_payment(999, 50.00); -- 沒有這個客戶

檢查結果

• 只有支付成功才會改變客戶餘額。
• payments 表只有成功扣款才會有紀錄。
• logs 表會有所有嘗試（還有錯誤）的歷史。

SELECT * FROM clients;
SELECT * FROM payments;
SELECT * FROM logs;

實際應用

處理交易的 procedure 是金融科技、電商，甚至遊戲平台系統的核心之一。想像一下網路商店要管理禮物卡餘額並在購物時扣款，或是銀行系統每秒有成千上萬的操作。

這些知識在實務上超有用，可以幫你保護客戶資料，避免支付流程出現災難性錯誤。