硅基计划5.0 MySQL 柒存储过程&触发器

最新推荐文章于 2025-12-03 14:39:35 发布

原创最新推荐文章于 2025-12-03 14:39:35 发布 · 1k 阅读

24 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #存储过程 #触发器

硅基计划5.0 MySQL 柒存储过程&触发器

1740030373041

一、存储过程

什么是存储过程，说白了就是为了完成特定功能的SWL语句集合，在编译后用户通过存储过程的名称和给予对应参数获取结果，就类似于Java中的方法
我们的存储过程其实就是在数据库层面对操作进行了封装，以前我们是每一个应用程序直接操作多张表，现在是每一个应用程序操作都给到存储过程，再由存储过程对数据库中的表进行操作

1. 优缺点

优点
1. 编译之后存储在数据库中，比单纯执行单个语句快速
2. 类似于方法，它可以被重用
3. 通过存储过程间接访问数据库，可以避免一些误操作，提高安全性
4. 支持事务管理
5. 当表结构变化时，仅需变更存储过程语句，无需修改应用程序语句
缺点
1. 因为存储过程有额外开销，因此不适合高并发场景
2. 针对不同的数据库种类有不同的存储过程，可移植性差
3. 大部分数据库不支持存储过程调试，因此出了问题难以debug

2. 存储过程的语法

create procedure 名称(参数列表)
begin
......
end;

call 名称(参数列表) # 调用存储过程

我们给出一张示例表并插入以下几条数据

# 创建示例表
-- 创建学生成绩表
CREATE TABLE student_scores (
    student_id INT PRIMARY KEY,      -- 学号（主键）
    student_name VARCHAR(50),        -- 学生姓名
    chinese_score DECIMAL(5,2),      -- 语文成绩
    math_score DECIMAL(5,2),         -- 数学成绩
    english_score DECIMAL(5,2),      -- 英语成绩
    physics_score DECIMAL(5,2),       -- 物理成绩
    chemistry_score DECIMAL(5,2)     -- 化学成绩
);

-- 插入示例数据
INSERT INTO student_scores VALUES
(2021001, '张三', 85.5, 92.0, 78.5, 88.0, 76.5),
(2021002, '李四', 92.0, 88.5, 85.0, 90.5, 82.0),
(2021003, '王五', 78.0, 95.5, 92.0, 85.5, 79.0),
(2021004, '赵六', 88.5, 76.0, 80.5, 79.0, 85.5),
(2021005, '钱七', 90.0, 89.5, 87.0, 92.5, 88.0),
(2021006, '孙八', 82.5, 84.0, 91.5, 86.0, 80.5),
(2021007, '周九', 79.0, 91.0, 83.5, 87.5, 84.0),
(2021008, '吴十', 86.5, 79.0, 89.0, 81.5, 77.0);

好，我们现在先进行查询每名学生总成绩的存储过程

# 我们创建一个计算成绩的存储过程
create procedure p_calAvg()
begin
  select student_id,student_name,chinese_score+math_score+english_score as '总成绩'
  from student_scores;
end;

call p_calAvg();

同时，我们还可以查看我们创建了哪些存储过程

# 查看我们创建过的存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = '数据库名';

并且，我们还可以查看我们创建存储过程的SQL语句

show create procedure 名称;

删除存储过程

drop procedure if exists 名称;

但是我们在命令行执行的时候，会默认把;作为SQL语句的结束符，这样就会导致在存储过程内的语句就不能带有;，但是这也不现实
因此我们可以暂时性的把语句结束符改成其他的符号，使用delimiter

delimiter //
create procedure p_calAvg()
begin
  select student_id,student_name,chinese_score+math_score+english_score as '总成绩'
  from student_scores;
end //
delimiter ;

3. 变量

1. 系统变量

它是SQL服务器的配置变量，控制着整个服务器的行为以及性能
分为全局变量（启动时读取的值）和会话变量（每一个连接的临时值，依赖于全局变量）
每次连接重启时由于都会从全局变量读取，因此此前修改的会话变量就会失效
每次SQL服务器重启时由于都要从配置文件中读取，因此此前修改的全局变量也会失效

查看

show (global/session) variables;

指定查看

show session variables like 'xx%'/'xxx';

精确到具体变量名的查看

select @@global/session.具体的变量名称;

设置系统变量

set global/session 变量名称 = 值;
# 或者是
set @@global/session.具体变量名称 = 值;

2. 用户自定义变量

用户自定义变量它的作用域在当前的会话内，便于后续的结果查询

有三种赋值操作

set @example = aaa; # 声明+赋值
set @example := aaa; # 推荐
select @example := aaa;# 赋值+查询
select 列名 into @example from 表名 where ...;# 查询结果赋值给自定义的变量

为什么推荐第二种赋值呢，因为如果是这这种情况

select @example = aaa;

这总就不会进行赋值而是进行判断，返回0或1的判断结果

使用/修改变量

select @example;# 使用
set @example := xxx;# 修改
select count(*) into @example from student_scores;# 查询结果赋值给变量
select * from student where name = @example;# 查询条件判断

3. 局部变量

这种变量仅在存储过程中有效，需要使用declare进行生声明

声明

declare 变量名 变量类型 default 默认值;

赋值和用户自定义变量一样，这里就不过多赘述

我们就拿我们之前创建的学生表举例，我们可以去统计有多少名学生

delimiter //

create procedure p2()
begin 
  declare tmp int default 0;
  
  select count(*) into tmp from student_scores;
  
  select tmp;
end//

delimiter ;

call p2();

4. SQL编程

1. 条件判断

语法

if 条件1 then
......
elseif 条件2 then 
......
else
......
end if;

比如我们举一个根据分数判断其分数所在水平的例子

delimiter //
create procedure p3()
BEGIN
  declare score int default 60;
  declare result varchar(10);
  if score >= 90 then
    set result := '优秀';
  elseif score >= 60 and score < 90 then 
    set result := '一般';
  else 
   set result := '不及格';
 end if ;
 select result;
end //
delimiter ;

call p3();

2. 参数

分为三种

in：输入型参数，即传入的值
out：输出型参数，即要返回的值
inout：输入输出型参数，可以想象成它是一种引用类型

语法

create procedure example(in/out/inout 参数名 类型 ...)
begin
......
end

我们举个例子，我们传入一个值，要求其加上10后返回

delimiter //
create procedure p4(inout num int)
begin 
  set num := num + 10;
end //
delimiter ;

set @num = 80;

call p4(@num);

select @num;

3. case分支语句

语法

case 初始值/不写内容
  when 比较值/条件判断 then
    ......
  when 比较值/条件判断 then
    ......
  else <--可不写
    ......
  end case;

我们举个例子，联系之前学的多线程，我们传入一个状态码，判断我们客户端访问时成功还是失败

delimiter //
create procedure p5(in num int)
begin 
  declare result varchar(10) default '未知';
  case num
  when 0 then
    set result := '成功';
  when 100 then 
    set result := '失败';
  else 
    set result := '非法访问';
  end case;
   
  select result; 
end //
delimiter ;

call p5(100);

4. 循环

分为三种循环

1. 普通while循环

语法

while 条件表达式 do
  ......
end while;

比如我们累加一个数

delimiter //
create procedure p6(in num int,out result int)
begin 
  declare sum int default 0;
  while num > 0 do 
    set sum := sum + num;
    set num := num - 1;
  end while;
  
  set result := sum;
  
end //
delimiter ;

set @result = 0;
call p6(10,@result);
select @result;

2. do-while循环

语法

repeat
  ......
until 条件判断
end repeat

还是那个求累加的数的例子

# 循环判断do-while
delimiter //
create procedure p7(in num int)
begin 
  declare result int default 0;
  repeat 
    set result := result + num;
    set num := num - 1;
  until num <= 0
  end repeat;
  
  select result;
  
end //
delimiter ;

drop procedure p7;

call p7(10);

3. while(true)循环

语法

循环名称:loop
  ......
end loop 循环名称

在这循环中，我们可以使用leave 循环名称退出整个循环，相当于break
我们还可以使用interate 循环名称跳过当前一轮的循环，相当于continue

我们写一个当num值小于5的时候自动退出，并且只累加偶数的例子

delimiter //
create procedure p8(in num int)
begin 
  declare result int default 0;
  declare tmp int default 0;
  sum_label:loop 
    if num = 5 then 
      leave sum_label;
    end if;
    
    set tmp := num;
    
    if num % 2 = 0 then
      set result := result + num;
    end if;
    
    set num := tmp - 1;
    
  end loop sum_label;
  
  select result;
end //
delimiter ;

drop procedure p8;

call p8(10);

5. 游标

它是一种数据库的对象，可以对查询到的结果集进行检索
游标它必须在条件处理程序之前就进行声明，变量则可以在游标或者条件处理程序之后声明

语法

declare 游标名 cursor for 查询语句; # 游标的声明，访问结果集但未执行游标
open 游标名; # 真正执行游标，将查询的结果即关联到游标
fetch 游标名 into 提前定义好的变量;
close 游标名;

我们举个例子，传入一个班级编号，将属于该班级的学生信息写入另一个表中，我们先给出示例数据

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,  -- 学生ID
    student_name VARCHAR(50) NOT NULL,          -- 学生姓名
    class_id INT NOT NULL,                      -- 班级编号
    age INT,                                    -- 年龄
    gender ENUM('男', '女')                     -- 性别
);

CREATE TABLE class_students_backup (
    backup_id INT PRIMARY KEY AUTO_INCREMENT,   -- 备份ID
    student_id INT,                             -- 学生ID
    student_name VARCHAR(50),                   -- 学生姓名
    class_id INT,                               -- 班级编号
    age INT,                                    -- 年龄
    gender varchar(2),                          -- 性别
    backup_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 备份时间
);

-- 插入学生数据
INSERT INTO students (student_name, class_id, age, gender) VALUES
('张三', 1, 18, '男'),
('李四', 1, 17, '男'),
('王五', 1, 19, '女'),
('赵六', 2, 18, '男'),
('钱七', 2, 17, '女'),
('孙八', 2, 16, '女'),
('周九', 3, 18, '男'),
('吴十', 3, 19, '女'),
('郑十一', 3, 17, '男'),
('王十二', 3, 16, '女');

好，假设我现在需要给三班的学生进行数据备份到另一张表中

delimiter //
create procedure p9(in input_class_id int)
begin 
  # 创建变量接收游标信息
  declare s_id int;
  declare s_name varchar(50);
  declare s_class_id int;
  declare s_age int;
  
  # 创建游标
  declare s_cursor cursor for
  select student_id,student_name,class_id,age from students where class_id = input_class_id;
  
  open s_cursor;
  
  # 遍历游标结果集
  while true do 
    fetch s_cursor into s_id,s_name,s_class_id,s_age; # 将游标结果集逐行插入变量中
    insert into class_students_backup(student_id,student_name,class_id,age)
    values(s_id,s_name,s_clss_id,s_age);# 将变量中的值复制到新的表
  end while;
  
  close s_cursor;
end //
delimiter ;

call p9(3);

但是目前存在一个问题，当我们执行上述代码后，会出现一个越界问题
因为我们的循环条件是true，游标并不知道自己遍历到了最后一行，因此我们接下来就要讲条件处理程序来应对这个问题

6. 条件处理程序

我们首先要定义条件，即在程序执行过程中的可预见性问题
再定义对应的处理程序，即遇到问题的处理
这套流程类似于Java中的异常与处理

语法

declare 行为 handler for 为哪种错误 ... 处理语句 ...

其中，行为有continue 继续执行当前程序和exit 终止执行当前程序并退出存储过程
其中，错误有

mysql_error_code：数据库级别的错误码
SQLSTATE [VALUE] sqlstate_value：语句级别的状态码
sqlwaring：所有以01开头的状态码
notfound：所有以02开头的状态码
sqlexception：剩下的情况

错误码官方网站

因此，我们对于上述的存储过程进行重写

delimiter //
create procedure p9(in input_class_id int)
begin 
  # 创建变量接收游标信息
  declare s_id int;
  declare s_name varchar(50);
  declare s_class_id int;
  declare s_age int;
  
  # !!!!加入循环结束标识符
  declare is_end bool default false;
  
  # 创建游标
  declare s_cursor cursor for
  select student_id,student_name,class_id,age from students where class_id = input_class_id;
  
  # !!!!定义条件处理程序
  declare continue handler for not found set is_end := true;
  
  open s_cursor;
  
  # 遍历游标结果集，改变循环类型和条件
  read_loop:loop
    fetch s_cursor into s_id,s_name,s_class_id,s_age; # 将游标结果集逐行插入变量中
    
    # 提前终止循环，避免插入无效数据
    if is_end then 
      leave read_loop;
    end if;
    
    # 如果判断不通过，正常执行插入语句
    insert into class_students_backup(student_id,student_name,class_id,age)
    values(s_id,s_name,s_class_id,s_age);# 将变量中的值复制到新的表
  end loop read_loop;
  
  close s_cursor;
end //
delimiter ;

call p9(3);

select * from class_students_backup;

7. 存储函数

它是具有返回值的存储过程，并且参数只能是in类型，且参数中不能写in关键字
即便如此，我们还是推荐使用存储过程，毕竟它可以灵活调整

语法

create function 名称(参数)
returns 返回类型 特征指定
begin
  ......
  return ......
end;
select 存储函数名(参数);

对于特征指定，有以下几种
(not) deterministic表示对于同一个输入是否会产生（不同）相同结果
no sql表示不包含SQL语句
reads sql data表示包含读取数据的语句，比如select
modifies sql data 表示包含写入数据的语句，比如update,delete

比如我们继续举累加例子，并返回结果

delimiter //
create function fun1(num int)
returns int DETERMINISTIC
begin 
 declare sum int default 0;
 while num > 0 do 
   set sum := sum + num;
   set num := num - 1;
 end while;
 return sum;
end //
delimiter ;

select fun1(10);

二、触发器

它是独立于我们主流程之外的，也就是说它不会去影响主流程的执行
当我们对表进行insert,delete,update操作时候，就可以通过触发对应SQL帮助我们完成一些辅助性的操作
它可以在表操作之前或者是之后执行，这就是它的触发时间

我们MySQL中支持insert,delete,update三种类型触发器，并且使用OLD和NEW关键字获取值，下面是一张说明表

类型	OLD&NEW操作
insert	只能使用NEW获取已经新增的数据
delete	只能使用OLD获取删除前的这条数据
update	可以使用NEW和OLD获取修改前后的数据

我们MySQL仅仅支持行级触发器，即修改了多少条数据就触发多少次
并不支持语句级触发器，即修改多条数据只触发一次

语法

create trigger (if not exists) 名称
trigger_time trigger_event
on 表明 for each row
begin
  ......
end;
show triggers; # 查看触发器
drop trigger if exists (数据库名称).触发器名字;# 删除，不写数据库名默认当前数据库

其中trigger_time有before和after
其中trigger_event有insert,update,delete三种类型

下面我们还是针对之前游标中学生的那张表，我们进行一些操作，将记录信息保存在另一张表中，叫学生日志表

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. 插入

delimiter //
create trigger if not exists trg_student_insert
after insert 
on students for each row 
begin 
  insert into student_log (operation_type,operation_time,operation_id,operation_data) values
  ('insert',now(),new.student_id,concat(new.student_name,',',new.class_id,',',new.age,',',new.gender));
end //
delimiter ;

# 插入数据
insert into students(student_name,class_id,age,gender) values('大顺',3,20,'男');

# 查询结果
select * from student_log;

2. 更新

# 更新操作
delimiter //
create trigger if not exists trg_student_update
after update 
on students for each row 
begin 
  insert into student_log (operation_type,operation_time,operation_id,operation_data) values
  ('update',now(),new.student_id,concat('new',new.student_name,',',new.class_id,',',new.age,',',new.gender,'||','old',old.student_name,',',old.class_id,',',old.age,',',old.gender));
end //
delimiter ;

# 更新数据
update students set age = 20,class_id = 1 where student_name = '张三';

# 查询结果
select * from student_log;

3. 删除

delimiter //
create trigger if not exists trg_student_delete
after delete 
on students for each row 
begin 
  insert into student_log (operation_type,operation_time,operation_id,operation_data) values
  ('delete',now(),old.student_id,concat('old',old.student_name,',',old.class_id,',',old.age,',',old.gender));
end //
delimiter ;

# 更新数据
delete from students where student_name = '大顺';

# 查询结果
select * from student_log;