Skip to main content

存储过程

存储过程​

存储过程是将 SQL 语句进行程序化封装，存储在数据库服务上，客户端可以直接调用该封装，获得查询结果。

可以理解为：一组预编译好的 SQL 语句，以一个名字保存在数据库中，可以像函数一样反复调用，还可以传入参数、包含流程控制(如条件、循环)，用于完成复杂的数据库操作和业务逻辑。

普通SQL​

客户端

解析 SQL 语句

语法分析与优化

执行 SQL

读写文件存储的数据

返回执行结果

存储流程​

客户端

查找存储过程

执行存储过程1 SQL 指令

执行存储过程2 SQL 指令

返回所有执行结果

查询​

可通过以下命令查询数据库中的存储过程：

-- 查询所有
show procedure status where db = <db_name>;

-- 查询某一个存储过程
show create procedure <db_name>.<procedure_name>;

删除​

可通过以下命令删除数据库中的存储过程：

drop procedure <procedure_name>;

创建​

如下示例：

create procedure plus(in x int, in y int, out z int)
begin
  set z = x + y
end;
  • create procedure 创建存储过程。
  • in 输入。
  • out 输出。
  • begin 开始处理。
  • set 设置。
  • end 结束处理。

可以看到，根很多编程语言的函数很像：

func plus(x int, y int) int z {
  z = x + y
  return z
}

调用​

set @z = 0;
call plus(1, 2, @z);

调用之后，可通过 select 获取结果：

select @z from dual;

优点​

  • 客户端不再需要编写 SQL 指令，节省了网络开销，保证 SQL 操作的网络安全问题。
  • 执行 SQL 指令不需要重复编译，节省了 SQL 服务的性能开销。
  • 存储过程的封装支持逻辑性、流程控制等编程特性，易于处理多个 SQL 指令间的复杂逻辑。

缺点​

  • 不同数据库程序之间的迁移问题较多。
  • 存储过程在高性能运算上在不同数据库程序中有不同的表现。
  • 由于数据库业务逻辑存储在数据库服务上，所以数据库服务在高并发时，影响数据库连接响应时间。

变量​

  • 局部变量：定义在存储过程中且只能在存储过程中使用的变量，调用存储过程时创建，过程结束即释放，使用 declare 声明。
  • 用户变量：以 @ 开头，不需要专门声明，直接赋值即生效，当前数据库连接内全局可见，可以在多条 SQL 之间共享。
  • 用户变量可通过 select @variable_name from dual 查询，dual 表是存储变量的表。
create procedure test(in x int, in y int, out z int)
begin
  -- 局部变量 a 和 b 
  declare a int default 0;
  declare b int default 0;
  set a = x / 2;
  set b = y / 2;
  set z = a + b;
end;

-- 用户变量 z
set @z = 0;
call test(2,4,@z);
select @z from dual;

into 用于把查询结果赋值到变量或参数，用于后续计算或输出，可认为是一种中转。

create procedure get_employees_count(out c int)
begin
	select count(emp_id) into c from employees;
end;

参数​

  • in 输入参数。
  • out 输出参数。
  • inout 输入输出参数，一般不推荐使用。

在输入输出过程中，如果涉及到表字段，类型尽可能保持一致。

create procedure get_employee (
  in id char(8),
  out ename varchar(20)
)
begin
  select emp_name into ename from employees where emp_id = id;
end;

分支​

分支包括 if else 以及 case：

-- if
create procedure callname(in type int, in id char(8), out targetName varchar(20))
begin
  if type = 1 then
    -- ...
  else if type 2 then
    -- ...
  else 
    -- ...
  end if;
end;

-- case
create procedure callname(in type int, in id char(8), out targetName varchar(20))
begin
  case type
  when 1 then
    -- ...
  when 2 then
    -- ...
  else 
    -- ...
  end case;
end;

循环​

while​

while 与编程语言类似：

create procedure callname(in num int)
begin
  declare i int default 0;
  while i < num do
    -- ...
    set i = i+ 1;
  end while
end;
repeat​

repeat 和编程语言的 do while 很像，使用 until 关键字结束循环：

create procedure insertdatarepeat(in num int)
begin
  declare i int default 0;
  repeat
    -- ...
    set i = i+ 1;
  until i >= num end repeat;
end;
loop​

loop 使用 leave 关键字结束循环：

create procedure insertdataloop(in num int)
begin
  declare i int default 0;
  l:loop
    -- ...
    set i = i+ 1;
    if i >= num then
      leave l;
    end if;
  end loop;
end;

游标​

游标是 MySQL 存储过程中用来逐行遍历查询结果集的一种机制，其作用有点像指针或迭代器，可以在存储过程中一行行地处理查询结果，而不是一次性全部处理，适合适合需要循环每一行而不是一次性批量操作的场景。

create procedure demo_cursor()
begin
  declare done int default 0;
  declare uid int;
  declare uname varchar(50);

  declare user_cursor cursor for select id, name from users;

  -- 声明循环结束标志
  -- 当 fetch 没有再返回任何行时, MySQL 会自动执行 set done = 1
  declare continue handler for not found set done = 1;

  -- 打开游标
  open user_cursor;

  -- 循环取值
  read_loop: loop
    fetch user_cursor into uid, uname;
    if done then
      leave read_loop;
    end if;
    select concat('用户ID:', uid, ', 姓名:', uname);
  end loop;

  close user_cursor;
end;

触发器​

触发器(Trigger)是一种特殊的存储过程，可以在对表执行特定操作时(如插入、更新、删除)，自动、被动地触发执行自定义的 SQL 逻辑，适合做数据一致性、自动审计、日志记录等场景。

基本语法​
create trigger <trigger_name>
{before | after} {insert | update | delete}
on <table_name>
for each row
-- sql 语句

示例：

create trigger after_insert_employee
after insert on employees
for each row
update departments set dpt_number = dpt_number + 1 where dpt_id = new.id
  • new 和 old 是新插入行或者删除行的主键。
    • insert 事件用 new 访问。
    • delete 事件用 old 访问被。
    • update 事件可以用 new 或 old 访问。
查看​
show triggers;
删除​
drop trigger <trigger_name>;
优点​
  • 自动调用。
  • 关联操作。
缺点​
  • 可能存在执行效率问题。
  • SQL 程序复杂。
  • 出现问题难以定位。
  • 数据库迁移困难。
  • 数据库职责复杂。

一般情况下，不建议使用触发器。

Last updated on