MySQL存储过程编写入门到实战

存储过程（Stored Procedure）是MySQL中一种非常强大的功能，它允许用户将一组SQL语句和逻辑封装成一个可重用的单元。通过存储过程，可以减少网络流量、提高执行效率，并且使数据库操作更加安全和易于维护。本文将从入门到实战逐步讲解如何编写MySQL存储过程。

一、存储过程的基本概念

存储过程是一组预编译的SQL语句，存储在数据库服务器中，可以通过调用名称来执行。与普通的SQL语句相比，存储过程具有以下优点：

1. 性能提升：存储过程在第一次调用时会被编译并缓存，后续调用无需重新解析。
2. 减少网络流量：客户端只需发送存储过程的名称和参数，而不是完整的SQL语句。
3. 增强安全性：通过权限控制，可以限制对存储过程的访问。
4. 代码复用性：复杂的业务逻辑可以封装到存储过程中，便于重复使用。

二、创建存储过程

1. 基本语法

创建存储过程的语法如下：

DELIMITER $$

CREATE PROCEDURE 存储过程名 (IN|OUT|INOUT 参数名 数据类型)
BEGIN
    -- SQL语句或逻辑
END$$

DELIMITER ;

• DELIMITER $$：用于更改语句结束符，默认为分号;，但在存储过程中可能包含多个分号，因此需要临时修改结束符。
• IN：表示输入参数。
• OUT：表示输出参数。
• INOUT：既可以作为输入参数，也可以作为输出参数。

2. 示例：简单存储过程

假设有一个名为employees的表，结构如下：

我们创建一个简单的存储过程，用于查询某个员工的薪水：

DELIMITER $$

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT, OUT emp_salary DECIMAL(10, 2))
BEGIN
    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;
END$$

DELIMITER ;

调用该存储过程：

CALL GetEmployeeSalary(1, @salary);
SELECT @salary;

上述代码中，@salary是一个用户变量，用于接收存储过程返回的薪水值。

三、存储过程中的控制流

存储过程支持多种控制流语句，包括条件判断、循环等。

1. 条件判断（IF...THEN...ELSE）

DELIMITER $$

CREATE PROCEDURE CheckSalary(IN emp_id INT, OUT result VARCHAR(50))
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);

    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;

    IF emp_salary > 5000 THEN
        SET result = 'High Salary';
    ELSE
        SET result = 'Low Salary';
    END IF;
END$$

DELIMITER ;

调用示例：

CALL CheckSalary(1, @result);
SELECT @result;

2. 循环（LOOP、WHILE、REPEAT）

使用WHILE循环插入数据

DELIMITER $$

CREATE PROCEDURE InsertData()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 10 DO
        INSERT INTO employees (name, salary) VALUES(CONCAT('User', i), i * 1000);
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

调用后，employees表将新增10条记录。

四、错误处理

在存储过程中，可以通过DECLARE HANDLER定义错误处理逻辑。例如，捕获特定错误并采取措施：

DELIMITER $$

CREATE PROCEDURE SafeInsert(IN emp_name VARCHAR(50), IN emp_salary DECIMAL(10, 2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'An error occurred during insertion' AS ErrorMessage;
    END;

    START TRANSACTION;
    INSERT INTO employees (name, salary) VALUES(emp_name, emp_salary);
    COMMIT;
END$$

DELIMITER ;

五、删除存储过程

如果不再需要某个存储过程，可以使用DROP PROCEDURE语句将其删除：

DROP PROCEDURE IF EXISTS 存储过程名;

六、实战案例：批量更新工资

假设需要给所有员工加薪10%，可以编写如下存储过程：

DELIMITER $$

CREATE PROCEDURE UpdateSalaries()
BEGIN
    UPDATE employees SET salary = salary * 1.1;
END$$

DELIMITER ;

调用存储过程：

CALL UpdateSalaries();