Mysql之存储过程（下）

🏝️专栏：Mysql_猫咪-9527的博客-CSDN博客 🌅主页：猫咪-9527-CSDN博客 

“欲穷千里目，更上一层楼。会当凌绝顶，一览众山小。”

目录

4. 存储过程

4.1 IF 判断

4.2 存储过程参数

4.2.1 IN 参数使用

4.2.2 OUT 参数使用

4.2.3 INOUT 参数使用

4.3 CASE 语句

4.4 WHILE 循环

4.5 REPEAT 循环

4.6 LOOP 循环

5.游标

5.1  声明游标

5.2 打开游标

5.3 取出数据

5.4Handler (异常处理器)

5.4 关闭游标

5.5 游标的完整示例

6. 触发器

6.1 创建触发器

6.2. 查看、删除触发器

6.3. 注意事项与坑

4. 存储过程

存储过程是由一组 SQL 语句构成的数据库对象，存储过程可以被存储在数据库中并在需要时执行。它支持控制流结构（如 IF 语句、CASE 语句、循环等），能够封装和重用数据库操作，提升代码的模块化和可维护性。

4.1 IF 判断

IF 语句是 MySQL 存储过程中的一种常见控制流结构，用于根据特定条件执行不同的 SQL 语句。它可以单独使用，也可以与 ELSE 和 ELSEIF 配合使用，从而形成多分支的条件判断结构。

语法：

IF condition THEN
— 执行的 SQL 语句
ELSEIF condition THEN
— 执行的 SQL 语句
ELSE
— 执行的 SQL 语句
END IF;

示例：

DELIMITER $$

CREATE PROCEDURE CheckScore()
BEGIN
DECLARE score INT DEFAULT 58;
DECLARE result VARCHAR(10);

IF score >= 80 THEN
SET result := '优秀';
ELSEIF score >= 60 THEN
SET result := '及格';
ELSE
SET result := '不及格';
END IF;

SELECT result;
END$$

DELIMITER ;

4.2 存储过程参数

存储过程可以接受三种类型的参数：IN、OUT 和 INOUT。每种类型的参数有不同的作用，分别用于数据的输入、输出或同时输入输出。

语法：

CREATE PROCEDURE 存储过程名称 (IN/OUT/INOUT 参数名 参数类型)
BEGIN
— SQL 语句
END;

4.2.1 IN 参数使用

IN 参数用于向存储过程传递输入值，存储过程内部可以使用这些值进行计算或查询操作。

示例：

DELIMITER $$

CREATE PROCEDURE CheckScoreIN(IN score INT)
BEGIN
DECLARE result VARCHAR(10);

IF score >= 80 THEN
SET result := '优秀';
ELSEIF score >= 60 THEN
SET result := '及格';
ELSE
SET result := '不及格';
END IF;

SELECT result;
END$$

DELIMITER ;

4.2.2 OUT 参数使用

OUT 参数用于存储过程返回值。执行存储过程时，返回结果将通过 OUT 参数传递给调用者。

示例：

DELIMITER $$

CREATE PROCEDURE CheckScoreOUT(IN score INT, OUT result VARCHAR(20))
BEGIN
IF score >= 80 THEN
SET result := '优秀';
ELSEIF score >= 60 THEN
SET result := '及格';
ELSE
SET result := '不及格';
END IF;
END$$

DELIMITER ;

4.2.3 INOUT 参数使用

INOUT 参数既可以作为输入值，也可以用作存储过程返回的输出值。

示例：

DELIMITER $$

CREATE PROCEDURE CheckScoreINOUT(INOUT score DOUBLE)
BEGIN
SET score := score * 0.5;
END$$

DELIMITER ;

4.3 CASE 语句

CASE 语句是一种条件表达式，允许根据不同的条件选择不同的结果。它通常用于 SELECT 查询、UPDATE 语句或存储过程中的条件判断。

语法：

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
WHEN value3 THEN result3
ELSE default_result
END

示例：

DELIMITER $$

CREATE PROCEDURE CheckGrade(IN score INT, OUT grade VARCHAR(3))
BEGIN
CASE
WHEN score BETWEEN 90 AND 100 THEN SET grade := 'A';
WHEN score BETWEEN 80 AND 89 THEN SET grade := 'B';
WHEN score BETWEEN 60 AND 79 THEN SET grade := 'C';
WHEN score BETWEEN 0 AND 59 THEN SET grade := 'D';
ELSE SET grade := 'NO';
END CASE;
END$$

DELIMITER ;

4.4 WHILE 循环

WHILE 循环是 MySQL 中的一种常见控制结构，用于在指定条件为 TRUE 时重复执行一组 SQL 语句，直到条件不再满足为止。

语法：

WHILE condition DO
— 执行的 SQL 语句
END WHILE;

示例：

DELIMITER $$

CREATE PROCEDURE SumNumbers(IN n INT)
BEGIN
DECLARE a INT DEFAULT 0;
WHILE n >= 0 DO
SET a := a + n;
SET n := n – 1;
END WHILE;
SELECT a;
END$$

DELIMITER ;

4.5 REPEAT 循环

REPEAT 循环与 WHILE 循环类似，不同之处在于它在每次执行代码块后才检查条件，直到满足退出条件为止。

语法：

REPEAT
— 执行的 SQL 语句
UNTIL condition
END REPEAT;

示例：

DELIMITER $$

CREATE PROCEDURE SumNumbersUsingRepeat(IN n INT)
BEGIN
DECLARE a INT DEFAULT 0;
REPEAT
SET a := a + n;
SET n := n – 1;
UNTIL n <= 0
END REPEAT;
SELECT a;
END$$

DELIMITER ;

4.6 LOOP 循环

LOOP 循环是另一种控制结构，适用于需要反复执行某些操作，直到满足特定退出条件时停止。与 REPEAT 和 WHILE 循环不同，LOOP 必须显式使用 LEAVE 语句来退出循环，否则它会无限执行。

语法：

loop_label : LOOP
— 执行的 SQL 语句
IF condition THEN
LEAVE loop_label; — 退出循环
END IF;
END LOOP;

示例：

DELIMITER $$

CREATE PROCEDURE SumOddNumbers(IN n INT)
BEGIN
DECLARE a INT DEFAULT 0;
sum_loop: LOOP
IF n % 2 = 0 THEN
SET n := n – 1;
ITERATE sum_loop;
END IF;
SET a := a + n;
SET n := n – 1;
IF n = 0 THEN
LEAVE sum_loop;
END IF;
END LOOP;
SELECT a;
END$$

DELIMITER ;

5.游标

在 MySQL 中，游标（Cursor）是一种用于遍历查询结果集的机制。游标允许你逐行处理查询结果，对于一些复杂的操作，比如逐行处理数据、进行复杂的计算等，游标非常有用。通常，游标用于存储过程中，在需要逐行处理结果集时使用。                                     

5.1  声明游标

使用 DECLARE 语句声明游标。在声明游标时，需要指定游标基于的查询。

语法：

DECLARE 游标名 CURSOR FOR 查询语句;

5.2 打开游标

通过 OPEN 语句打开游标并执行查询。

语法：

OPEN 游标名;

5.3 取出数据

使用 FETCH 语句从游标中获取一行数据。

语法：

FETCH NEXT FROM 游标名 INTO 变量列表;

FETCH 会将游标当前指向的行的数据加载到指定的变量中。

5.4Handler (异常处理器)

在 MySQL 中，Handler（异常处理器） 是一种用于处理存储过程中的异常和错误的机制。Handler 可以帮助在遇到特定条件（如查询结果为空、错误发生等）时，自动执行指定的操作，从而提高程序的健壮性和灵活性。

MySQL 提供了多种 Handler 类型，主要通过 DECLARE 语句声明。在存储过程中，Handler 用于捕获特定条件（如查询没有返回结果、触发错误等），并执行相应的操作。

Handler 机制的目的是：当遇到异常或特定条件时，不需要让存储过程中断，而是可以继续执行后续的逻辑。

Handler 的基本语法:

DECLARE handler_type HANDLER FOR condition_value action;

• handler_type：指定处理器的类型，常见的类型有：

  • CONTINUE：遇到异常或条件时继续执行后续语句。

  • EXIT：遇到异常或条件时退出存储过程或循环。

• condition_value：指定条件，可以是：

  • NOT FOUND：表示没有找到数据（通常与 SELECT 查询结果为空时结合使用）02。

  • SQLWARNING：表示 SQL 警告。01

  • SQLEXCEPTION：表示 SQL 异常（如查询错误、插入失败等），除01和02

• action：在满足指定条件时要执行的操作，例如设置变量、打印错误信息、退出等。

declare exit handler for sqlstate '02000';——退出码退出
declare exit handler for not found ;————未找到退出01
declare exit handler for sqlwarning;————sql警告退出02

5.4 关闭游标

当数据处理完毕后，使用 CLOSE 语句关闭游标。

语法：

CLOSE 游标名;

5.5 游标的完整示例

完整的示例：

delimiter ##
create procedure s10(in p_sdept varchar(10))
begin
declare c_snme varchar(20);
declare c_sno varchar(5);
declare c_birthday date;
declare c_name cursor for
select sno,snme,birthday from student where p_sdept=sdept;
declare exit handler for not found close c_name;

create table if not exists r1
( id varchar(5),
name varchar(20),
birthday date
);
open c_name;
while true do
fetch c_name into c_sno,c_snme,c_birthday;
insert into r1 values( c_sno,c_snme,c_birthday);
end while;
close c_name;
end ##
delimiter ;

6. 触发器

• 定义：预先绑定在表上的一段 SQL 代码，当满足指定事件（INSERT/UPDATE/DELETE）并到达指定时机（BEFORE/AFTER）时，由 MySQL 自动执行。

• 作用：数据校验、自动补充派生字段、写审计日志、同步冗余表等。

• 执行粒度：MySQL 只支持 行级触发器（FOR EACH ROW），没有语句级触发器。对表里每一行受影响的记录都会执行一次触发器体。

6.1 创建触发器

创建触发器时，首先使用 DELIMITER 临时改变语句分隔符，以便在触发器体内使用分号。然后，通过 CREATE TRIGGER 语句创建触发器。

DELIMITER $$ — 临时换分隔符，避免碰到触发器体内的分号

CREATE TRIGGER 触发器名
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON 表名
FOR EACH ROW
BEGIN
— 触发器体：可写多条语句
— 可用 NEW.列名 访问“新值”
— 可用 OLD.列名 访问“旧值”（对于 INSERT 没旧值，DELETE 没新值）
END$$

DELIMITER ; — 恢复默认分隔符

命名约定：推荐 表名_事件_时机_trigger，如 student_ai_trigger 表示 After Insert 的触发器。

6.2. 查看、删除触发器

— 查看当前库所有触发器
SHOW TRIGGERS\\G

— 查看某触发器的创建语句
SHOW CREATE TRIGGER student_ai_log\\G

— 删除触发器
DROP TRIGGER IF EXISTS student_ai_log;

6.3. 注意事项与坑

DELIMITER ##

CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON student
FOR EACH ROW
BEGIN
INSERT INTO user_logs (operation, operate_time, operate_id, operate_params)
VALUES (
'insert',
NOW(),
NEW.sno,
CONCAT(
'插入的数据内容为：sno=', NEW.sno, ', name=', NEW.snme
)
);
END##
DELIMITER ;