MySQL 存储函数和存储过程

本文将介绍 MySQL 的存储函数和存储过程。

一、说明

存储函数和存储过程都是对某些语句的封装，存储函数侧重于执行语句并返回一个值，存储过程侧重于单纯执行语句。

二、存储函数

1. 创建存储函数

1
2
3
4
5
6
7
CREATE FUNCTION 存储函数名称([形参列表])
RETURNS 返回值类型
BEGIN
    函数语句;
    函数语句;
    RETURN 函数语句;
END

2. 调用存储函数

1
SELECT 存储函数名称([实参列表])

3. 查看存储函数

1
SHOW CREATE FUNCTION 函数名

4. 删除存储函数

1
DROP FUNCTION 函数名

三、存储过程

1. 创建存储过程

1
2
3
4
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

2. 调用存储过程

1
CALL 存储过程([参数列表]);

3. 查询存储过程

1
SHOW CREATE PROCEDURE 存储过程名称

4. 删除存储过程

1
DROP PROCEDURE 存储过程名称

5. 形参前缀

与存储函数不同的是，存储过程可以在定义形参时添加参数前缀，如下：

1
2
3
4
CREATE FUNCTION 存储函数名称(
	参数类型 [IN | OUT | INOUT] 参数名 数据类型
)
···

其中，

  • IN：
    • 实参不需要是变量
    • 参数值可以被读取
    • 参数不可以被赋值
  • OUT：
    • 实参应该是变量
    • 参数值无法被读取
    • 参数可以被赋值，在存储过程中的改变将可以传递出去
  • INOUT：
    • 实参应该是变量
    • 参数值可以被读取
    • 参数可以被赋值，在存储过程中的改变将可以传递出去

四、语句

1. 定义局部变量

1
DECLARE 变量名1, 变量名2, ... 数据类型 [DEFAULT 默认值];
1
DECLARE c INT;

2. 设置变量值

1
SET 变量名 = 值;

3. 分支语句

1
2
3
4
5
6
7
8
9
10
11
IF 表达式 THEN
    处理语句列表
[
ELSEIF 表达式 THEN
    处理语句列表
]
[
ELSE
    处理语句列表
]
END IF;

4. 循环语句

(1) WHILE

1
2
3
4
WHILE 表达式
DO
    处理语句列表
END WHILE;

(2) REPEAT

1
2
3
4
REPEAT
    处理语句列表
UNTIL 表达式
END REPEAT;

(3) LOOP

1
2
3
LOOP
    处理语句列表
END LOOP;

LOOP 并没有自带循环终止条件，应该将循环终止条件写到处理语句列表中，当符合循环终止条件时，通过 RETURN 语句终止函数或 LEAVE + flag 跳出循环。

5. 游标

(1) 说明

游标可以用于遍历多行的查询结果。

(2) 创建游标

1
2
DECLARE 游标名称 CURSOR
FOR 查询语句;

(2) 打开和关闭游标

1
2
3
OPEN 游标名称;

CLOSE 游标名称;
  • 打开游标意味着执行查询语句，创建一个与查询结果相关联的游标
  • 关闭游标意味着释放以游标相关的资源，用完游标后应将其关闭

(3) 获取记录

1
FETCH 游标名 INTO 变量1, 变量2, ... 变量n

通过该语句，可以将结果集中当前行各列的值分别赋值给多个变量，并且游标对应的行会下移，以便下次获取记录时获取下一行。

(4) 遍历结束事件

游标遍历结束时将会触发一个事件，可以通过语句配置该事件发生时执行的操作。

1
DECLARE CONTINUE HANDLER FOR NOT FOUND 处理语句;

参考