一、存储过程概述

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

1、输入输出参数：存储过程可以有输入参数、输出参数和输入输出参数。

2、变量声明：可以在存储过程中声明变量。

3、控制语句：支持if/else、case、while等控制语句，可以实现复杂的逻辑功能。

4、模块化与封装：通过编写存储过程，可以将常用的SQL操作封装起来，实现代码的重用。

5、编译优化：存储过程在首次执行时会进行编译和优化，后续调用时直接执行，提高了运行速度。

6、跨平台性：由于不同数据库系统的SQL语法存在差异，存储过程在不同数据库系统之间的移植性较差。

二、创建和使用存储过程

1. 基本语法

DELIMITER $$
CREATE PROCEDURE procedure_name(
    [IN | OUT | INOUT] parameter_name datatype, 
    ...
)
BEGIN
    -- 存储过程体
END$$
DELIMITER ;

DELIMITER $$：改变默认的语句结束符，从;改为$$，这样可以在存储过程中使用;而不会结束定义。

CREATE PROCEDURE procedure_name：定义一个名为procedure_name的存储过程。

[IN | OUT | INOUT] parameter_name datatype：参数列表，可以包含输入参数（IN）、输出参数（OUT）和输入输出参数（INOUT）。

BEGIN ... END：存储过程的主体，包含一系列SQL语句。

DELIMITER ;：恢复默认的语句结束符。

2. 示例

创建一个简单的存储过程，用于查询员工信息：

DELIMITER $$
CREATE PROCEDURE GetEmployeeDetails()
BEGIN
    SELECT * FROM employees;
END$$
DELIMITER ;

调用存储过程：

CALL GetEmployeeDetails();

三、存储过程中的变量

1. 局部变量

局部变量是在存储过程内部定义的变量，作用范围仅限于存储过程内部。

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

2. 用户变量

用户变量以@开头，可以在存储过程外部和内部使用。

SET @employee_id = 1;
DELIMITER $$
CREATE PROCEDURE GetEmployeeById()
BEGIN
    SELECT * FROM employees WHERE id = @employee_id;
END$$
DELIMITER ;
CALL GetEmployeeById();

3. 系统变量

系统变量以@@开头，用于设置或获取系统级别的配置信息，查看当前数据库中的自动递增值：

SHOW VARIABLES LIKE 'auto_increment%';

四、存储过程的传参类型

1. IN参数

IN参数表示传入存储过程的参数，存储过程内部不能修改该参数的值。

CREATE PROCEDURE GreetEmployee(IN emp_name VARCHAR(50))
BEGIN
    SELECT CONCAT('Hello, ', emp_name) AS greeting;
END$$

2. OUT参数

OUT参数表示存储过程内部可以修改该参数的值，并且修改后的值可以返回给调用者。

CREATE PROCEDURE CalculateSquare(IN num INT, OUT square INT)
BEGIN
    SET square = num * num;
END$$

调用时需要使用用户变量来接收OUT参数：

CALL CalculateSquare(5, @result);
SELECT @result;

3. INOUT参数

INOUT参数既可以作为输入参数传入，也可以在存储过程中被修改，并且修改后的值可以返回给调用者。

CREATE PROCEDURE IncrementCounter(INOUT counter INT)
BEGIN
    SET counter = counter + 1;
END$$

调用时同样需要使用用户变量：

SET @counter = 10;
CALL IncrementCounter(@counter);
SELECT @counter;

五、存储过程的控制语句

1. IF语句

CREATE PROCEDURE CheckNumber(IN num INT)
BEGIN
    IF num > 0 THEN
        SELECT 'Positive';
    ELSEIF num < 0 THEN
        SELECT 'Negative';
    ELSE
        SELECT 'Zero';
    END IF;
END$$

2. CASE语句

CREATE PROCEDURE EvaluateGrade(IN score INT)
BEGIN
    CASE 
        WHEN score >= 90 THEN SELECT 'A';
        WHEN score >= 80 THEN SELECT 'B';
        WHEN score >= 70 THEN SELECT 'C';
        WHEN score >= 60 THEN SELECT 'D';
        ELSE SELECT 'F';
    END CASE;
END$$

3. WHILE循环

CREATE PROCEDURE PrintNumbers(IN max_num INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    WHILE counter <= max_num DO
        SELECT counter;
        SET counter = counter + 1;
    END WHILE;
END$$

六、存储过程的高级应用

1. 事务管理

存储过程可以用于事务管理，确保数据的一致性和完整性，将转账操作封装在存储过程中：

CREATE PROCEDURE TransferMoney(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
    
    COMMIT;
END$$

2. 动态SQL

有时需要在存储过程中执行动态构建的SQL语句，可以使用PREPARE和EXECUTE语句：

CREATE PROCEDURE DynamicQuery(IN table_name VARCHAR(64), IN column_name VARCHAR(64))
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', table_name, ' WHERE ', column_name, ' IS NOT NULL');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

3. 异常处理

MySQL 5.5及以上版本支持内置的错误处理机制，如DECLARE ... HANDLER：

CREATE PROCEDURE SafeDivide(IN a INT, IN b INT, OUT result DOUBLE)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET result = NULL;
    SET result = a / b;
END$$

1. 优点与缺点

优点：

复用性：一次编写，多次调用，减少代码重复。

安全性：封装业务逻辑，限制对敏感数据的直接访问。

性能优化：预编译和缓存，提高执行效率。

维护方便：集中管理，易于修改和调试。

缺点：

可移植性差：不同数据库的SQL语法差异大，导致存储过程难以跨平台移植。

调试困难：存储过程内部的错误定位不如应用程序层直观。

资源消耗：大量使用存储过程可能导致数据库服务器负担加重。

业务逻辑复杂：过度依赖存储过程可能使业务逻辑过于集中在数据库层，不利于分层架构的设计。

- **可测试