数据库实验七：存储过程实验：原理、实现与优化

一、存储过程概述

存储过程（Stored Procedure）是数据库系统中一种重要的数据库对象，它是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程具有以下特点：

1. 预编译执行：存储过程在首次创建时进行编译，后续调用直接执行编译后的代码，提高了执行效率。
2. 减少网络流量：客户端只需传递存储过程名和参数，避免了发送大量SQL语句。
3. 增强安全性：可以通过授权方式控制对存储过程的访问，隐藏底层数据表结构。
4. 代码复用：一次创建多次调用，减少代码冗余。

在本次实验中，我们将重点学习MySQL环境下存储过程的创建、调用和管理方法。

二、存储过程语法详解

2.1 基本语法结构

DELIMITER //
CREATE PROCEDURE 过程名([参数列表])
BEGIN
    -- SQL语句块
END //
DELIMITER ;

关键要素说明：

• DELIMITER：修改语句结束符，避免与存储过程中的分号冲突
• 参数格式：[IN|OUT|INOUT] 参数名 数据类型
• BEGIN...END：定义存储过程体

2.2 参数类型

1. IN参数（默认）：输入参数，调用时传入值
2. OUT参数：输出参数，返回给调用者
3. INOUT参数：既作为输入又作为输出

2.3 变量声明与使用

DECLARE 变量名 数据类型 [DEFAULT 默认值];
SET 变量名 = 值;

三、实验案例：员工管理系统存储过程

3.1 实验环境准备

创建测试表：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

3.2 案例1：基本存储过程

创建查询特定部门员工的存储过程：

DELIMITER //
CREATE PROCEDURE GetDeptEmployees(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees WHERE department = dept_name;
END //
DELIMITER ;

调用方法：

CALL GetDeptEmployees('研发部');

3.3 案例2：带输出参数的存储过程

创建计算部门平均工资的存储过程：

DELIMITER //
CREATE PROCEDURE GetDeptAvgSalary(
    IN dept_name VARCHAR(50),
    OUT avg_salary DECIMAL(10,2)
)
BEGIN
    SELECT AVG(salary) INTO avg_salary 
    FROM employees 
    WHERE department = dept_name;
END //
DELIMITER ;

调用方法：

CALL GetDeptAvgSalary('研发部', @avg);
SELECT @avg;

3.4 案例3：事务处理存储过程

创建员工调薪事务处理：

DELIMITER //
CREATE PROCEDURE AdjustSalary(
    IN emp_id INT,
    IN amount DECIMAL(10,2),
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result = '调薪失败';
    END;
    START TRANSACTION;
    UPDATE employees SET salary = salary + amount WHERE id = emp_id;
    INSERT INTO salary_log(emp_id, change_amount, change_date)
    VALUES(emp_id, amount, NOW());
    COMMIT;
    SET result = '调薪成功';
END //
DELIMITER ;

四、存储过程优化策略

1. 参数优化：

   • 合理选择参数类型（IN/OUT/INOUT）
   • 避免使用过大的参数

2. SQL优化：

   • 在存储过程中使用EXPLAIN分析查询
   • 为常用查询条件建立索引

3. 错误处理：

   • 使用DECLARE HANDLER处理异常
   • 记录错误日志

4. 性能监控：

   • 使用SHOW PROCEDURE STATUS查看存储过程信息
   • 通过performance_schema监控执行时间

五、常见问题与解决方案

1. 权限问题：

   • 确保用户有CREATE ROUTINE权限
   • 执行时需要有EXECUTE权限

2. 调试技巧：

   • 使用SELECT输出中间结果
   • 分步测试复杂逻辑

3. 版本兼容性：

   • 注意不同MySQL版本的语法差异
   • 使用DELIMITER解决分号冲突

六、实验总结

通过本次实验，我们系统掌握了：

1. 存储过程的基本语法和创建方法
2. 各种参数类型的使用场景
3. 存储过程中的事务控制和错误处理
4. 性能优化和调试技巧

存储过程作为数据库应用开发的重要技术，能够显著提高系统性能和安全性。建议在实际项目中合理使用存储过程，但也要注意避免过度使用导致维护困难。

七、扩展练习

1. 设计一个分页查询的存储过程
2. 实现数据统计报表生成的存储过程
3. 创建定时执行的存储过程（结合事件调度器）
4. 研究存储过程与函数的区别