多步骤过程的创建：数据校验、插入和日志记录

在真实的业务场景下，通常不是只做一步操作，而是要搞一串动作：比如下单时——先校验客户数据，再保存订单，还要写一条日志方便以后查。多步骤procedure能把这些步骤合成一套逻辑，并且通过事务保证一致性：只要有一步出错，所有更改都会回滚。

随着PostgreSQL新版本的发布，尤其是有了独立的procedure（CREATE PROCEDURE）和更强的事务控制，理解PL/pgSQL里function和procedure的区别，以及怎么用savepoint（SAVEPOINT）、回滚、异常块就很重要了。

多步骤procedure的基本结构

典型的业务procedure一般分为这些步骤：

1. 数据校验 —— 校验输入参数、客户/商品是否存在等等。
2. 插入数据 —— 真正往表里加（或更新）记录。
3. 日志或审计 —— 记录操作成功或失败的信息。

每一步都可以在一个事务里搞定（原子性），或者如果流程很长、需要分步处理错误，也可以用SAVEPOINT和异常处理块来做局部回滚。

例子：带完整性校验的下单

假设有三个表：

• 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()
);

多步骤procedure的写法：FUNCTION还是PROCEDURE？

注意！

• 如果你需要完全控制事务（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总是在一个外部事务里执行。function里不能用事务控制（COMMIT、ROLLBACK、SAVEPOINT）。回滚或提交都是外部决定的。

带错误处理和错误日志的版本：

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事务回滚
    END;
END;
$$ LANGUAGE plpgsql;

BEGIN ... EXCEPTION ... END块：在PL/pgSQL里，function和procedure都可以用这个块，它会自动创建虚拟savepoint。块里出错就会回滚这个块的所有更改。

分步提交和分阶段处理：为什么要用procedure

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

PostgreSQL从11版开始支持独立的procedure（CREATE PROCEDURE），可以在服务器端控制事务和savepoint。只有PROCEDURE（function不行！）里才能用COMMIT、ROLLBACK、SAVEPOINT、RELEASE SAVEPOINT。但是：在PL/pgSQL的procedure里不能用ROLLBACK TO SAVEPOINT——要用异常处理。

分阶段处理和错误处理的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);

事务和procedure的最佳实践

• 原子业务操作用function —— 需要“全成全不成”的时候。
• 分阶段提交或分步回滚用procedure，并且在autocommit模式下调用。
• “部分回滚”用BEGIN ... EXCEPTION ... END块 —— 里面PL/pgSQL会自动建savepoint，出错就回滚这个块。
• 记录错误日志 —— 这样才能知道为啥有些东西没写进去或者没生效。
• 不要在PL/pgSQL的procedure里用ROLLBACK TO SAVEPOINT —— 这样会报语法错（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;