MySQL进阶教程（二）—— 储存过程与触发器（下）

@

目录

• 前言
• 一、SQL编程
  • （一）条件判断
  • （二）参数
  • （三）Case
  • （四）循环
    • 1、while
    • 2、repeat
    • 3、loop
  • （五）游标
  • （六）条件处理程序
  • （七）存储函数
• 二、触发器
  • （一）触发器基本概念
  • （二）语法
  • （三）三种触发器的示例
    • 1、插入数据的触发器
    • 2、更新数据的触发器
    • 3、删除数据的触发器
• 结语

前言

在了解了存储过程的基本概念后，本期我们将要继续介绍关于MySQL储存过程中SQL编程与触发器的内容。

一、SQL编程

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，以及数据库查询和程序设计语言。其用于存取数据以及查询、更新和管理关系数据库系统。

（一）条件判断

IF 条件1 THEN
......
[ELSEIF 条件2 THEN
......
ELSE
......]
END IF;

使用案例：
根据分数的值，判定当前分数对应的等级
分数 >= 90 分等级为优秀
分数 >= 80 且分数 < 90分等级为良好
分数 >= 60 分且分数 < 80分等级为及格
分数 < 60分等级为不及格

-- 创建存储过程
delimiter //

drop procedure if exists p3;
create procedure if not exists p3()
BEGIN
  declare score int default 85;
  declare ret varchar(10);
  
  if score >= 90 THEN
    set ret := '优秀';
  elseif score >= 80 and score < 90 THEN
    set ret := '良好';
  elseif score >= 60 and score < 80 THEN
    set ret := '及格';
  else
    set ret := '不及格';
  end if;
  
  select ret;
END //

delimiter ;

call p3();

（二）参数

在MySQL中参数主要分为这三类。

类型	描述
IN	输入类型，调用存储过程时要传入的值，默认参数类型
OUT	输出参数，可以作为存储过程的返回值
INOUT	输入输出类型，即可以作为输入类型也可以作为输出类型

具体语法：

-- 创建存储过程
CREATE PROCEDURE 存储过程名 ([IN/OUT/INOUT 参数名 参数类型] [,...])
BEGIN
-- SQL 语句
END //

接着上面的案例：

delimiter // 

drop procedure if exists p4;
create procedure if not exists p4(in score int, out ret varchar(10))
BEGIN
    if score >= 90 THEN
      set ret := "优秀";
    elseif score >=80 and score < 90 THEN
      set ret := "良好";
    elseif score >= 60 and score < 80 THEN
      set ret := "及格";
    else 
      set ret := "不及格";
    end if;
END //
delimiter ;

call p4(80,@ret);

select @ret;

以及关于输入输出参数的例子：

-- 传⼊⼀个分数的值，在传⼊分数的基础上加10分，然后返回 
delimiter //
drop procedure if exists p5;
create procedure if not exists p5(inout score int)
BEGIN
  
  set score := score + 10;
END //
delimiter ;

set @score := 98;
call p5(@score);
SELECT @score;

（三）Case

-- 语法⼀
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

CASE 后的 case_value 是一个表达式，该表达式的值与每一个 WHEN 子句中的
when_value比较，当找到一个相等的 when_value 时，执行相应的 THEN 子句的statement_list 。如果没有相等的 when_value ，则执行 ELSE 子句 statement_list (如果存在ELSE)。

-- 语法⼆
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

计算每个 WHEN 子句 search_condition 表达式，直到其中一个表达式为真，此时执行相应的 THEN 子句的 statement_list 。如果 search_condition 都不相等，则执行ELSE 子句 statement_list (如果存在ELSE)。

要注意的点如下：

• 每个 statement_list 由一条或多条SQL语句组成且不允许为空。但可以使用BEGIN... END;块。
• 如果没有 when_value 或 search_condition 与测试值匹配，并且 CASE 语句不包含ELSE 子句，则会导致CASE语句错误

具体例子：
传入一个状态码，输出该状态码表示的含义。
0：成功
10001：用户名或密码错误
10002：您没有对应的权限，请联系管理员
20001：你传入的参数有误
20002：没有找到相应的结果

delimiter //
drop procedure if exists p6;
create procedure if not exists p6(in code int , out ret varchar(50))
BEGIN 
  case code
    when 0 then
      set ret := "成功";
    when 10001 then 
      set ret := "⽤⼾名或密码错误";
    when 10002 then 
      set ret := "您没有对应的权限";
    when 20001 then 
      set ret := "你传⼊的参数有误";
    when 20002 then 
      set ret := "没有找到相应的结果";
    else 
      set ret := "服务器错误没请联系管理员";
  end case;
end //
delimiter ;


call p6(10001,@ret);
select @ret;

（四）循环

1、while

WHILE search_condition DO
statement_list
END WHILE;

先判断while后面的条件是否为真来决定是否进入循环体。

示例：
计算1-n的累加总和

delimiter //
drop procedure if exists p7;
create procedure if not exists p7(in n int)
BEGIN 
  -- 定义一个变量用于表示结果
  declare total int default 0;
  
  while n > 0 DO
    set total := total + n;
    set n := n - 1;
  end while;
  
  select total;
  
end //
delimiter ;

call p7(100);

2、repeat

EPEAT
statement_list
UNTIL search_condition
END REPEAT;

先执行一遍循环体里的内容，再判断until 后面的条件是否成立，条件成立则继续循环，否则推出循环。

同上题一样的示例：

delimiter //
DROP PROCEDURE if exists p8;
CREATE PROCEDURE if not exists p8(IN n INT)
BEGIN
-- 定义⼀个变量，表⽰结果
DECLARE total int DEFAULT 0;
-- 累加
REPEAT
SET total := total + n;
SET n := n - 1;
UNTIL n <= 0 END REPEAT;
-- 查询结果
SELECT total;

END // 

delimiter ;
-- 调⽤存储过程
CALL p8(100);

3、loop

[begin_label:] LOOP
statement_list
END LOOP [end_label]

LOOP也可以实现一个简单的循环，并且当满足某个条件时终止当前循环或退出整个循环。其通常会搭配着以下两个句子使用：

• leave label : 退出整个循环
• iterate label : 终止本次循环，进入下一轮循环

此处给出传入一个数n，累加从1累加到n之间偶数的值的示例：

delimiter //
DROP PROCEDURE if exists p9;
CREATE PROCEDURE if not exists p9(IN n INT)
BEGIN
-- 定义⼀个变量，表⽰结果
DECLARE total int DEFAULT 0;

-- 累加
sum_label : loop 
  -- 判断是否退出循环
  if n <= 0 THEN
    leave sum_label;
  end if;
  
  -- 判断是否为偶数
  if n % 2 = 1 THEN
    set n := n - 1;
    -- 跳出本次循环
    iterate sum_label;
  end if;
  
  -- 进行累加操作
  set total := total + n;
  set n := n - 1;
end loop sum_label;

-- 查询结果
SELECT total;

END // 

delimiter ;
-- 调⽤存储过程
CALL p9(100);

（五）游标

MySQL中的游标是⼀种数据库对象，允许在存储过程和函数中对查询到的结果集进行逐行检索。 并且MySQL的游标是只能进行读，而不能进行其他操作的。

语法：
使用游标之前必须先声明游标，之后再用 OPEN 、 FETCH 和 CLOSE 语句来打开游标、获取游标记录和关闭游标。同时游标必须在条件处理程序之前被声明，并且变量必须在游标或条件处理程序之前被声明。

-- 声明游标
DECLARE 游标名 CURSOR FOR 查询语句;
-- 打开游标
OPEN 游标名;
-- 获取游标记录
FETCH 游标名 INTO 变量[, 变量] ...;
-- 关闭游标
CLOSE 游标名;

（六）条件处理程序

定义条件是事先定义程序执行过程中可能遇到的问题。处理程序定义了在遇到问题时应当采取的处理方式。使用条件处理程序保证存储过程或函数在遇到警告或错误时能继续执行，可以增强程序处理问题的能力，避免程序异常停止运行。

语法：

DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
CONTINUE -- 继续执⾏当前程序
| EXIT -- 终⽌执⾏当前程序
} c
ondition_value: {
mysql_error_code -- MYSQL错误码
| SQLSTATE [VALUE] sqlstate_value -- 状态码
| SQLWARNING -- 所有以01开头的SQLSTATE代码
| NOT FOUND -- 所有以02开头的SQLSTATE代码
| SQLEXCEPTION -- 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码
}

具体的错误码，请参照MySQL的官方说明：官网链接

结合游标与条件处理程序，我们就能实现对表的逐行检索了。下面给出示例:
假设我们目前存在这样一张表，要对里面某一行里某一项进行检索。

delimiter //
-- 创建存储过程
drop PROCEDURE if exists p10;
CREATE PROCEDURE if not exists p10 (IN class_id INT)
BEGIN
  -- 首先要声明变量
  -- 定义学生姓名变量
  declare student_name VARCHAR(20);
  -- 定义游标结束标志
  declare is_done bool default false;
  
  -- 定义游标
  declare s_cursor CURSOR FOR select s.`name` student_name from student s, class c where s.class_id = c.id and s.class_id = class_id;
  
  -- 定义条件处理程序
  declare continue handler for not found set is_done := true;
  
  -- 创建新表
  DROP TABLE IF EXISTS t_student_class;
  CREATE TABLE IF NOT EXISTS t_student_class (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(20)
  );
  
  -- 开启游标
  open s_cursor;
  
  -- 遍历结果集
  read_loop : loop
    -- 获取游标记录
    fetch s_cursor into student_name;
    -- 退出循环
    if is_done THEN
      leave read_loop;
    end if;
    
    -- 写入到新表
    insert into t_student_class values(null , student_name);
    SELECT student_name;
end loop read_loop;

-- 关闭游标
close s_cursor;
  

END //

delimiter ;
-- 调⽤存储过程
CALL p10 (1);

（七）存储函数

MySQL存储函数是有返回值的存储过程，参数只能是IN类型，类似于内置函数。存储函数与存储过程的主要区别在于存储函数必须有返回值，而存储过程则不⼀定。

语法：

CREATE FUNCTION 存储函数名称 ([参数列表])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END;
characteristic:
[NOT] DETERMINISTIC --表⽰相同的输⼊参数总是产⽣[不同]相同的结果
| NO SQL --不包含SQL语句
| READS SQL DATA --包含读取数据的语句，如select
| MODIFIES SQL DATA -- 包含写⼊数据的语句，如update，delete
-- 使⽤存储函数
select 存储函数名称 ([参数列表]);

在MySQL 8.0版本中，如果binlog是开启的，那么在定义存储函数时，需要指定characteristic特性，否则会报错。

仍然是传入⼀个数n，计算从1累加到n的值的示例：

delimiter //
-- 创建存储过程
drop FUNCTION if exists func1;
CREATE FUNCTION if not exists func1 (n INT)
returns int DETERMINISTIC -- 指定 characteristic
BEGIN
  -- 定义一个变量用于表示结果
  declare total int default 0;
  
  while n > 0 do
    set total := total + n;
    set n := n - 1;
  end while;
  
  return total;

END //

delimiter ;
-- 调⽤存储函数
SELECT func1 (100);

二、触发器

（一）触发器基本概念

触发器是一个与表关联的数据库对象，在对表进行insert、update、delete操作时，触发并执行定义触发器时指定的SQL语句。
触发器可以在对表操作之前或之后执行，这被称为触发时间。
触发器可以执行SQL语句或逻辑块，用于实施复杂的业务逻辑或数据验证。

MySQL支持三种类型的触发器：INSERT触发器、UPDATE触发器和DELETE触发器。使用 OLD 和 NEW 关键字来引用触发器中发生变化的记录内容。

触发器类型	OLD和NEW
INSERT触发器	NEW表示将要或已经新增的数据
UPDATE触发器	OLD表示修改之前的数据，NEW表示将要或已经修改的数据 DELETE触发器 OLD表示将要或已经删除的数据
DELETE触发器	OLD表示将要或已经删除的数据

以上是根据触发器的功能所进行的分类；根据触发方式，还能分为以下两类触发器：行级触发器 、语句级触发器。

触发器类型	描述
行级触发器	当对表中的每一行进行INSERT、UPDATE或DELETE操作时，行级触发器都会被触发。例如，如果执行⼀个UPDATE语句影响了多行数据，那么行级触发器会对每一行都触发一次。行级触发器可以访问受影响行的旧值和新值，常于实现复杂的业务逻辑时对新旧值的访问。
语句级触发器	在整个INSERT、UPDATE或DELETE语句执行时只触发一次。无论该语句影响了多少行数据，语句级触发器都只在语句开始或结束时触发一次。语句级触发器主要用于实现一些全局性的操作，比如数据同步、数据清理等。

在MySQL中只支持行级触器，不支持语句级触发器。

（二）语法

-- 创建
CREATE TRIGGER [IF NOT EXISTS] trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
BEGIN
trigger_stmt;
END;
trigger_time: { BEFORE
| AFTER }
trigger_event: { INSERT
| UPDATE | DELETE }
-- 查看
SHOW TRIGGERS;
-- 删除, 如果没有指定schema_name，默认为当前数据库
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

（三）三种触发器的示例

现在我们通过触发器记录学生表的变更日志，将变更日志写入·日志表student_log中，包含增加，修改和删除操作。

前提准备：

-- 创建学⽣⽇志表
create table student_log (
	id bigint primary key auto_increment,
	operation_type varchar(10) not null comment '操作类型: insert/update/delete',
	operation_time datetime not null comment '操作时间',
	operation_id bigint not null comment '操作的记录ID',
	operation_data varchar(500) comment '操作数据'
);

1、插入数据的触发器

-- 插入触发器
create TRIGGER trg_student_insert after insert on student for each ROW
begin
    -- 插⼊新增数据⽇志
    insert into student_log (
    operation_type,
    operation_time,
    operation_id,
    operation_data)
    values (
    'insert',
    now(),
    new.id,
    concat(new.id, ',', new.name, ',', new.sno,',', new.age, ',',
    new.gender, ',', new.enroll_date, ',', new.class_id)
    );
end;

-- 向学⽣表中插⼊记录
insert into student values (null, '天下无贼', '300001', 28, 1, '2024-09-01', 3);

-- 查看学⽣⽇志表
select id, operation_type as type, operation_time as time, operation_id as op_id, operation_data as data from student_log;

2、更新数据的触发器

-- 更新触发器
create trigger trg_student_update after update on student for each row
BEGIN
  -- 插入新增数据日志
  insert into student_log (
    operation_type,
    operation_time,
    operation_id,
    operation_data)
    values (
    'update',
    now(),
    new.id,
    concat(old.id, ',', old.name, ',', old.sno,',', old.age, ',',
    old.gender, ',', old.enroll_date, ',', old.class_id,
    '|', new.id, ',', new.name, ',', new.sno,',', new.age, ',',
    new.gender, ',', new.enroll_date, ',', new.class_id)
    );
end;

-- 更新学⽣表中记录
update student set age = 20, class_id = 2 where name = '天下无贼';

-- 查看学⽣⽇志表
select * from student_log;

-- 更新多条记录
update student set class_id = 3 where id >= 7;

3、删除数据的触发器

create trigger trg_student_delete
after delete on student for each row
begin
-- 插⼊新增数据⽇志
insert into student_log (
operation_type,
operation_time,
operation_id,
operation_data)
values (
'delete',
now(),
old.id,
concat(old.id, ',', old.name, ',', old.sno,',', old.age, ',',
old.gender, ',', old.enroll_date, ',', old.class_id)
);
end;
-- 删除学⽣表中记录
delete from student where name = '曹操';
-- 查看学⽣⽇志表
select * from student_log;

结语

以上便是本期关于MySQL储存过程与触发器章节的全部内容了。如果喜欢我的内容，请点赞、收藏加关注，多多支持我，以及欢迎各位在评论区讨论交流，并指出我的不足，谢谢大家。