存储过程与函数

存储过程和函数允许你在数据库内部执行业务逻辑——数据不离开数据库，减少网络往返，对特定场景有显著性能优势。

函数 vs 存储过程

PostgreSQL 11 之前只有函数（必须返回值），11 之后引入了真正的存储过程（可以不返回值）。

特性	函数（FUNCTION）	存储过程（PROCEDURE）
返回值	必须返回（可 VOID）	可不返回
事务控制	不支持 BEGIN/COMMIT 内部	支持
SELECT 调用	✅ `SELECT func()`	❌
CALL 调用	❌	✅ `CALL proc()`
参数模式	IN / OUT / INOUT	IN / INOUT

创建函数

CREATE OR REPLACE FUNCTION func_name(param1 TYPE, param2 TYPE DEFAULT default_value)
RETURNS return_type
LANGUAGE plpgsql
AS $$
BEGIN
    -- 函数体
    RETURN result;
END;
$$;

标量函数

-- 最简单的函数：计算折扣价格
CREATE OR REPLACE FUNCTION calc_discount_price(
    p_price NUMERIC,
    p_discount NUMERIC DEFAULT 0.9  -- 默认 9 折
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN ROUND(p_price * p_discount, 2);
END;
$$;

-- 调用
SELECT calc_discount_price(100);       -- 返回 90.00
SELECT calc_discount_price(100, 0.8); -- 返回 80.00

SQL 语言函数

对于简单计算，用 SQL 语言（而非 PL/pgSQL）性能更好：

CREATE OR REPLACE FUNCTION get_product_count(cat VARCHAR)
RETURNS BIGINT
LANGUAGE SQL
STABLE  -- 提示函数在同事务中结果一致
AS $$
    SELECT COUNT(*) FROM products WHERE category = cat AND is_active = true
$$;

SELECT get_product_count('电子产品');

表值函数（返回多行）

-- 返回指定分类的产品
CREATE OR REPLACE FUNCTION get_active_products(cat VARCHAR DEFAULT NULL)
RETURNS TABLE(
    id INTEGER,
    name VARCHAR,
    price NUMERIC,
    stock INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, p.name, p.price, p.stock
    FROM products p
    WHERE (cat IS NULL OR p.category = cat)
        AND p.is_active = true
    ORDER BY p.price DESC;
END;
$$;

-- 调用
SELECT * FROM get_active_products('电子产品');
SELECT * FROM get_active_products();  -- 返回全部

函数分类（VOLATILE/STABLE/IMMUTABLE）

-- VOLATILE（默认）：每次执行可能返回不同结果
CREATE OR REPLACE FUNCTION current_timestamp_func()
RETURNS TIMESTAMPTZ
LANGUAGE SQL
VOLATILE
AS $$ SELECT NOW(); $$;

-- STABLE：在同事务中返回相同结果
CREATE OR REPLACE FUNCTION get_user_name(uid INT)
RETURNS VARCHAR
LANGUAGE SQL
STABLE
AS $$ SELECT name FROM users WHERE id = uid; $$;

-- IMMUTABLE：输入相同则结果永远相同
CREATE OR REPLACE FUNCTION add_tax(price NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE
AS $$ SELECT price * 1.13; $$;

-- 对索引优化：IMMUTABLE 函数可以用于表达式索引！
CREATE INDEX idx_products_tax ON products(get_tax(price));

PL/pgSQL 语法要素

变量声明

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    cust_id INT;
    total  NUMERIC;
    status TEXT;
    counter INT := 0;
BEGIN
    SELECT customer_id, total_amount, status 
    INTO STRICT cust_id, total, status
    FROM orders WHERE order_id = process_order.order_id;
    -- ↑ 使用函数参数名限定时用函数名
    
    RETURN format('客户 %s 的订单 %s：￥%s', cust_id, status, total);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN '订单不存在';
    WHEN TOO_MANY_ROWS THEN
        RETURN '多个订单匹配';
END;
$$;

条件与循环

CREATE OR REPLACE FUNCTION apply_batch_discount(
    min_amount NUMERIC DEFAULT 10000,
    discount NUMERIC DEFAULT 0.95
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    r          RECORD;
    updated    INT := 0;
    batch_date  TEXT := TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
BEGIN
    FOR r IN SELECT order_id, total_amount FROM orders 
             WHERE status = '已完成' AND total_amount >= min_amount
             ORDER BY order_id 
    LOOP
        UPDATE orders 
        SET total_amount = total_amount * discount, 
            updated_at = NOW()
        WHERE order_id = r.order_id;
        
        updated := updated + 1;
    END LOOP;
    
    RETURN format('[%s] 共更新 %s 个订单，折扣 %.0f%%', batch_date, updated, (1-discount)*100);
END;
$$;

存储过程

PostgreSQL 11+ 引入，主要区别是支持内部事务控制：

CREATE OR REPLACE PROCEDURE transfer_money(
    from_account INT,
    to_account INT,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 检查余额
    IF (SELECT balance FROM bank_accounts WHERE id = from_account) < amount THEN
        RAISE EXCEPTION '余额不足';
    END IF;
    
    UPDATE bank_accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE bank_accounts SET balance = balance + amount WHERE id = to_account;
    
    -- 记录转账日志
    INSERT INTO transfer_log (from_id, to_id, amount, created_at)
    VALUES (from_account, to_account, amount, NOW());
    
    COMMIT;  -- ✅ 存储过程内部可以 COMMIT
END;
$$;

-- 调用
CALL transfer_money(1, 2, 1000);

错误处理

CREATE OR REPLACE FUNCTION safe_divide(
    a NUMERIC, b NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE WARNING '除以零：%/%', a, b;
        RETURN NULL;
    WHEN others THEN
        RAISE WARNING '未知错误：%', SQLERRM;
        RETURN NULL;
END;
$$;

RAISE 抛出异常

RAISE DEBUG '调试信息：%', variable;
RAISE LOG '日志：%', variable;
RAISE INFO '提示：%', variable;
RAISE NOTICE '注意：%', variable;  -- 默认客户端可见
RAISE WARNING '警告：%', variable;
RAISE EXCEPTION '致命错误：%', variable;  -- 中断事务

实战：订单流程函数

-- 创建一个完整的订单处理函数
CREATE OR REPLACE FUNCTION create_order(
    p_customer_id INT,
    p_product_list JSONB  -- [{"product_id": 1, "qty": 2}, {...}]
)
RETURNS TABLE(order_id INT, total NUMERIC, message TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    new_order_id INT;
    item         JSONB;
    pid          INT;
    qty          INT;
    unit_price   NUMERIC;
    stock_avail  INT;
    order_total  NUMERIC := 0;
BEGIN
    -- 1. 创建订单
    INSERT INTO orders (customer_id, total_amount, status)
    VALUES (p_customer_id, 0, 'pending')
    RETURNING order_id INTO new_order_id;
    
    -- 2. 逐个处理商品
    FOR item IN SELECT * FROM JSONB_ARRAY_ELEMENTS(p_product_ids)
    LOOP
        pid := (item->>'product_id')::INT;
        qty := (item->>'qty')::INT;
        
        -- 获取价格并锁定库存行
        SELECT price, stock INTO STRICT price, stock_avail
        FROM products WHERE id = pid FOR UPDATE;
        
        -- 检查库存
        IF stock_avail < qty THEN
            ROLLBACK;
            RETURN QUERY SELECT NULL::INT, 0::NUMERIC, 
                format('库存不足: %s (需要 %s，剩余 %s)', 
                    (SELECT name FROM products WHERE id = pid), qty, stock_avail);
            RETURN;
        END IF;
        
        -- 扣减库存
        UPDATE products SET stock = stock - qty WHERE id = pid;
        
        -- 插入订单详情
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (new_order_id, pid, qty, price);
        
        order_total := order_total + price * qty;
    END LOOP;
    
    -- 3. 更新订单总额
    UPDATE orders SET total_amount = order_total WHERE order_id = new_order_id;
    
    -- 4. 返回结果
    RETURN QUERY SELECT new_order_id, order_total, '下单成功'::TEXT;
    
EXCEPTION
    WHEN OTHERS THEN
        RETURN QUERY SELECT 0::INT, 0::NUMERIC, 
            format('下单失败：%s', SQLERRM)::TEXT;
END;
$$;

事务中调用函数

函数内部不能显式 COMMIT，但可以被包含在外部事务中：

BEGIN;
SELECT create_order(1, '[{"product_id": 1, "qty": 1}]');
-- 所有 INSERT/UPDATE 都作为一个事务
COMMIT;  -- 全部成功
-- ROLLBACK;  -- 全部回滚

安全考虑

-- 创建函数时指定安全属性
CREATE OR REPLACE FUNCTION high_privilage_op()
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER  -- 以调用者身份执行（默认）
-- SECURITY DEFINER  -- 以函数所有者身份执行
AS $$
BEGIN
    -- 执行需要更高权限的操作
    RETURN 'done';
END;
$$;

小结

存储过程和函数是把业务逻辑下推到数据库层的工具。PostgreSQL 的 PL/pgSQL 提供了丰富的语法——变量、循环、条件判断、异常处理——让函数具备完整的编程能力。合理使用函数可以减少应用层代码的复杂度，在数据分析、批量处理和事务管理场景中尤其有用。

下一篇文章将学习触发器，它能让数据库在特定事件发生时自动执行逻辑。

Summary: PL/pgSQL 函数、存储过程、事务控制与订单处理实战。