存储过程与触发器

存储过程和触发器是MySQL数据库中用于实现复杂业务逻辑和自动化数据处理的重要工具。它们可以将复杂的SQL操作封装起来，提高代码的重用性和可维护性，同时也能减少网络传输和提高数据库性能。本章将详细介绍存储过程和触发器的概念、创建方法、使用技巧以及在实际开发中的应用场景。

8.1 存储过程概述

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用执行它。

存储过程的优点

提高性能：存储过程只在创建时编译一次，而SQL语句每次执行都需要编译
减少网络流量：客户端只需发送存储过程的调用命令，而不是整个SQL语句
提高代码重用性：存储过程可以被多个应用程序调用
增强安全性：可以授予用户执行存储过程的权限，而不授予直接访问数据库对象的权限
简化复杂操作：将复杂的业务逻辑封装在存储过程中，使应用程序更简单

8.2 创建存储过程

在MySQL中，创建存储过程使用CREATE PROCEDURE语句。其基本语法如下：

DELIMITER $$
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    -- SQL语句集
END$$
DELIMITER ;

其中：

DELIMITER $$：将SQL语句的分隔符临时修改为$$，避免存储过程中的分号被MySQL解析器错误处理
存储过程名称：存储过程的标识符
参数列表：可选，存储过程的参数，格式为[IN|OUT|INOUT] 参数名数据类型
BEGIN和END：定义存储过程的开始和结束
DELIMITER ;：将分隔符恢复为默认的分号

示例：创建简单的存储过程

-- 创建一个无参数的存储过程，查询所有员工信息
DELIMITER $$
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END$$
DELIMITER ;

-- 创建一个带IN参数的存储过程，根据部门查询员工
DELIMITER $$
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees WHERE department = dept_name;
END$$
DELIMITER ;

-- 创建一个带OUT参数的存储过程，获取指定部门的员工数量
DELIMITER $$
CREATE PROCEDURE GetEmployeeCountByDepartment(IN dept_name VARCHAR(50), OUT employee_count INT)
BEGIN
    SELECT COUNT(*) INTO employee_count FROM employees WHERE department = dept_name;
END$$
DELIMITER ;

-- 创建一个带INOUT参数的存储过程，计算工资增长后的结果
DELIMITER $$
CREATE PROCEDURE CalculateSalaryIncrease(INOUT salary DECIMAL(10,2), IN increase_percent DECIMAL(4,2))
BEGIN
    SET salary = salary * (1 + increase_percent / 100);
END$$
DELIMITER ;

8.3 调用存储过程

在MySQL中，调用存储过程使用CALL语句。其基本语法如下：

CALL 存储过程名称([参数列表]);

示例：调用存储过程

-- 调用无参数的存储过程
CALL GetAllEmployees();

-- 调用带IN参数的存储过程
CALL GetEmployeesByDepartment('技术部');

-- 调用带OUT参数的存储过程
SET @count = 0;
CALL GetEmployeeCountByDepartment('市场部', @count);
SELECT @count AS employee_count;

-- 调用带INOUT参数的存储过程
SET @current_salary = 5000.00;
CALL CalculateSalaryIncrease(@current_salary, 10); -- 增加10%
SELECT @current_salary AS new_salary;

8.4 存储函数

存储函数与存储过程类似，但存储函数必须返回一个值，并且可以在SQL语句中直接使用。在MySQL中，创建存储函数使用CREATE FUNCTION语句。

其基本语法如下：

DELIMITER $$
CREATE FUNCTION 函数名称([参数列表])
RETURNS 返回数据类型
[NOT] DETERMINISTIC
BEGIN
    -- 函数体
    RETURN 返回值;
END$$
DELIMITER ;

其中：

DETERMINISTIC：指定函数是否是确定性的，即相同的输入是否总是产生相同的输出
RETURNS：指定函数的返回数据类型
RETURN：在函数体中使用，用于返回结果

示例：创建和使用存储函数

-- 创建一个计算员工年薪的函数
DELIMITER $$
CREATE FUNCTION CalculateAnnualSalary(monthly_salary DECIMAL(10,2), bonus DECIMAL(10,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    DECLARE annual_salary DECIMAL(12,2);
    SET annual_salary = (monthly_salary * 12) + bonus;
    RETURN annual_salary;
END$$
DELIMITER ;

-- 在SELECT语句中使用存储函数
SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary, 
    bonus, 
    CalculateAnnualSalary(salary, bonus) AS annual_salary 
FROM employees;

-- 创建一个验证邮箱格式的函数
DELIMITER $$
CREATE FUNCTION IsValidEmail(email VARCHAR(100))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE is_valid BOOLEAN;
    SET is_valid = email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$';
    RETURN is_valid;
END$$
DELIMITER ;

-- 使用函数验证邮箱
SELECT 
    email, 
    IsValidEmail(email) AS email_is_valid 
FROM users;

提示：存储函数和存储过程的主要区别在于，存储函数必须返回一个值，而存储过程可以返回多个值或不返回值；存储函数可以在SQL语句中使用，而存储过程需要使用CALL语句调用。

8.5 触发器概述

触发器是与表关联的特殊存储过程，当表上发生特定事件（如INSERT、UPDATE或DELETE）时自动执行。触发器可以用于实现数据的完整性约束、审计跟踪、级联更新等功能。

触发器的类型

INSERT触发器：在向表中插入数据时触发
UPDATE触发器：在更新表中数据时触发
DELETE触发器：在删除表中数据时触发

触发器的触发时间

BEFORE触发器：在事件发生之前执行
AFTER触发器：在事件发生之后执行

8.6 创建触发器

在MySQL中，创建触发器使用CREATE TRIGGER语句。其基本语法如下：

DELIMITER $$
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
BEGIN
    -- 触发器体
END$$
DELIMITER ;

其中：

触发器名称：触发器的标识符
BEFORE|AFTER：指定触发器是在事件发生之前还是之后触发
INSERT|UPDATE|DELETE：指定触发事件的类型
表名：触发器关联的表名
FOR EACH ROW：表示对每一行受影响的记录都执行触发器

示例：创建各种类型的触发器

-- 创建一个BEFORE INSERT触发器，自动设置插入时间
-- 假设有一个products表，包含id, name, price, created_at字段
DELIMITER $$
CREATE TRIGGER before_product_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END$$
DELIMITER ;

-- 创建一个AFTER INSERT触发器，记录插入操作日志
-- 假设有一个audit_log表，包含id, table_name, operation, record_id, operated_at字段
DELIMITER $$
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, record_id, operated_at)
    VALUES ('employees', 'INSERT', NEW.employee_id, NOW());
END$$
DELIMITER ;

-- 创建一个BEFORE UPDATE触发器，验证更新的数据
DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 验证工资不能为负数
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '工资不能为负数';
    END IF;
    
    -- 记录工资变更（如果工资发生了变化）
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO salary_history (employee_id, old_salary, new_salary, changed_at)
        VALUES (NEW.employee_id, OLD.salary, NEW.salary, NOW());
    END IF;
END$$
DELIMITER ;

-- 创建一个AFTER DELETE触发器，实现级联删除
-- 假设员工删除后，需要删除其相关的绩效记录
DELIMITER $$
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    -- 删除相关的绩效记录
    DELETE FROM performance_reviews WHERE employee_id = OLD.employee_id;
    -- 记录删除日志
    INSERT INTO audit_log (table_name, operation, record_id, operated_at)
    VALUES ('employees', 'DELETE', OLD.employee_id, NOW());
END$$
DELIMITER ;

提示：在触发器中，可以使用NEW和OLD关键字来访问触发事件前后的数据。对于INSERT触发器，只有NEW可用；对于DELETE触发器，只有OLD可用；对于UPDATE触发器，NEW和OLD都可用。

8.7 查看存储过程和触发器

在MySQL中，可以通过以下方法查看存储过程和触发器的信息：

查看存储过程

-- 查看所有存储过程
SHOW PROCEDURE STATUS;

-- 查看特定数据库的存储过程
SHOW PROCEDURE STATUS WHERE db = 'your_database_name';

-- 查看存储过程的创建语句
SHOW CREATE PROCEDURE procedure_name;

查看存储函数

-- 查看所有存储函数
SHOW FUNCTION STATUS;

-- 查看特定数据库的存储函数
SHOW FUNCTION STATUS WHERE db = 'your_database_name';

-- 查看存储函数的创建语句
SHOW CREATE FUNCTION function_name;

查看触发器

-- 查看所有触发器
SHOW TRIGGERS;

-- 查看特定数据库的触发器
SHOW TRIGGERS WHERE db = 'your_database_name';

-- 查看触发器的创建语句
SHOW CREATE TRIGGER trigger_name;

-- 从information_schema中查询触发器信息
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_database_name';

8.8 修改和删除存储过程和触发器

修改存储过程和函数

在MySQL中，不能直接修改存储过程或函数的定义。如果需要修改，必须先删除然后重新创建。

删除存储过程

DROP PROCEDURE [IF EXISTS] procedure_name;

删除存储函数

DROP FUNCTION [IF EXISTS] function_name;

删除触发器

DROP TRIGGER [IF EXISTS] trigger_name;

示例：修改和删除存储过程和触发器

-- 删除存储过程
DROP PROCEDURE IF EXISTS GetAllEmployees;

-- 删除存储函数
DROP FUNCTION IF EXISTS CalculateAnnualSalary;

-- 删除触发器
DROP TRIGGER IF EXISTS before_product_insert;

-- 修改存储过程（先删除后创建）
DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;
DELIMITER $$
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT employee_id, first_name, last_name, salary, hire_date
    FROM employees 
    WHERE department = dept_name
    ORDER BY hire_date DESC;
END$$
DELIMITER ;

8.9 存储过程和触发器的最佳实践

命名规范：使用清晰、一致的命名规范，便于识别存储过程或触发器的用途和功能
文档注释：为存储过程和触发器添加详细的注释，说明其功能、参数、返回值等
错误处理：在存储过程和触发器中添加适当的错误处理逻辑，提高代码的健壮性
性能考虑：避免在触发器中执行复杂的操作，以免影响数据操作的性能
安全性：限制存储过程和触发器的权限，只授予必要的用户执行权限
测试：在部署到生产环境之前，对存储过程和触发器进行充分的测试
维护：定期审查和优化存储过程和触发器，确保其性能和功能满足需求

示例：带错误处理的存储过程

DELIMITER $$
CREATE PROCEDURE TransferMoney(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    -- 声明退出处理程序，发生错误时回滚事务
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '转账失败：发生错误' AS message;
    END;
    
    -- 开启事务
    START TRANSACTION;
    
    -- 检查转出账户余额是否足够
    IF (SELECT balance FROM accounts WHERE account_id = from_account) < amount THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '余额不足，转账失败';
    END IF;
    
    -- 检查账户是否存在
    IF (SELECT COUNT(*) FROM accounts WHERE account_id = from_account) = 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '转出账户不存在';
    END IF;
    
    IF (SELECT COUNT(*) FROM accounts WHERE account_id = to_account) = 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '转入账户不存在';
    END IF;
    
    -- 执行转账操作
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
    
    -- 记录转账日志
    INSERT INTO transfer_log (from_account, to_account, amount, transfer_time)
    VALUES (from_account, to_account, amount, NOW());
    
    -- 提交事务
    COMMIT;
    
    SELECT '转账成功' AS message;
END$$
DELIMITER ;

8.10 MySQL 8.0对存储过程和触发器的增强

MySQL 8.0相比之前的版本，对存储过程和触发器提供了一些增强功能：

支持更多的数据类型：MySQL 8.0支持JSON、GIS等新的数据类型在存储过程和触发器中使用
增强的错误处理：提供了更丰富的错误处理机制，如SIGNAL和RESIGNAL语句
性能优化：优化了存储过程和触发器的执行性能
安全增强：提供了更细粒度的权限控制和安全检查
支持窗口函数：在存储过程和触发器中可以使用窗口函数进行复杂的数据分析

提示：MySQL 8.0引入了角色管理功能，可以将一组权限授予一个角色，然后将该角色授予用户。这使得管理存储过程和触发器的权限更加方便和灵活。