栈记
MySQL

存储过程

存储过程、变量、流程控制与游标

存储过程

信息

经过编译,并存储在数据库中的一段 SQL 集合;

就是 SQL 的封装和重用,调用存储过程可以简化业务端的工作,减少数据在客户端和服务端的传输从而提高效率。

-- 创建存储过程
create procedure 存储过程名称([参数...])
begin
    sql 语句
end;

-- 调用存储过程
call 存储过程名称()

-- 查看存储所有过程 infomation_schema.ROUTEINGS 查询可以带任何查询条件
select * from infomation_schema.ROUTEINES;

-- 查看存储过程
show create procedure 存储过程名称;

-- 删除存储过程
drop procedure if exists 存储过程名称;

命令行创建存储过程

由于存储过程中也包含一些 sql 语句导致直接出现多个分号会创建失败;

-- 使用 delimiter 修改结束符
delimiter $$
create procedure p1()
begin
    select * from emp;
end;
delimiter $$

变量

  • 系统变量 global | session
-- 查看系统变量
select @@[global | session] 系统变量名;

show [global | session] variables;
show [global | session] variables like '';

-- 设置系统变量
set @@[global. | session.]系统变量名 = 值;
set [global. | session.]系统变量名 = 值;
  • 用户自定义变量
-- 定义变量
set @变量名 = 值[,...];
set @变量名 := 值[,...];
-- select 只能使用 :=
select @变量 := 值[,...];
-- info 则是将查询的数据赋值给这个变量
select 字段名 into @变量名 from 表名 where 条件...;

-- 查询变量
select @变量名;
  • 局部变量

在存储过程中定义的变量,必须在 begin 后面 sql 语句前面。存储过程中的变量不需要增加 @

delcare 变量名 变量类型 [default 默认值];

create procedure p1()
begin
  declare num int default 0;
  select count(*) into num from emp;
  select num;
end;

call p1();

条件判断

单分支 if 判断

create procedure p1()
begin
  declare gender varchar(16) default 'male';
  set gender = if (gender = 'male', '男', '女');
  select gender;
end;
call p1();

多分支 if 判断

delimiter $$
create procedure p1()
begin 
  declare score int default 80;
  declare notice varchar(10) default '';
  if (score = 100) then
    set notice = '优秀';
  elseif (score = 80) then
    set notice = '不错';
  else
    set notice = '垃圾';
  end if;
  select notice;
end $$
delimiter;
call p1();

多分支 case 表达式

delimiter $$
create procedure p1()
begin
  declare score int default 80;
  declare notice varchar(10) default '';
  set notice = case score
    when 100 then '优秀'
    when 80 then '不错'
    else '垃圾'
  end;
  select notice;
end $$
delimiter;
call p1();

多分支 case 语句

delimiter $$
create procedure p1()
begin
  declare score int default 80;
  declare notice varchar(10) default '';
  set notice = case
    when score >= 90 then '牛逼'
    when score >= 80 then '还行'
    else '垃圾'
  end;
  select notice;
end $$
delimiter;
call p1();

参数

-- in 输入参数, 默认
-- out  输出参数
-- inout 输入和输出参数
create procedure 存储过程名称([in/out/inout 参数名 参数类型, ....])
begin
  sql 语句
end;

比如将上面的存储过程改为参数传递

create procedure p1(in score int, out res varchar(16))
begin
  set res = case
    when score >= 90 then '优秀'
    when score >= 70 then '一般'
    else '垃圾'
  end;
end;

-- @res 不赋值的变量就是 null
call p1(90, @res);
-- 打印变量
select @res;

如果这个变量期望在外面也生效,就需要使用 inout

-- 如果使用 in 外部的 @score 则不会改变
create procedure p1(inout score double)
begin
  set score = score / 10;
end;

set @score = 88;
call p1(@score);
select @score;

循环语句

while 循环,满足条件退出循环

create procedure p1(in num int)
begin
  declare res int default 0;
  while num > 0 do
    set res = num + res;
    set num = num - 1; -- 终止循环条件
  end while;
  select res;
end;

repeat 循环,满足条件退出循环,至少执行一遍逻辑

create procedure p1(in num int)
begin
  declare res int default 0;
  repeat
    set res = res + num;
    set num = num - 1;
    until num <= 0; -- 满足条件终止循环
  end repeat;
  select res;
end;

loop 循环,可以通过 leave 跳出循环 iterate 进入下一次循环

create procedure p1(in num int)
begin
    declare res int default 0;
    -- sum: 是标签,用来终止或者跳出循环时使用
    sum:loop
        if num = 5 then
            set num = num - 1;
            iterate sum; -- 等于 5 的时候跳出本次循环进入下一次
        end if;

        set res = res + num;
        set num = num - 1;

        if num <= 0 then
            leave sum; -- 跳出循环
        end if;
    end loop sum; -- 尾部的标签可写可不写

    select res;
end;

游标

用来存储查询结果集的数据类型

-- 声明游标, select语句的结果会赋值给这个游标
declare 游标名称 cursor for select语句;
-- 打开游标
open 游标名称;
-- 读取游标记录, 到对应的变量名
fetch 游标名称 into 变量名,...;
-- 关闭游标
close 游标名称

示例:接收 u_salary 参数,查询 emp 中所有薪资小于该参数的员工姓名、年龄、薪资以及岗位,存储到一个新的表中

create procedure p1(in u_salary int)
begin
    declare my_name varchar(16);
    declare my_age int;
    declare my_salary float(10, 2);
    declare my_post varchar(16);
    -- 定义游标内容
    declare my_cursor cursor for select name, age, salary, post from emp where salary < u_salary;
    -- 使用 handler 捕获异常关闭游标
    declare exit handler for SQLSTATE '02000' close my_cursor;

    drop table if exists tb_cursor;
    create table tb_cursor(
        id int primary key auto_increment,
        name varchar(16),
        age int,
        salary float(10, 2),
        post varchar(16)
    );

    open my_cursor;
    -- 死循环会导致出现异常
    while true do
        -- 将游标中的每行数据放在对应的变量中
        fetch my_cursor into my_name, my_age, my_salary, my_post;
        insert into tb_cursor(name, age, salary, post) values (my_name, my_age, my_salary, my_post);
    end while;
    close my_cursor;
end;

条件处理程序 handler

-- continue 跳过异常代码继续执行
-- exit 退出整个存储过程
-- 条件,包含以下四种
-- 满足条件后执行 continue/exit 动作,然后执行 SQL逻辑
declare continue/exit handler for 条件 SQL逻辑;
  • SQLSTATE 状态码 捕获某一个状态码
  • SQLWARNING 警告信息,所有 01 开头的状态码
  • NOT FOUND 没有找到数据,所有 02 开头的状态码
  • SQLEXCEPTION 上面错误数据的兜底

存储函数

有返回值的存储过程,存储函数的参数只能是 in 类型;

准确来说前面的存储过程都是传入参数,然后修改参数的值,而不是真正的返回值。

create function 存储函数名([参数,...])
returns 返回值类型 [特性]
begin
  sql 语句
end;

特性 8.0 之后必须

  • DETERMINTSTIC: 确定性,输入和输出总是相同的,这个参数可以帮助优化器进行性能优化,允许函数缓存结果
  • NOT DETERMINTSTIC:非确定性,输入和返回值可能会发生变化,适用于可能会因为外部因素变化的函数比如时间
  • READS SQL DATA:指定函数会读取数据,包括表,视图等,就是说函数在执行的过程中会对数据库进行读取操作
  • MODIFIES SQL DATA:指定函数会修改数据,比如插入,更新删除表数据,函数在执行过程中会对数据库进行写入操作
  • NO SQL:不会对数据进行读写操作
create function f1(num int)
returns int READS SQL DATA
begin
    declare res int;
    select count(*) into res from emp where salary < num;
    return res;
end;

set @num = f1(5000);
select @num;

触发器

是一个和表有关的数据库对象,在插入、更新、删除数据之前或者之后触发,触发器被触发后会执行内部的 sql 逻辑

create trigger 触发器名字
-- before/after 之前或者之后
-- insert/update/delete 指定语句触发
-- for each row 行触发器,涉及多少行就会触发多少次
before/after insert/update/delete on 表名 for each row
begin
  sql;
end;

show triggers; -- 查看触发器
drop trigger 触发器名字; -- 删除触发器

比如记录 emp 表的数据变更日志,并把变更日志插入到日志表 emp_logs 中。

-- 创建表
create table emp_logs(
    id int primary key auto_increment,
    event_type varchar(16) not null,
    event_time datetime not null ,
    event_id int not null ,
    event_content varchar(800)
);

-- 在触发器的内部,提供了 old 和 new 可以访问操作前和操作的数据
create trigger emp_insert_trigger
    before insert on emp for each row
    begin
        insert into emp_logs values (null, 'insert', now(), new.id, concat(
                '插入的数据:', 'id=', new.id, 'name=',new.name, 'salary=',new.salary)
        );
    end;

create trigger emp_update_trigger
    before update on emp for each row
    begin
        insert into emp_logs values(null, 'update', now(), old.id, concat(
                '更新后的数据:', 'id=', new.id, 'name=',new.name, 'salary=',new.salary,
                '更新前的数据:', 'id=', old.id, 'name=',old.name, 'salary=',old.salary)
        );
    end;

create trigger emp_delete_trigger
    before delete on emp for each row
    begin
        insert into emp_logs values(null, 'update', now(), old.id, concat(
                '删除的数据:', 'id=', old.id, 'name=',old.name, 'salary=',old.salary)
        );
    end;

On this page