MySQL 存储过程使用指南

存储过程是 MySQL 中一组预编译的 SQL 语句集合，可以像函数一样被调用。下面详细介绍如何在 MySQL 中使用存储过程。

1. 创建存储过程

基本语法：

DELIMITER //

CREATE PROCEDURE 过程名([参数列表])
BEGIN
    -- 存储过程体
    -- 可以包含SQL语句、流程控制语句等
END //

DELIMITER ;

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

DELIMITER //

CREATE PROCEDURE GetCustomerCount()
BEGIN
    SELECT COUNT(*) AS total_customers FROM customers;
END //

DELIMITER ;

2. 存储过程参数

存储过程可以接受三种类型的参数： - IN (输入参数，默认类型) - OUT (输出参数) - INOUT (既可输入又可输出)

示例：

DELIMITER //

CREATE PROCEDURE UpdateSalary(
    IN emp_id INT,
    IN increase_amount DECIMAL(10,2),
    OUT new_salary DECIMAL(10,2)
)
BEGIN
    DECLARE current_salary DECIMAL(10,2);

    SELECT salary INTO current_salary FROM employees WHERE id = emp_id;
    SET new_salary = current_salary + increase_amount;

    UPDATE employees SET salary = new_salary WHERE id = emp_id;
END //

DELIMITER ;

3. 调用存储过程

基本语法：

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

示例：

-- 调用无参存储过程
CALL GetCustomerCount();

-- 调用带参数的存储过程
CALL UpdateSalary(101, 500.00, @result);
SELECT @result;

4. 变量声明与使用

在存储过程中可以声明局部变量：

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

示例：

DELIMITER //

CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)
BEGIN
    DECLARE subtotal DECIMAL(10,2);
    DECLARE tax DECIMAL(10,2);
    DECLARE total DECIMAL(10,2);

    SELECT SUM(price * quantity) INTO subtotal FROM order_items WHERE order_id = order_id;
    SET tax = subtotal * 0.08;
    SET total = subtotal + tax;

    SELECT subtotal, tax, total;
END //

DELIMITER ;

5. 流程控制

存储过程中可以使用流程控制语句：

IF 语句

IF 条件 THEN
    语句块
[ELSEIF 条件 THEN
    语句块]
[ELSE
    语句块]
END IF;

CASE 语句

CASE 
    WHEN 条件 THEN 语句块
    [WHEN 条件 THEN 语句块]
    [ELSE 语句块]
END CASE;

循环语句

-- WHILE 循环
WHILE 条件 DO
    语句块
END WHILE;

-- REPEAT 循环
REPEAT
    语句块
UNTIL 条件
END REPEAT;

-- LOOP 循环
[标签:] LOOP
    语句块
    IF 条件 THEN
        LEAVE [标签]; -- 相当于break
    END IF;

    ITERATE [标签]; -- 相当于continue
END LOOP;

6. 查看和删除存储过程

查看存储过程：

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

-- 查看特定存储过程的定义
SHOW CREATE PROCEDURE 过程名;

删除存储过程：

DROP PROCEDURE [IF EXISTS] 过程名;

7. 存储过程的优点

1. 提高性能：预编译执行，减少解析和编译时间
2. 减少网络流量：客户端只需发送调用命令，而非多条SQL语句
3. 增强安全性：可以限制用户对基础表的直接访问
4. 代码复用：一次编写，多次调用
5. 事务处理：可以在存储过程中实现复杂的事务逻辑

8. 实际应用示例

DELIMITER //

CREATE PROCEDURE ProcessMonthlyPayroll(IN month INT, IN year INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE emp_bonus DECIMAL(10,2);
    DECLARE total_payroll DECIMAL(10,2) DEFAULT 0;

    -- 声明游标
    DECLARE emp_cursor CURSOR FOR 
        SELECT id, salary FROM employees WHERE active = 1;

    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS payroll_results (
        employee_id INT,
        salary DECIMAL(10,2),
        bonus DECIMAL(10,2),
        total_payment DECIMAL(10,2)
    );

    -- 打开游标
    OPEN emp_cursor;

    -- 开始循环处理每个员工
    emp_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_salary;
        IF done THEN
            LEAVE emp_loop;
        END IF;

        -- 计算奖金(假设12月有年终奖)
        IF month = 12 THEN
            SET emp_bonus = emp_salary * 0.5;
        ELSE
            SET emp_bonus = emp_salary * 0.1;
        END IF;

        -- 插入结果
        INSERT INTO payroll_results VALUES (emp_id, emp_salary, emp_bonus, emp_salary + emp_bonus);

        -- 累计总工资
        SET total_payroll = total_payroll + emp_salary + emp_bonus;
    END LOOP;

    -- 关闭游标
    CLOSE emp_cursor;

    -- 输出结果
    SELECT * FROM payroll_results;
    SELECT total_payroll AS company_total_payroll;

    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS payroll_results;
END //

DELIMITER ;

通过以上内容，您应该能够开始在 MySQL 中使用存储过程来提高数据库操作的效率和可维护性。