多階段程序的綜合除錯與優化

多階段程序就像資料庫界的「瑞士刀」。通常會包含輸入資料驗證、執行變更（像是更新紀錄、寫 log），有時還會有分析。但問題來了：程序越複雜，出錯機率就越高。邏輯錯誤、慢查詢、漏掉的小細節——一個不小心就全炸了。

綜合除錯包含這幾個面向：

1. 分析輸入資料：參數設對了嗎？資料傳進來對嗎？
2. 檢查關鍵步驟有沒有執行：每個步驟都正確跑了嗎？
3. log 中間結果：這樣才知道哪裡「爆炸」前發生了什麼。
4. 優化效能瓶頸：加強那些「卡住」查詢的地方。

題目設定：多階段程序的例子

來個實戰例子，假設我們在搞一個網路商店的資料庫。要寫一個處理訂單的程序。它會做這幾步：

1. 檢查商品庫存。
2. 預留商品。
3. 更新訂單狀態。
4. 把事件（像是預留成功或錯誤）寫進 log 表。

資料庫結構腳本：

-- 商品表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

-- 訂單表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    order_status TEXT NOT NULL
);

-- log 表
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

步驟 1：建立多階段程序

來寫個基本的 process_order 程序。它會接收訂單 id，然後跑完所有處理步驟。

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    -- 1. 取得商品 id 和訂單狀態
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION '訂單 % 不存在或缺少 product_id', p_order_id;
    END IF;

    -- 2. 檢查商品庫存
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION '商品 % 已售完', v_product_id;
    END IF;

    -- 3. 更新庫存數量
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. 更新訂單狀態
    UPDATE orders
    SET order_status = '已處理'
    WHERE order_id = p_order_id;

    -- 5. 寫入成功事件到 log
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, '訂單處理成功。');
END;
$$;

步驟 2：用 RAISE NOTICE 和 RAISE EXCEPTION log 錯誤

這裡開始進入魔法時刻。我們要加上中間步驟的 log，這樣才能抓錯，也能知道每一步發生什麼事。

加上 log 的新版程式碼：

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    RAISE NOTICE '正在處理訂單 %...', p_order_id;

    -- 1. 取得商品 id
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION '訂單 % 不存在或缺少 product_id', p_order_id;
    END IF;
    RAISE NOTICE '訂單 % 的商品 ID：%', p_order_id, v_product_id;

    -- 2. 檢查商品庫存
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION '商品 % 已售完', v_product_id;
    END IF;
    RAISE NOTICE '商品 % 的庫存數量：%', v_product_id, v_stock_quantity;

    -- 3. 更新庫存數量
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. 更新訂單狀態
    UPDATE orders
    SET order_status = '已處理'
    WHERE order_id = p_order_id;

    -- 5. log 成功
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, '訂單處理成功。');
    RAISE NOTICE '訂單 % 處理成功。', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- log 錯誤
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, '錯誤：' || SQLERRM);
    RAISE;
END;
$$;

步驟 3：用 index 優化

如果資料庫裡商品或訂單很多，找資料會變成瓶頸。加點 index 來加速查詢：

-- 加速 orders 表查詢的 index
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- 加速 products 表查詢的 index
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

步驟 4：用 EXPLAIN ANALYZE 分析效能

現在來看看我們的 function 跑多快。用效能分析來呼叫它：

EXPLAIN ANALYZE
SELECT process_order(1);

結果會顯示每個步驟花多少時間。這樣就能找出哪一步最慢——方便我們再優化。

步驟 5：用 transaction 增強可靠性

為了更可靠，可以把整個程序包進 transaction。這樣只要有錯，所有變更都會 rollback。

BEGIN;

-- 呼叫 function
SELECT process_order(1);

-- commit transaction
COMMIT;

在 function 裡也可以用 SAVEPOINT 和 ROLLBACK TO SAVEPOINT 來處理部分錯誤。

實戰練習：批次處理訂單

最後來個一次處理多個訂單的例子。我們寫個 function，會處理所有狀態是 待處理 的訂單：

CREATE OR REPLACE FUNCTION process_all_orders()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    FOR v_order_id IN
        SELECT order_id
        FROM orders
        WHERE order_status = '待處理'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '處理訂單 % 失敗：%', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

呼叫這個 function 時，所有狀態為 待處理 的訂單都會被處理，任何錯誤只會被 log。

這樣我們就示範了怎麼除錯和優化複雜程序，讓它們更可靠、更快、更好讀。這些技巧在真實專案裡超實用，因為程序品質直接影響應用程式的成敗！