MySQL 存储过程和函数

存储过程

基本语法

DELIMITER //

CREATE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype, ...)
BEGIN
    -- 存储过程体
    -- SQL语句
END //

DELIMITER ;

参数类型

1. IN参数

   • 默认参数类型
   • 在存储过程中只能读取

   CREATE PROCEDURE get_user_orders(IN user_id INT)
   BEGIN
       SELECT * FROM orders WHERE user_id = user_id;
   END
   

2. OUT参数

   • 在存储过程中可以写入
   • 调用者可以获取值

   CREATE PROCEDURE get_order_total(IN order_id INT, OUT total DECIMAL(10,2))
   BEGIN
       SELECT SUM(price * quantity) INTO total
       FROM order_items WHERE order_id = order_id;
   END
   

3. INOUT参数

   • 可以读取和写入
   • 既可以传入也可以返回值

   CREATE PROCEDURE apply_discount(INOUT price DECIMAL(10,2), IN discount INT)
   BEGIN
       SET price = price - (price * discount / 100);
   END
   

存储函数

基本语法

DELIMITER //

CREATE FUNCTION function_name(parameter1 datatype, parameter2 datatype, ...)
RETURNS return_datatype
BEGIN
    -- 函数体
    RETURN value;
END //

DELIMITER ;

创建和使用函数

-- 创建计算折扣的函数
CREATE FUNCTION calculate_discount(price DECIMAL(10,2), discount INT)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE discounted_price DECIMAL(10,2);
    SET discounted_price = price - (price * discount / 100);
    RETURN discounted_price;
END;

-- 使用函数
SELECT item_name, price, 
       calculate_discount(price, 10) as discounted_price
FROM products;

流程控制

1. IF语句

   IF condition THEN
       statements;
   ELSEIF condition THEN
       statements;
   ELSE
       statements;
   END IF;
   

2. CASE语句

   CASE expression
       WHEN value1 THEN statements;
       WHEN value2 THEN statements;
       ELSE statements;
   END CASE;
   

3. 循环语句

   -- WHILE循环
   WHILE condition DO
       statements;
   END WHILE;
   
   -- REPEAT循环
   REPEAT
       statements;
   UNTIL condition
   END REPEAT;
   

错误处理

-- 使用DECLARE HANDLER
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    -- 错误处理代码
    ROLLBACK;
    SET error_message = 'An error occurred';
END;

最佳实践

1. 命名规范

   • 使用描述性名称
   • 遵循一致的命名规则
   • 添加适当的注释

2. 参数处理

   • 验证输入参数
   • 使用适当的参数类型
   • 处理NULL值

3. 性能考虑

   • 避免过度使用循环
   • 优化SQL语句
   • 适当使用事务

4. 安全性

   • 控制访问权限
   • 防止SQL注入
   • 记录关键操作