4.2 MySQL存储过程

存储过程是MySQL中用于封装一组 SQL 语句的数据库对象，便于简化重复任务、增强性能和逻辑复用。本文将从多个角度详细解析存储过程的功能、语法和应用场景。

---

1. 存储过程概述

1.1 什么是存储过程

存储过程（Stored Procedure）是一段在数据库中保存的SQL语句集合，用户通过调用存储过程来执行这些语句。它类似于编程语言中的函数，但运行在数据库内部。

1.2 存储过程的作用

• 提高代码复用性：常用的操作可以封装为存储过程，多次调用。
• 增强安全性：用户只需拥有执行存储过程的权限，而无需直接操作底层表。
• 减少网络流量：减少客户端与数据库之间的多次交互。
• 集中业务逻辑：将复杂逻辑从应用程序转移到数据库层，便于管理。

---

2. 存储过程基本语法

2.1 创建存储过程

DELIMITER $$

CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
    SQL语句块;
END$$

DELIMITER ;

• 参数列表：可选，包含输入/输出参数。
• DELIMITER：定义语句分隔符，避免语法冲突。
• SQL语句块：可以包含SELECT、INSERT、UPDATE等操作。

2.2 调用存储过程

CALL 存储过程名(参数值列表);

2.3 查看存储过程

SHOW PROCEDURE STATUS WHERE Db='数据库名';

2.4 删除存储过程

DROP PROCEDURE IF EXISTS 存储过程名;

---

3. 入参解析

3.1 参数类型

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

参数类型	描述	使用场景
IN	传递输入参数到存储过程（默认类型）。	传入数据供存储过程使用。
OUT	存储过程执行后返回结果，调用方可接收该值。	需要将结果返回给调用者。
INOUT	既可以传入初始值，又可修改后返回值。	参数值需要更新并返回调用者使用。

3.2 参数案例

DELIMITER $$

CREATE PROCEDURE param_example(
    IN param1 INT, 
    OUT param2 INT, 
    INOUT param3 INT
)
BEGIN
    SET param2 = param1 * 2;    -- 输出参数
    SET param3 = param3 + 10;  -- 输入输出参数
END$$

DELIMITER ;

CALL param_example(5, @out_val, @inout_val);
SELECT @out_val, @inout_val;

---

4. 存储过程中的变量

4.1 系统变量

查看系统变量

SHOW VARIABLES LIKE '%变量名%';

设置系统变量

SET GLOBAL max_connections = 200;

4.2 用户定义变量

说明

• 用户定义变量是以@开头的变量，作用域为会话级别。

设置变量

SET @var_name = '值';

使用变量

SELECT @var_name;

4.3 局部变量

声明变量

DECLARE var_name 数据类型 DEFAULT 默认值;

赋值变量

SET var_name = 值;

案例

DELIMITER $$

CREATE PROCEDURE var_example()
BEGIN
    DECLARE total INT DEFAULT 0;
    SET total = 10 + 20;
    SELECT total;
END$$

DELIMITER ;

CALL var_example();

---

5. 条件语句

5.1 IF语法

IF 条件 THEN
    语句;
ELSEIF 条件 THEN
    语句;
ELSE
    语句;
END IF;

案例

DELIMITER $$

CREATE PROCEDURE if_example(IN num INT)
BEGIN
    IF num > 10 THEN
        SELECT '大于10';
    ELSE
        SELECT '小于等于10';
    END IF;
END$$

DELIMITER ;

CALL if_example(15);

---

5.2 CASE语法

CASE
    WHEN 条件1 THEN 语句1;
    WHEN 条件2 THEN 语句2;
    ELSE 语句N;
END CASE;

案例

DELIMITER $$

CREATE PROCEDURE case_example(IN grade CHAR(1))
BEGIN
    CASE grade
        WHEN 'A' THEN SELECT '优秀';
        WHEN 'B' THEN SELECT '良好';
        ELSE SELECT '及格或不及格';
    END CASE;
END$$

DELIMITER ;

CALL case_example('A');

---

6. 循环语句

6.1 WHILE语法

WHILE 条件 DO
    语句;
END WHILE;

案例

DELIMITER $$

CREATE PROCEDURE while_example(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= num DO
        SELECT CONCAT('当前值: ', i);
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

CALL while_example(5);

---

6.2 REPEAT语法

REPEAT
    语句;
UNTIL 条件
END REPEAT;

案例

DELIMITER $$

CREATE PROCEDURE repeat_example(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    REPEAT
        SELECT CONCAT('当前值: ', i);
        SET i = i + 1;
    UNTIL i > num
    END REPEAT;
END$$

DELIMITER ;

CALL repeat_example(5);

---

6.3 LOOP语法

[标记:] LOOP
    语句;
    LEAVE 标记;  -- 退出循环
END LOOP;

多层Loop：

OUTER_LOOP: LOOP
    INNER_LOOP: LOOP
        IF 条件 THEN
            LEAVE OUTER_LOOP; -- 直接跳出外层循环
        END IF;
    END LOOP INNER_LOOP;
END LOOP OUTER_LOOP;

案例

DELIMITER $$

CREATE PROCEDURE loop_example(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    my_loop: LOOP
        SELECT CONCAT('当前值: ', i);
        SET i = i + 1;
        IF i > num THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
END$$

DELIMITER ;

CALL loop_example(5);

---

7. 游标（Cursor）

7.1 游标的概念

游标用于处理查询结果集中的每一行数据，通常用于逐行处理复杂的业务逻辑。

7.2 游标操作语法

• 声明游标：DECLARE cursor_name CURSOR FOR 查询语句;
• 打开游标：OPEN cursor_name;
• 获取数据：FETCH cursor_name INTO 变量列表;
• 关闭游标：CLOSE cursor_name;

案例

DELIMITER $$

CREATE PROCEDURE cursor_example()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_name VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT name FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT emp_name;
    END LOOP read_loop;

    CLOSE cur;
END$$

DELIMITER ;

CALL cursor_example();

---

8. 条件处理语句（Handler）

8.1 含义

条件处理器用于捕获存储过程执行中的错误或特殊情况。

8.2 语法

DECLARE HANDLER_ACTION HANDLER FOR CONDITION_VALUE 语句;

• HANDLER_ACTION：
  • CONTINUE：继续执行下一语句。
  • EXIT：退出当前存储过程。
• CONDITION_VALUE：
  • SQLEXCEPTION：捕获所有SQL错误。
  • SQLSTATE sqlstate_value: 状态码，如 02000
  • SQLWARNING: 所有以01开头的SQLSTATE代码的简写
  • NOT FOUND: 所有以02开头的SQLSTATE代码的简写
  • SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

案例

DELIMITER $$

CREATE PROCEDURE handler_example()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT '发生错误！';
    END;

    -- 错误SQL
    SELECT * FROM non_existent_table;
END$$

DELIMITER ;

CALL handler_example();

---

以上内容涵盖了MySQL存储过程的核心知识及常用语法，希望对您有所帮助！