【进阶版】使用存储过程查数据全流程

使用存储过程查数据全流程,以下是一个复杂的 存储过程 示例，包含以下功能：

1. 创建数据库和表
2. 插入测试数据
3. 创建存储过程
   • 带输入（IN）、输出（OUT）和输入输出（INOUT）参数
   • 使用游标（Cursor）处理多行数据
   • 使用条件逻辑（IF, CASE, WHILE, LOOP, REPEAT）
   • 使用异常处理（DECLARE CONTINUE HANDLER）
4. 调用存储过程
5. 优化与注意事项

---

1. 创建数据库和表

首先，创建 company_db 数据库，并创建 employees（员工表）和 departments（部门表）。

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

-- 创建部门表
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(50) UNIQUE NOT NULL
);

-- 创建员工表
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

---

2. 插入测试数据

插入一些部门和员工数据。

-- 插入部门数据
INSERT INTO departments (dept_name) VALUES
('技术部'), ('市场部'), ('财务部');

-- 插入员工数据
INSERT INTO employees (emp_name, dept_id, salary, hire_date) VALUES
('张三', 1, 9000.00, '2020-01-15'),
('李四', 2, 7500.50, '2021-07-10'),
('王五', 1, 12000.00, '2019-09-25'),
('赵六', 3, 7800.00, '2022-03-18'),
('孙七', 1, 15000.00, '2018-06-30'),
('周八', 2, 6700.00, '2023-05-20');

---

3. 创建复杂存储过程

这个存储过程 ManageEmployeeSalaries 将：

• 输入参数（IN）：部门名称
• 输入输出参数（INOUT）：平均工资
• 输出参数（OUT）：员工总数
• 使用游标 处理每个员工，并增加 10% 工资
• 使用异常处理 遇到错误时继续执行

存储过程代码

DELIMITER //

CREATE PROCEDURE ManageEmployeeSalaries(
    IN dept_name VARCHAR(50),  -- 传入的部门名称
    INOUT avg_salary DECIMAL(10,2), -- 传入当前平均工资，更新后返回新平均工资
    OUT total_employees INT   -- 返回该部门的员工总数
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE empID INT;
    DECLARE empName VARCHAR(50);
    DECLARE empSalary DECIMAL(10,2);
    
    -- 用于存储计算后的新平均工资
    DECLARE new_avg_salary DECIMAL(10,2);
    
    -- 游标用于遍历该部门的所有员工
    DECLARE emp_cursor CURSOR FOR 
        SELECT emp_id, emp_name, salary FROM employees 
        WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = dept_name);
    
    -- 异常处理，防止游标遍历完后出错
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 获取该部门的员工总数
    SELECT COUNT(*) INTO total_employees 
    FROM employees 
    WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = dept_name);

    -- 如果没有员工，则退出
    IF total_employees = 0 THEN
        SET avg_salary = 0;
        LEAVE proc_exit;
    END IF;
    
    -- 开启事务
    START TRANSACTION;

    -- 打开游标
    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO empID, empName, empSalary;
        IF done THEN 
            LEAVE read_loop;
        END IF;

        -- 给每个员工加薪 10%
        UPDATE employees 
        SET salary = empSalary * 1.1
        WHERE emp_id = empID;
        
        -- 输出调试信息（MySQL 8.0+ 支持）
        SELECT CONCAT('员工 ', empName, ' 加薪至 ', empSalary * 1.1) AS Update_Info;
    END LOOP;
    
    -- 关闭游标
    CLOSE emp_cursor;

    -- 重新计算新的平均工资
    SELECT AVG(salary) INTO new_avg_salary
    FROM employees
    WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = dept_name);
    
    -- 更新 INOUT 参数
    SET avg_salary = new_avg_salary;

    -- 提交事务
    COMMIT;
    
    proc_exit: END;

END //

DELIMITER ;

---

4. 调用存储过程

假设我们要给 技术部 的员工加薪，并计算新的平均工资：

SET @avg_salary = 0; -- 初始化平均工资变量
SET @total_employees = 0; -- 初始化员工总数变量

CALL ManageEmployeeSalaries('技术部', @avg_salary, @total_employees);

SELECT @avg_salary AS '新平均工资', @total_employees AS '员工总数';

结果示例

+------------+------------+
| 新平均工资  | 员工总数  |
+------------+------------+
| 11880.00   | 3         |
+------------+------------+

---

5. 细节优化

(1) 删除存储过程

如果需要修改存储过程，可以先删除：

DROP PROCEDURE IF EXISTS ManageEmployeeSalaries;

(2) 事务管理

如果某个更新失败，可以 ROLLBACK，但这里我们设置了 CONTINUE HANDLER，不会影响后续操作。

(3) 兼容性

• MySQL 5.7 及以下 可能不支持 SELECT CONCAT(...) AS Update_Info，可以改用 INSERT INTO log_table(...) 记录日志。
• 使用 LEAVE proc_exit; 处理空部门情况，避免不必要计算。

---

总结

✅ 创建数据库和表（CREATE TABLE）
✅ 插入数据（INSERT INTO）
✅ 创建存储过程：

• IN（输入参数）
• OUT（输出参数）
• INOUT（输入+输出参数）
• 游标 CURSOR 处理多行数据
• 事务管理 START TRANSACTION, COMMIT
• 错误处理 HANDLER
  ✅ 调用存储过程（CALL procedure_name）