抱歉，您的浏览器无法访问本站
本页面需要浏览器支持（启用）JavaScript
了解详情 >

管理存储过程

2.6k 字

10 分钟

次

条

摘要：本文学习了如何使用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
1
2
3
4
5
6
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.2 使用

语法：

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

示例：

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
1
2
3
4
mysql> drop procedure showScore;
Query OK, 0 rows affected (0.00 sec)

mysql>

3 变量

3.1 系统变量

系统定义的变量，根据生效范围可以分为全局变量和会话变量：

  • 全局变量：在所有会话有效，修改会影响所有会话，服务重启后失效。
  • 会话变量：只在当前会话有效，修改只会影响当前会话，断开连接后失效。

3.1.1 全局变量

查看变量列表：

sql
1
show global variables [like 搜索条件];

查看变量的值：

sql
1
select @@global.变量名;

可以通过命令修改变量的值，修改后立即生效，服务重启后恢复默认值：

sql
1
2
set @@global.变量名 = 变量值;
set global 变量名 = 变量值;

也可以通过配置文件修改变量的值，服务重启后生效。

3.1.2 会话变量

查看变量列表：

sql
1
2
show variables [like 搜索条件];
show session variables [like 搜索条件];

查看变量的值：

sql
1
2
select @@变量名;
select @@session.变量名;

只能通过命令修改变量的值，修改后立即生效，断开连接后恢复默认值：

sql
1
2
3
4
set @@变量名 = 变量值;
set @@session.变量名 = 变量值;
set 变量名 = 变量值;
set session 变量名 = 变量值;

3.2 用户变量

用户定义的变量，根据生效范围可以分为会话变量和局部变量：

  • 会话变量：只在当前会话有效，修改只会影响当前会话，断开连接后失效。
  • 局部变量：只在存储过程和函数中的语句块中有效。

3.2.1 会话变量

会话变量的变量名前面需要使用@符号进行标识。

定义：

  • 直接定义：
    sql
    1
    2
    set @变量名 = 变量值;
    set @变量名 := 变量值;
  • 查询定义：
    sql
    1
    2
    select @变量名 := 表达式 [from 查询语句];
    select 表达式 into @变量名  [from 查询语句];

使用：

sql
1
select @变量名;

3.2.2 局部变量

局部变量的变量名前面不需要使用任何标识。

声明：

sql
1
declare 变量名 类型 [default 值];

说明：

  • 只能在存储过程或函数的begin和end语句块中声明，并且需要在首行声明。
  • 如果在声明时没有定义默认值，则使用NULL作为初始值。

赋值：

  • 直接赋值：
    sql
    1
    2
    set 变量名 = 变量值;
    set 变量名 := 变量值;
  • 查询赋值：
    sql
    1
    2
    select 变量名 := 表达式 [from 查询语句];
    select 表达式 into 变量名  [from 查询语句];

使用：

sql
1
select 变量名;

4 错误处理

4.1 默认错误处理

在执行存储过程和函数时，如果遇到错误，服务器会退出当前逻辑停止执行，并抛出错误。

如果需要在出现错误时，根据不同的错误类型进行不同的错误处理，保证程序逻辑的完整执行，就需要对错误进行处理。

4.2 错误条件

语法：

sql
1
declare 错误名称 condition for 错误类型;

错误类型：

  • 错误码：错误对应的数值代码。
  • SQLSTATE = 错误字符串码：错误对应的字符串代码。

错误条件是可选的，其目的是为处理逻辑提供更加明确的错误名称。

4.3 处理逻辑

语法：

sql
1
declare 处理方式 handler for 错误类型 处理语句;

处理方式：

  • CONTINUE：表示遇到错误不处理，继续执行。
  • EXIT：表示遇到错误马上退出。
  • UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持。

错误类型：

  • 错误码：错误对应的数值代码。
  • SQLSTATE 错误字符串码：错误对应的字符串代码。
  • 错误名称：错误条件中定义的错误名称。
  • SQLWARNING：匹配所有以01开头的SQLSTATE错误。
  • NOT FOUND：匹配所有以02开头的SQLSTATE错误。
  • SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误。

处理语句：

  • 可以通过设置变量进行处理。
  • 也可以通过复合语句进行处理。

处理逻辑是必选的，匹配指定的错误条件并执行相应的处理逻辑。

5 流程控制

5.1 判断语句

5.1.1 if语句

语法：

sql
1
2
3
4
if 条件 then 命令
[elseif 条件 then 命令]
[else 命令]
end if;

示例：

sql
1
2
3
4
5
6
7
8
9
10
11
12
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 ;

5.1.2 case语句

语法：

sql
1
2
3
4
5
case 变量
when 条件 then 命令
[...]
else 命令
end case;

示例：

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
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 ;

5.2 循环语句

5.2.1 while语句

当条件成立时执行循环。

语法：

sql
1
2
3
while 条件 do
  命令
end while;

示例：

sql
1
2
3
4
5
6
7
8
9
10
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 ;

5.2.2 repeat语句

当条件成立时结束循环。

语法：

sql
1
2
3
4
repeat
  命令
  until 条件
end repeat;

示例：

sql
1
2
3
4
5
6
7
8
9
10
11
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 ;

5.2.3 loop语句

可以在循环里判断，可以使用leave跳出循环，也可以使用iterate结束循环。

语法：

sql
1
2
3
4
5
6
7
循环名称: loop
  命令
  if 条件 then
    leave 循环名称;
    iterate 循环名称;
  end if;
end loop 循环名称;

示例：

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 ;
管理存储过程
https://momashanhe.com/posts/20250522/160327/
本文作者
墨码山河
发布于
2025年5月22日
更新于
2025年6月21日
许可协议
CC BY-NC-SA 4.0
博文难免存在纰漏，如有错误之处，无论大小，恳请读者指正，博主不胜感激。
数据库

MySQL

评论