存储过程和函数

概述实现存储过程、函数和触发器以处理业务逻辑、数据验证和性能优化。涵盖过程设计、错误处理和性能考虑。

何时使用

业务逻辑封装
复杂多步骤操作
数据验证和约束
审计跟踪维护
性能优化
代码在应用程序间的可重用性
基于触发器的自动化

PostgreSQL 过程和函数

简单函数

PostgreSQL - 标量函数：

-- 创建返回单个值的函数
CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal DECIMAL,
  p_tax_rate DECIMAL,
  p_shipping DECIMAL
)
RETURNS DECIMAL AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::NUMERIC, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 在查询中使用
SELECT id, subtotal, calculate_order_total(subtotal, 0.08, 10) as total
FROM orders;

-- 或在应用程序代码中
SELECT * FROM orders
WHERE calculate_order_total(subtotal, 0.08, 10) > 100;

PostgreSQL - 表返回函数：

-- 返回行集
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id UUID)
RETURNS TABLE (
  order_id UUID,
  order_date TIMESTAMP,
  total DECIMAL,
  status VARCHAR
) AS $$
BEGIN
  RETURN QUERY
  SELECT o.id, o.created_at, o.total, o.status
  FROM orders o
  WHERE o.user_id = p_user_id
  ORDER BY o.created_at DESC;
END;
$$ LANGUAGE plpgsql STABLE;

-- 使用函数
SELECT * FROM get_user_orders('user-123');

存储过程

PostgreSQL - 带 OUT 参数的过程：

-- 带输出参数的存储过程
CREATE OR REPLACE PROCEDURE process_order(
  p_order_id UUID,
  OUT p_success BOOLEAN,
  OUT p_message VARCHAR
)
LANGUAGE plpgsql AS $$
BEGIN
  BEGIN
    -- 开始事务
    UPDATE orders SET status = 'processing' WHERE id = p_order_id;

    UPDATE inventory
    SET quantity = quantity - 1
    WHERE product_id IN (
      SELECT product_id FROM order_items WHERE order_id = p_order_id
    );

    -- 检查库存
    IF EXISTS (SELECT 1 FROM inventory WHERE quantity < 0) THEN
      RAISE EXCEPTION 'Insufficient inventory';
    END IF;

    p_success := true;
    p_message := 'Order processed successfully';
  EXCEPTION WHEN OTHERS THEN
    p_success := false;
    p_message := SQLERRM;
    -- 事务自动回滚
  END;
END;
$$;

-- 调用过程
CALL process_order('order-123', success, message);
SELECT success, message;

复杂过程与逻辑：

CREATE OR REPLACE PROCEDURE transfer_funds(
  p_from_account_id INT,
  p_to_account_id INT,
  p_amount DECIMAL,
  OUT p_success BOOLEAN,
  OUT p_error_message VARCHAR
)
LANGUAGE plpgsql AS $$
DECLARE
  v_from_balance DECIMAL;
BEGIN
  BEGIN
    -- 检查余额
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE id = p_from_account_id
    FOR UPDATE;

    IF v_from_balance < p_amount THEN
      RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- 从账户借记
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE id = p_from_account_id;

    -- 向账户贷记
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE id = p_to_account_id;

    -- 记录交易
    INSERT INTO transaction_log (from_id, to_id, amount, status)
    VALUES (p_from_account_id, p_to_account_id, p_amount, 'completed');

    p_success := true;
    p_error_message := NULL;
  EXCEPTION WHEN OTHERS THEN
    p_success := false;
    p_error_message := SQLERRM;
  END;
END;
$$;

MySQL 存储过程

简单过程

MySQL - 基本过程：

-- 简单过程
DELIMITER //

CREATE PROCEDURE get_user_by_email(IN p_email VARCHAR(255))
BEGIN
  SELECT id, email, name, created_at
  FROM users
  WHERE email = p_email;
END //

DELIMITER ;

-- 调用过程
CALL get_user_by_email('john@example.com');

MySQL - 带 OUT 参数的过程：

DELIMITER //

CREATE PROCEDURE calculate_user_stats(
  IN p_user_id INT,
  OUT p_total_orders INT,
  OUT p_total_spent DECIMAL
)
BEGIN
  SELECT
    COUNT(*),
    SUM(total)
  INTO p_total_orders, p_total_spent
  FROM orders
  WHERE user_id = p_user_id AND status != 'cancelled';

  IF p_total_orders IS NULL THEN
    SET p_total_orders = 0;
    SET p_total_spent = 0;
  END IF;
END //

DELIMITER ;

-- 调用过程
CALL calculate_user_stats(123, @orders, @spent);
SELECT @orders as total_orders, @spent as total_spent;

带错误处理的复杂过程

MySQL - 事务管理：

DELIMITER //

CREATE PROCEDURE create_order(
  IN p_user_id INT,
  IN p_items JSON,
  OUT p_order_id INT,
  OUT p_success BOOLEAN,
  OUT p_error VARCHAR(500)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_success = FALSE;
    SET p_error = 'Transaction failed';
  END;

  START TRANSACTION;

  -- 创建订单
  INSERT INTO orders (user_id, status, created_at)
  VALUES (p_user_id, 'pending', NOW());

  SET p_order_id = LAST_INSERT_ID();

  -- 添加项目到订单（假设项目是 JSON 数组）
  -- 需要在 MySQL 5.7+ 中解析 JSON
  -- INSERT INTO order_items (order_id, product_id, quantity)
  -- SELECT p_order_id, JSON_EXTRACT(...), ...

  -- 更新库存
  UPDATE inventory
  SET quantity = quantity - 1
  WHERE product_id IN (
    SELECT product_id FROM order_items WHERE order_id = p_order_id
  );

  -- 检查库存
  IF EXISTS (SELECT 1 FROM inventory WHERE quantity < 0) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Insufficient inventory';
  END IF;

  COMMIT;
  SET p_success = TRUE;
  SET p_error = NULL;
END //

DELIMITER ;

触发器

PostgreSQL 触发器

审计跟踪触发器：

-- 审计表
CREATE TABLE user_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  operation VARCHAR(10),
  old_values JSONB,
  new_values JSONB,
  changed_at TIMESTAMP DEFAULT NOW()
);

-- 触发器函数
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_audit_log (user_id, operation, old_values, new_values)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    to_jsonb(OLD),
    to_jsonb(NEW)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER user_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

更新时间戳触发器：

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_orders_timestamp
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

验证触发器：

CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
BEGIN
  -- 验证订单总额
  IF NEW.total < 0 THEN
    RAISE EXCEPTION 'Order total cannot be negative';
  END IF;

  -- 验证用户存在
  IF NOT EXISTS (SELECT 1 FROM users WHERE id = NEW.user_id) THEN
    RAISE EXCEPTION 'User does not exist';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_order_trigger
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION validate_order();

MySQL 触发器

MySQL - 插入触发器：

DELIMITER //

CREATE TRIGGER create_order_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  -- 更新用户统计信息
  UPDATE user_stats
  SET total_orders = total_orders + 1,
      total_spent = total_spent + NEW.total
  WHERE user_id = NEW.user_id;

  -- 创建审计日志
  INSERT INTO audit_log (table_name, operation, record_id, timestamp)
  VALUES ('orders', 'INSERT', NEW.id, NOW());
END //

DELIMITER ;

MySQL - 更新阻止触发器：

DELIMITER //

CREATE TRIGGER prevent_old_order_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  IF OLD.status = 'completed' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot update completed orders';
  END IF;
END //

DELIMITER ;

函数性能

PostgreSQL - IMMUTABLE vs STABLE vs VOLATILE:

-- IMMUTABLE: 对于相同的参数结果总是相同的（可以优化）
CREATE FUNCTION calculate_tax(p_amount DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
  RETURN p_amount * 0.08;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- STABLE: 结果在查询中一致（可以用作索引）
CREATE FUNCTION get_current_year()
RETURNS INT AS $$
BEGIN
  RETURN EXTRACT(YEAR FROM CURRENT_DATE);
END;
$$ LANGUAGE plpgsql STABLE;

-- VOLATILE: 可以改变（函数每次都执行）
CREATE FUNCTION get_random_user()
RETURNS UUID AS $$
BEGIN
  RETURN (SELECT id FROM users ORDER BY RANDOM() LIMIT 1);
END;
$$ LANGUAGE plpgsql VOLATILE;

参数验证

PostgreSQL - 输入验证：

CREATE OR REPLACE FUNCTION create_user(
  p_email VARCHAR,
  p_name VARCHAR
)
RETURNS UUID AS $$
DECLARE
  v_user_id UUID;
BEGIN
  -- 验证输入
  IF p_email IS NULL OR p_email = '' THEN
    RAISE EXCEPTION 'Email cannot be empty';
  END IF;

  IF p_name IS NULL OR LENGTH(p_name) < 2 THEN
    RAISE EXCEPTION 'Name must be at least 2 characters';
  END IF;

  -- 检查电子邮件格式
  IF NOT p_email ~ '^\w+@\w+\.\w+$' THEN
    RAISE EXCEPTION 'Invalid email format';
  END IF;

  -- 创建用户
  INSERT INTO users (email, name)
  VALUES (LOWER(p_email), TRIM(p_name))
  RETURNING id INTO v_user_id;

  RETURN v_user_id;
EXCEPTION WHEN unique_violation THEN
  RAISE EXCEPTION 'Email already exists';
END;
$$ LANGUAGE plpgsql;

测试过程

PostgreSQL - 测试函数：

-- 测试 transfer_funds 过程
DO $$
DECLARE
  v_success BOOLEAN;
  v_error VARCHAR;
BEGIN
  CALL transfer_funds(1, 2, 100, v_success, v_error);
  ASSERT v_success, 'Transfer should succeed: ' || v_error;

  -- 验证转账
  ASSERT (SELECT balance FROM accounts WHERE id = 1) = 900,
    'Account 1 balance should be 900';
  ASSERT (SELECT balance FROM accounts WHERE id = 2) = 1100,
    'Account 2 balance should be 1100';

  RAISE NOTICE 'All tests passed';
END $$;

过程维护

PostgreSQL - 删除过程：

-- 删除函数
DROP FUNCTION IF EXISTS calculate_order_total(DECIMAL, DECIMAL, DECIMAL);

-- 删除过程
DROP PROCEDURE IF EXISTS process_order(UUID);

-- 删除触发器
DROP TRIGGER IF EXISTS user_audit_trigger ON users;
DROP FUNCTION IF EXISTS audit_user_changes();

最佳实践

✅ DO 使用过程处理复杂操作 ✅ DO 在过程中验证输入 ✅ DO 优雅地处理错误 ✅ DO 详细记录过程参数 ✅ DO 彻底测试过程 ✅ DO 适当使用事务 ✅ DO 监控过程性能

❌ DON’T 将所有业务逻辑放在过程里 ❌ DON’T 用于简单查询的过程 ❌ DON’T 忽略错误处理 ❌ DON’T 创建文档不清晰的过程 ❌ DON’T 仅将过程用作安全层

存储过程和函数 stored-procedures