管理存储过程

摘要：本文学习了如何使用DDL语句管理存储过程。

环境

Windows 10 企业版 LTSC 21H2
MySQL 5.7.40

1 简介

1.1 定义

存储过程是一组为了完成特定功能的SQL语句集合。

将常用或复杂的工作预先用SQL语句写好，将这些SQL存储起来，这个过程经过编译和优化后存储在数据库服务器中，因此称为存储过程。

1.2 优点

性能方面：

减少网络通信：把多条SQL语句封装成存储过程，客户端调用时只需发送一个调用命令，避免逐条发送SQL语句产生的大量网络通信，减少网络延迟和带宽占用。
预编译和缓存：存储过程首次调用时被预编译并缓存，后续调用直接使用缓存的编译结果，无需重复编译，提高执行效率。

安全方面：

权限控制：可对存储过程设置权限，用户获得执行特定存储过程的权限后，无需拥有对底层表的直接访问权限，限制用户对数据库的访问范围，降低数据被错误修改或泄露风险。
封装敏感操作：将复杂、敏感的数据操作逻辑（如涉及多个表更新的事务、核心商业机密计算等）封装在存储过程内部，其代码在服务器端执行，不易被篡改或被恶意用户获取敏感信息。

维护方面：

代码重用：存储过程可被多个应用程序或用户调用，避免在每个应用程序中重复编写相同的SQL代码，提高代码可维护性。
集中管理：数据库逻辑集中在服务器端，修改业务逻辑时，只需在服务器端修改存储过程，所有依赖该存储过程的应用程序都能自动使用新的逻辑，减少维护成本。

2 操作

2.1 创建

2.1.1 基础

语法：

sql

1 2	create procedure 存储过程名称([参数]) 存储过程主体

如果开启了二进制日志会导致执行失败，有两种解决办法：

使用命令：

sql

1	set global log_bin_trust_function_creators = 1;

修改配置文件：
my.ini
1
log_bin_trust_function_creators = 1

说明：

存储过程名称：存储过程的名称，默认在当前数据库中创建，可以通过数据库名.存储过程名指定数据库创建存储过程。
参数：存储过程的参数列表。
存储过程主体：存储过程的主体部分，包含在过程调用的时候必须执行的SQL语句。可以是单条SQL语句，也可以是被begin和end包裹的复合语句块。

示例：

sql

delimiter $$
create procedure showScore()
begin
  select * from score;
end $$
delimiter ;

2.1.2 参数

参数的格式如下：

sql

1	[in\|out\|inout] 参数名参数类型

支持三种类型的参数：

输入参数：用in关键字标识，可以传递存储过程。
输出参数：用out关键字标识，用于存储过程需要返回操作结果的情形。
输入/输出参数：用inout关键字标识，既可以充当输入参数也可以充当输出参数。

需要注意的是，参数的取名不要与数据表的列名相同，多个参数时需要用逗号分隔，没有参数时需要保留括号。

2.1.3 关键字

常用关键字：

delimiter：修改语句结束字符。不建议使用;符号，因为这是默认结束字符，该符号后面的存储过程不会执行。也不建议使用\符号，该符号是转义字符。
begin：存储过程开始符号。
end：存储过程结束符号。
set：变量赋值。
declare：变量定义。

2.1.4 流程控制

2.1.4.1 判断语句

if语句：

sql

if 条件 then 命令
[elseif 条件 then 命令]
[else 命令]
end if;

示例：

sql

delimiter $$
create procedure fixSex(in id int, in sex int)
begin
  if sex = 0 then
    update student s set s.sex = '女' where s.id = id;
  elseif sex = 1 then
    update student s set s.sex = '男' where s.id = id;
  else
    update student s set s.sex = '未知' where s.id = id;
  end if;
end $$
delimiter ;

case语句：

sql

case 变量
when 条件 then 命令
[...]
else 命令
end case;

示例：

sql

delimiter $$
create procedure fixSex(in id int, in sex int)
begin
  case sex
  when 0 then
    update student s set s.sex = '女' where s.id = id;
  when 1 then
    update student s set s.sex = '男' where s.id = id;
  else
    update student s set s.sex = '未知' where s.id = id;
  end case;
end $$
delimiter ;

2.1.4.2 循环语句

while语句，当条件成立时执行循环：

sql

1
2
3

while 条件 do
命令
end while;

示例：

sql

delimiter $$
create procedure addStudent(in start int, in num int)
begin
  declare i int default 0;
  while i < num do
    insert into student(id) values (start + i);
    set i = i + 1;
  end while;
end $$
delimiter ;

repeat语句，当条件成立时结束循环：

sql

repeat
命令
until 条件
end repeat;

示例：

sql

delimiter $$
create procedure addStudent(in start int, in num int)
begin
  declare i int default 0;
  repeat
    insert into student(id) values (start + i);
    set i = i + 1;
    until i >= num
  end repeat;
end $$
delimiter ;

loop语句，可以在循环里判断，可以使用leave跳出循环，也可以使用iterate结束循环：

sql

循环名称: loop
命令
if 条件 then
leave 循环名称;
iterate 循环名称;
end if;
end loop 循环名称;

示例：

sql

delimiter $$
create procedure addStudent(in start int, in num int)
begin
  declare i int default 0;
  ADD_STUDENT: loop
    insert into student(id) values (start + i);
    set i = i + 1;
    if i == 0 then
      iterate ADD_STUDENT;
    end if;
    if i >= num then
      leave ADD_STUDENT;
    end if;
  end loop ADD_STUDENT;
end $$
delimiter ;

2.2 使用

语法：

sql

1	call 存储过程名称([参数]);

示例：

sql

mysql> call showScore();
+----+---------+--------+-------+
| id | student | course | grade |
+----+---------+--------+-------+
|  1 | 张三    | 计算机 |    98 |
|  2 | 张三    | 英语   |    53 |
|  3 | 李四    | 计算机 |    48 |
|  4 | 李四    | 中文   |    38 |
|  5 | 王五    | 中文   |    95 |
|  6 | 赵六    | 计算机 |    70 |
|  7 | 赵六    | 英语   |    92 |
|  8 | 赵六    | 中文   |    73 |
|  9 | 孙七    | 英语   |    94 |
| 10 | 周八    | 计算机 |    90 |
| 11 | 周八    | 英语   |    85 |
| 12 | 吴九    | 计算机 |    90 |
| 13 | 吴九    | 中文   |    55 |
+----+---------+--------+-------+
13 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

2.3 查看

查看所有存储过程：

sql

1	show procedure status;

查看存储过程的创建：

sql

1	show create procedure 存储过程名称;

2.4 修改

可以通过先删除存储过程，然后重新创建存储过程的方法实现修改的操作。

2.5 删除

语法：

sql

1	drop procedure 存储过程名称;

示例：

sql

mysql> drop procedure showScore;
Query OK, 0 rows affected (0.00 sec)

mysql>