【mysql知识】mysql的存储过程详细说明

概述

MySQL 存储过程（Stored Procedure）是一组预编译的 SQL 语句，存储在数据库中，可以通过调用来执行。存储过程可以提高代码的复用性、安全性和性能。以下是 MySQL 存储过程的详细开发说明。

1. 存储过程的基本结构

存储过程的基本语法如下：

DELIMITER //

CREATE PROCEDURE procedure_name([IN | OUT | INOUT] parameter_name data_type, ...)
BEGIN
    -- 存储过程的逻辑代码
END //

DELIMITER ;

• DELIMITER：修改默认的语句结束符（;），以便在存储过程中使用分号。
• CREATE PROCEDURE：定义存储过程。
• procedure_name：存储过程的名称。
• parameter_name：参数名称，可以是 IN（输入）、OUT（输出）或 INOUT（输入输出）。
• data_type：参数的数据类型（如 INT, VARCHAR, DATETIME 等）。
• BEGIN … END：存储过程的主体部分，包含 SQL 语句和逻辑。

2. 存储过程的参数

存储过程支持三种类型的参数：

• IN：输入参数，调用者传递给存储过程的值（默认类型）。
• OUT：输出参数，存储过程返回给调用者的值。
• INOUT：既是输入参数，也是输出参数。

示例：

DELIMITER //

CREATE PROCEDURE GetEmployeeName(IN emp_id INT, OUT emp_name VARCHAR(100))
BEGIN
    SELECT name INTO emp_name FROM employees WHERE id = emp_id;
END //

DELIMITER ;

调用存储过程：

CALL GetEmployeeName(1, @name);
SELECT @name; -- 输出结果

3. 存储过程的变量

在存储过程中，可以使用 DECLARE 定义局部变量：

DECLARE variable_name data_type [DEFAULT value];

示例：

DELIMITER //

CREATE PROCEDURE CalculateBonus(IN emp_id INT, OUT bonus DECIMAL(10, 2))
BEGIN
    DECLARE base_salary DECIMAL(10, 2);
    DECLARE performance_rating INT;

    -- 获取基本工资和绩效评分
    SELECT salary, performance INTO base_salary, performance_rating FROM employees WHERE id = emp_id;

    -- 计算奖金
    SET bonus = base_salary * performance_rating * 0.1;
END //

DELIMITER ;

4. 控制流语句

存储过程支持常见的控制流语句，如 IF、CASE、LOOP、WHILE 和 REPEAT。

IF 语句

IF condition THEN
    -- 逻辑代码
ELSEIF condition THEN
    -- 逻辑代码
ELSE
    -- 逻辑代码
END IF;

示例：

DELIMITER //

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

    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;

    IF emp_salary > 10000 THEN
        SELECT 'High Salary' AS result;
    ELSE
        SELECT 'Low Salary' AS result;
    END IF;
END //

DELIMITER ;

CASE 语句

CASE
    WHEN condition THEN
        -- 逻辑代码
    WHEN condition THEN
        -- 逻辑代码
    ELSE
        -- 逻辑代码
END CASE;

循环语句

• LOOP：无限循环，需要手动退出。
• WHILE：条件循环。
• REPEAT：先执行后判断条件。

示例：

DELIMITER //

CREATE PROCEDURE SumNumbers(IN n INT, OUT total INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    SET total = 0;

    WHILE counter <= n DO
        SET total = total + counter;
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

5. 异常处理

MySQL 存储过程支持通过 DECLARE ... HANDLER 定义异常处理程序。

DECLARE action HANDLER FOR condition_value statement;

• action：CONTINUE 或 EXIT。
• condition_value：错误代码或条件（如 SQLSTATE 或 SQLEXCEPTION）。
• statement：发生错误时执行的语句。

示例：

DELIMITER //

CREATE PROCEDURE SafeInsert(IN emp_name VARCHAR(100))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'An error occurred, transaction rolled back!' AS message;
        ROLLBACK;
    END;

    START TRANSACTION;
    INSERT INTO employees(name) VALUES(emp_name);
    COMMIT;
END //

DELIMITER ;

6. 调用存储过程

使用 CALL 语句调用存储过程：

CALL procedure_name([parameter1, parameter2, ...]);

示例：

CALL CalculateBonus(1, @bonus);
SELECT @bonus;

7. 查看和删除存储过程

查看存储过程

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

查看存储过程的定义：

SHOW CREATE PROCEDURE procedure_name;

删除存储过程

DROP PROCEDURE IF EXISTS procedure_name;

8. 存储过程的优点

1. 代码复用：存储过程可以在多个地方调用，减少重复代码。
2. 性能优化：存储过程在数据库中预编译，执行效率高。
3. 安全性：通过存储过程可以限制对底层数据的直接访问。
4. 事务管理：存储过程可以包含事务逻辑，确保数据一致性。

9. 示例：完整的存储过程

以下是一个完整的存储过程示例，用于插入员工记录并返回新记录的 ID：

DELIMITER //

CREATE PROCEDURE InsertEmployee(IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10, 2), OUT new_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'An error occurred, transaction rolled back!' AS message;
        ROLLBACK;
    END;

    START TRANSACTION;
    INSERT INTO employees(name, salary) VALUES(emp_name, emp_salary);
    SET new_id = LAST_INSERT_ID();
    COMMIT;
END //

DELIMITER ;

调用存储过程：

CALL InsertEmployee('John Doe', 5000, @new_id);
SELECT @new_id;

通过以上步骤，你可以开发复杂的 MySQL 存储过程，满足业务需求。

相关文献

Mysql基础操作-常见SQL语句