多步驟程序的建立：資料檢查、插入與日誌記錄

在真實的商業場景裡，通常不只做一個操作，而是要串一連串的動作：比如收到訂單時，要先檢查客戶資料、儲存訂單、然後寫一筆審計日誌。多步驟的程序可以把這些步驟包在一起，靠 transaction 來確保資料一致性：只要中間有哪一步出錯，所有變更都會被 rollback。

隨著 PostgreSQL 新版推出，特別是有了獨立的 procedure（CREATE PROCEDURE）和更強的 transaction 控制，搞懂 PL/pgSQL 的 function 跟 procedure 差在哪裡、怎麼正確用 savepoint（SAVEPOINT）、rollback、錯誤處理區塊就很重要。

多步驟程序的基本結構

典型的商業程序會有這幾個步驟：

1. 資料檢查 — 驗證輸入參數、檢查客戶/商品是否存在等等。
2. 資料插入 — 實際新增（或更新）資料列。
3. 日誌或審計 — 記錄操作成功或失敗的資訊。

每個步驟都可以包在同一個 transaction（原子性），或者如果流程很長、需要分段處理錯誤，也可以用 SAVEPOINT 跟 exception 區塊來做局部 rollback。

範例：帶完整性檢查的訂單新增

來看一個情境 — 有三張表：

• customers — 客戶
• orders — 訂單
• order_log — 訂單日誌

先建表：

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(),
    amount NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_log (
    log_id SERIAL PRIMARY KEY,
    order_id INT,
    log_message TEXT NOT NULL,
    log_date TIMESTAMP NOT NULL DEFAULT NOW()
);

多步驟程序的寫法：FUNCTION 還是 PROCEDURE？

重點！

• 如果你需要完全掌控 transaction（savepoint、明確的 COMMIT/ROLLBACK）— 用 CREATE PROCEDURE。
• 如果這個程序邏輯上是原子性的（「要嘛全做，要嘛全不做」）而且會被其他 SQL 語句呼叫 — 用 function。

function 版本（原子邏輯）：

CREATE OR REPLACE FUNCTION add_order(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 1. 檢查客戶
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'ID 為 % 的客戶不存在', p_customer_id;
    END IF;

    -- 2. 插入訂單
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. 日誌記錄
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, '訂單建立成功。');

    RAISE NOTICE '訂單 % 已成功加入給客戶 %', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

注意： PostgreSQL 的 function 一定會在同一個外部 transaction 裡執行。你不能在 function 裡用 COMMIT、ROLLBACK、SAVEPOINT 這些 transaction 控制。commit 或 rollback 只能在外面做。

加上錯誤處理和錯誤日誌的版本：

CREATE OR REPLACE FUNCTION add_order_with_error_logging(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    BEGIN
        -- 檢查客戶
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'ID 為 % 的客戶不存在', p_customer_id;
        END IF;

        -- 插入訂單
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- 日誌記錄
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, '訂單建立成功。');

        RAISE NOTICE '訂單 % 已成功加入給客戶 %', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('錯誤：%', SQLERRM));
            RAISE; -- 整個 function transaction rollback
    END;
END;
$$ LANGUAGE plpgsql;

BEGIN ... EXCEPTION ... END 區塊：在 PL/pgSQL 的 function 跟 procedure 裡，這個區塊會自動建立一個虛擬 savepoint。如果裡面出錯，這個區塊的變更會被 rollback。

分段 commit 跟逐步處理：為什麼要用 procedure

如果你真的需要分段 commit（真的部分 commit）— 就要用 PROCEDURE！

PostgreSQL 從 11 版開始支援獨立的 procedure（CREATE PROCEDURE），可以在 server 端直接控制 transaction 跟 savepoint。只有 PROCEDURE（function 不行！）才能明確執行 COMMIT、ROLLBACK、SAVEPOINT、RELEASE SAVEPOINT。但： 在 PL/pgSQL 的 procedure 裡不能用 ROLLBACK TO SAVEPOINT — 要用 exception 處理。

來看一個有分段處理和錯誤處理的 procedure 範例：

CREATE OR REPLACE PROCEDURE add_order_step_by_step(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 第一段：檢查客戶
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'ID 為 % 的客戶不存在', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('錯誤（驗證）：%', SQLERRM));
            RETURN;
    END;

    -- 第二段：插入訂單
    BEGIN
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('錯誤（訂單）：%', SQLERRM));
            RETURN;
    END;

    -- 第三段：成功操作的日誌
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, '訂單建立成功。');
    EXCEPTION
        WHEN OTHERS THEN
            -- 這裡就算日誌沒寫進去也沒差
            RAISE NOTICE '訂單 % 的日誌寫入失敗', v_order_id;
    END;

    RAISE NOTICE '訂單 % 已成功加入給客戶 %（procedure）', v_order_id, p_customer_id;
END;
$$;

呼叫 procedure：

CALL add_order_step_by_step(1, 150.50);

用 transaction 跟 procedure 的好習慣

• 原子性商業操作用 function — 需要「全做或全不做」的時候。
• 要分段 commit 或分段 rollback 就用 procedure，而且要在 autocommit 模式下呼叫。
• 「部分 rollback」就用 BEGIN ... EXCEPTION ... END 區塊 — PL/pgSQL 會自動幫你建 savepoint，出錯就 rollback 區塊內容。
• 記錄錯誤日誌 — 這樣才知道為什麼資料沒進去或哪裡出問題。
• 不要在 PL/pgSQL 的 procedure 裡用 ROLLBACK TO SAVEPOINT — 這會 syntax error（PostgreSQL 17+ 限制）。

測試：成功與失敗的情境

-- 新增一個客戶
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- 呼叫 function（應該會成功）
SELECT add_order(1, 300.00);

-- 呼叫 function，客戶不存在（會出錯）
SELECT add_order(999, 100.00);

-- 查詢日誌
SELECT * FROM order_log;