MySQL自定义函数和存储过程

一、介绍

MySQL函数是一些具有特定功能的方法,在编写sql时,可以进行使用,从而完成对数据的处理。

存储过程的话,更像是一些特定功能的sql组合而成的sql语句集合,由于它的事先编译,通过传入参数来执行这个过程,可以使得,在某些场景下利用存储过程的情况,达到一些快捷方便的功能。

一起来看看他们的定义和写法把。首先,加一下基础的表格

1
2
3
4
5
6
7
8
9
10
11
-- 用户表
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`sex` varchar(2) DEFAULT NULL COMMENT '性别',
`age` tinyint(4) DEFAULT NULL COMMENT '年龄',
`birthday` datetime DEFAULT NULL COMMENT '生日',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

INSERT INTO `tb_user`(`id`, `name`, `sex`, `age`, `birthday`) VALUES (1, '半月无霜', '男', 18, '2022-04-29 09:06:52');

二、自定义函数

1)无参函数

1
2
3
4
5
6
7
8
9
10
11
12
-- 删除
drop function if exists randomNum10;

-- 创建
create function randomNum10()
returns int
begin
return floor(rand()*10);
end;

-- 使用
select randomNum10();

2)有参函数

1
2
3
4
5
6
7
8
9
10
11
12
-- 删除
drop function if exists randomNum;

-- 创建
create function randomNum(num int)
returns int
begin
return floor(rand()*num);
end;

-- 使用
select randomNum(5);

最基本的使用就是上面这样了,由于自定义函数与存储过程内,有挺多相同的东西,将在存储过程中一一介绍。

三、存储过程

语法结构

1
2
3
4
5
6
7
8
9
10
-- delimiter $$ 定义结束符,避免与存储过程中的分号结束符冲突
delimiter $$

-- proc_parameter 出入参定义
-- characteristic 特性
-- routine_body 存储过程体,进行业务SQL的编写,BEGIN...END
create procedure 函数名([proc_parameter...])
[characteristic...]
routine_body
$$

上述便是存储过程的语法结构,对照上面语法结构,来简单书写一个存储过程

1
2
3
4
5
6
7
8
9
10
11
12
-- 删除
drop procedure if exists pro_test;

-- 定义
delimiter $$
create procedure pro_test()
begin
select now();
end $$

-- 调用
call pro_test();

image-20220508165829250

1)变量

1.1)局部变量

局部变量,需要先定义,才能进行使用。且只能在当前的begin ... end中使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
drop procedure if exists pro_var01;

-- 定义
delimiter $$
create procedure pro_var01()
begin
-- 定义
declare var_name varchar(32) default '半月无霜';
declare var_sex varchar(2);
declare var_age int default 18;
-- 重新赋值
set var_age = 19;
select sex into var_sex from tb_user where `name` = var_name;
-- 查询
select var_name as name, var_sex as sex, var_age as age;
end $$

-- 调用
call pro_var01();

image-20220508185423272

1.2)用户变量

用户变量就是用户自己定义的变量,也是在连接断开时失效。对比使用局部变量,便是不再需要提前定义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
drop procedure if exists pro_var02;

-- 定义
delimiter $$
create procedure pro_var02()
begin
-- 赋值
set @name = '半月无霜';
set @age = 18;
select sex into @sex from tb_user where `name` = @name;
-- 查询
select @name, @sex, @age;
end $$

-- 调用
call pro_var02();

image-20220509230904992

用户在同个连接中,还可以使用此变量

1
select @name as "姓名", @sex as "性别";

image-20220509230837745

1.3)会话变量

如果说上面的用户变量是我们自己在一个连接中自定义的变量的话,那么会话变量就是MySQL在一个连接中初始化定义的一些变量。

在连接建立完成后,MySQL会将自己全局变量值复制一份成为当前连接的会话变量。

1
2
3
4
5
6
-- 查看当前所有的会话变量
show session variables;
-- 查看某个会话变量
select @@session.group_concat_max_len;
-- 修改会话变量
set session group_concat_max_len = 102400;

在存储过程中不常用

1.4)全局变量

在上面的会话变量中,提到了全局变量。那么这个全局变量,就是MySQL服务在启动时,从配置文件中加载的一些变量,包括了一些系统的基本信息,基本配置等参数。

对比会话变量的使用,全局变量差不多

1
2
3
4
5
6
-- 查看当前所有的全局变量
show global variables;
-- 查看某个全局变量
select @@global.group_concat_max_len;
-- 修改全局变量
set global group_concat_max_len = 102400;

1.5)对比

操作类型 局部变量 用户变量 会话变量 全局变量
出现的位置 函数、存储过程 命令行、函数、存储过程 命令行、函数、存储过程 命令行、函数、存储过程
定义的方式 declare count int; 直接使用,@var形式 只能查看修改,不能定义 只能查看修改,不能定义
有效生命周期 begin…end 一个连接内有效,当连接断开时,变量失效 一个连接内有效,当连接断开时,变量失效 服务器重启时恢复默认值
查看所有变量 / / show session variables;
show variables;
show global variables;
查看部分变量 / / select @@session.core_file; select @@global.core_file;
查看指定变量 select count; select @var; select @@session.core_file; select @@global.core_file;
设置指定变量 set count=1;
set count:=101;
select 1 into count;
set @var=1;
set @var:=101;
select 1 into @var;
set session core_file = 'ON'; set global core_file = 'ON';

2)出入参

2.1)IN 入参

1
2
3
4
5
6
7
8
9
10
11
12
drop procedure if exists pro_params01;

-- 定义函数,入参语法,[in 参数名 参数类型,...]
delimiter $$
create procedure pro_params01(in var_name varchar(32), in var_age int, in var_sex varchar(2))
begin
set var_age = var_age+1;
select var_name, var_sex, var_age;
end $$

-- 调用
call pro_params01('半月无霜', 18, '男');

image-20220510102800581

2.2)OUT 出参

1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop procedure if exists pro_params02;

-- 定义,出参语法,[out 参数名 参数类型,...]
delimiter $$
create procedure pro_params02(in var_name varchar(32), out out_age int, out out_sex varchar(2))
begin
select age, sex into out_age, out_sex from tb_user where name = var_name;
end $$

-- 调用
call pro_params02('半月无霜', @age, @sex);

-- 查看出参
select '半月无霜', @age, @sex;

image-20220510102739225

2.3)INOUT 出入参

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
drop procedure if exists pro_params03;

-- 定义函数,出入参语法,[inout 参数名 参数类型,...]
delimiter $$
create procedure pro_params03(inout var_name varchar(32), inout var_age int, inout var_sex varchar(2))
begin
-- 定义
declare var_temp varchar(32) default var_name;
-- 重新赋值
set var_name = concat('你好,', var_name);
set var_age = var_age + 1;
select sex into var_sex from tb_user where name = var_temp;
end $$

-- 调用
set @name = '半月无霜';
set @age = 18;
set @sex = '未知';
call pro_params03(@name, @age, @sex);

-- 查看出参
select @name, @age, @sex;

image-20220510104935000

3)判断语句

使用if...then...else...end if来进行,使用的语法格式如下

1
2
3
4
if 判断语句 then 处理内容
[elseif 判断语句 then 处理内容] ...
[else 处理内容]
end if

在过程中的具体使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
drop procedure if exists pro_judge01;

-- 定义
delimiter $$
create procedure pro_judge01(in var_score int, out var_result varchar(4))
begin
if var_score > 60 then
set var_result = '合格';
elseif var_score = 60 then
set var_result = '刚刚好';
else
set var_result = '不合格';
end if;
select var_result as "结果";
end $$

-- 调用
call pro_judge01(60, @result);

image-20220510110559738

4)循环语句

循环基本上是有三种标准的写法,看自己喜欢用哪一种吧

4.1)WHILE

while语句语法

1
2
3
while 判断语句 do
循环体
end while;

在过程中的具体使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
drop procedure if exists pro_cyclic01;

-- 定义
delimiter $$
create procedure pro_cyclic01()
begin
-- 定义变量
declare var_i int default 0;
-- 循环判断
while var_i < 10 do
-- 插入
INSERT INTO `tb_user`(`name`, `sex`, `age`) VALUES (concat('批量', var_i), '男', 18+var_i);
-- 不要忘记自增,否则会造成死循环
set var_i = var_i+1;
end while;
end $$

-- 调用
call pro_cyclic01();

-- 查看表
select * from tb_user;

image-20220510115319781

4.2)REPEAT

第二种循环写法写法,其中判断语句有所不同,为真才会退出。

1
2
3
4
-- 循环,直到...则退出
repeat
循环体
until 判断语句 end repeat;

在过程中的具体使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
drop procedure if exists pro_cyclic02;

-- 定义
delimiter $$
create procedure pro_cyclic02()
begin
declare var_i int default 0;
repeat
INSERT INTO `tb_user`(`name`, `sex`, `age`) VALUES (concat('repeat批量', var_i), '男', 18+var_i);
set var_i = var_i+1;
until var_i > 10 end repeat;
end $$

-- 调用
call pro_cyclic02();

-- 查看表
select * from tb_user where name like 'repeat%';

image-20220510141058774

4.3)LOOP

loop循环,语法如下

1
2
3
4
5
6
标签:loop
循环体
if 判断语句 then
leave 标签;
end if;
end loop;

在过程中的具体使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
drop procedure if exists pro_cyclic03;

-- 定义
delimiter $$
create procedure pro_cyclic03()
begin
declare var_i int default 0;
loop_label:loop
INSERT INTO `tb_user`(`name`, `sex`, `age`) VALUES (concat('loop批量', var_i), '男', 18+var_i);
set var_i = var_i+1;
if var_i > 10 then
leave loop_label;
end if;
end loop;
end $$

-- 调用
call pro_cyclic03();

-- 查看表
select * from tb_user where name like 'loop%';

image-20220510185728893

5)游标

在上面的循环中,好像只是简单的次数循环。那如果需要对查询的结果集进行循环的话,上面的循环方法就无能为力了。

这时候我们就得使用到游标,来对查询结果集进行遍历。

简单来看看这个需求,现在我们有一张计划表tb_user_plan,我们将遍历这张表,根据里面定义的计划向tb_user中插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 计划表
CREATE TABLE `tb_user_plan` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`sex` tinyint(4) DEFAULT NULL COMMENT '性别,1=男,2=女',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`count` int(11) DEFAULT NULL COMMENT '数量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `tb_user_plan`(`id`, `name`, `sex`, `age`, `count`) VALUES (1, '遍历1', 1, 18, 4);
INSERT INTO `tb_user_plan`(`id`, `name`, `sex`, `age`, `count`) VALUES (2, '遍历2', 1, 18, 4);
INSERT INTO `tb_user_plan`(`id`, `name`, `sex`, `age`, `count`) VALUES (3, '遍历3', 1, 19, 4);
INSERT INTO `tb_user_plan`(`id`, `name`, `sex`, `age`, `count`) VALUES (4, '遍历4', 2, 18, 4);
INSERT INTO `tb_user_plan`(`id`, `name`, `sex`, `age`, `count`) VALUES (5, '遍历5', 2, 18, 4);

那么过程的实现如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
drop procedure if exists pro_cursor;

-- 定义
delimiter $$
create procedure pro_cursor()
begin
declare var_i int;
declare var_name varchar(32);
declare var_sex tinyint;
declare var_age int;
declare var_count int;
declare done int default 0;
-- 定义游标
declare my_cursor cursor for select name, sex, age, count from tb_user_plan;
declare continue handler for not found set done = 1;

-- 打开游标
open my_cursor;
-- 遍历游标
my_label:loop
-- 取出每一项的值
fetch my_cursor into var_name, var_sex, var_age, var_count;
-- 如果遍历完成则退出
if done=1 then
leave my_label;
end if;

-- 循环业务
set var_i = 0;
while var_i<var_count do
INSERT INTO `tb_user`(`name`, `sex`, `age`) VALUES (var_name, var_sex, var_age);
set var_i = var_i+1;
end while;
end loop my_label;
-- 关闭游标
close my_cursor;
end $$

-- 调用
call pro_cursor();

-- 查看表
select * from tb_user where name like '遍历%';

image-20220513092657552

在使用上的流程简单说就是

  1. 定义游标

  2. 打开游标

  3. 遍历游标,使用loop方式

  4. 如果游标遍历完成,使用leave方式离开循环

  5. 业务,过程体

  6. 循环结束,与步骤3行成闭环

  7. 关闭游标,与步骤2行成闭环

6)异常

在存储过程运行的过程中,程序会发生一些有一定可能会出现的异常,如果不对这些异常进行处理,会导致我们的存储过程运行失败。所以对应Java中的try...catch...,存储过程也有一套自己的异常捕获处理方式。

在上面的定义游标时,应该有发现了,我们额外定义了一个continue handler,如下

1
declare continue handler for not found set done = 1;

这就要和我们要讲得异常处理有关,上面这个只是其中之一。实际上有三个概念,分别如下

  • condition

  • handler

  • diagnostics area

6.1)CONDITION

存储过程中出现的异常被称为condition,就像java中的Exception一样。

我们可以定义一个conditionMySQL官方说的,定义语法结构如下

1
2
3
4
5
6
DECLARE condition_name CONDITION FOR condition_value

condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
}

这段语句声明了一个错误条件,将名称与需要特定处理的条件相关联。此处定义的condition将会在后续被handler进行处理。

很多人不清楚condition_value是什么,上面官方语法结构已经说了,分别可以是mysql_error_code、``

  • mysql_error_code:表示 MySQL 错误代码的整数文字,查看官网有哪些错误代码
    • 不要使用 MySQL 错误代码 0,因为这表示成功而不是错误条件
  • SQLSTATE [VALUE] sqlstate_value:一个 5 字符的字符串文字,指示 SQLSTATE 值,查看官网有哪些错误代码
    • 不要使用以 开头的 SQLSTATE 值, '00’因为它们表示成功而不是错误情况

使用mysql_error_code声明condition

1
2
3
4
5
DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table-- handler,下一小章会讲到
BEGIN
-- body of handler
END;

使用SQLSTATE值声明condition

1
2
3
4
5
DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;

在官网的错误代码列表可以搜到,105142s02指的是没有找到表

image-20220515123502071

6.2)HANDLER

在上面已经两次提到了handler,大家应该知道了它的作用了吧。这个handler是用来处理condition的,当condition发生时,就会执行handler中的处理逻辑。

官网的文档,语法结构图如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement

handler_action: {
CONTINUE
| EXIT
| UNDO
}

condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}

通过语法结构来看,我们可以发现这些信息

  • 一个handler,可以处理多个condition

  • 我们可以不再定义condition,因为handler中可以直接使用mysql_error_codeSQLSTATE

  • 处理逻辑一共有三种

    • CONTINUE:继续执行
    • EXIT:退出
    • UNDO:目前还不支持
  • 其中condition_value还支持其他三种类型的,分别是

    • SQLWARNING:统称,以01为开头的SQLSTATE集合
    • NOT FOUND:统称,以02为开头的SQLSTATE集合
    • SQLEXCEPTION:不以000102开头的SQLSTATE

语法定义就是这样,我们来看官网上的这个例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO test.t VALUES (1);
SET @x = 2;
INSERT INTO test.t VALUES (1);
SET @x = 3;
END;
//
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

简单的说,就是一张表,重复插入,导致主键冲突的condition,这边handler的处理是继续执行。

所以当12行报错时,13行依旧会进行执行,所以最后的答案就是3


当我们要忽略某个condition时,请为其声明一个condition 处理程序并将其与一个空块相关联,begin...end,如下

1
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

在使用循环时,需要注意块标签的范围不包括在块中声明的处理程序的代码,听着有点拗口,我们直接看官方的例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 3;
retry:
REPEAT
BEGIN
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
ITERATE retry; # illegal
END;
IF i < 0 THEN
LEAVE retry; # legal
END IF;
SET i = i - 1;
END;
UNTIL FALSE END REPEAT;
END;

在程序执行时,会报出下面这个异常

image-20220515133947988

这是因为,retry标签在 if块内的语句的范围内 。它不在handler处理程序的范围内,因此那里的引用无效并导致错误。

简单的来说,就是handler不能使用leave或者iterate操控外部的循环。

所以,如果爆出异常,我们要退出时,可以这样

1
DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

如果我们还需要做一些处理的话,我们可以这样

1
2
3
4
DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
block cleanup statements
END;

当然,我们也可以定义一个临时变量,当做状态。就像上面游标的处理方式一样,具体如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 3;
DECLARE done INT DEFAULT FALSE;
retry:
REPEAT
BEGIN
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
SET done = TRUE;
END;
IF done OR i < 0 THEN
LEAVE retry;
END IF;
SET i = i - 1;
END;
UNTIL FALSE END REPEAT;
END;

爆出异常后,修改状态值,后续的程序后判断这个状态,来进行leave或者iterate

6.3)Diagnostics Area

Diagnostics Area是诊断区域,查看官方文档

感觉有点用不到,暂时先不做记录了

四、最后

存储过程以前就在用,但也是和平常懒人一下,用到的时候导出翻博客,没有记录自己的笔记。

这很不好,正好趁这次把存储过程过了一遍,问题不大。

本文写得不是很深,如果有什么新的注意点,我会在此进行更新的。

我是半月,祝你幸福!!!