MySQL 数据库的结构操作之存储过程与函数

[TOC]

## 1. 函数
**什么是函数：**将一段代码块封装到一个结构中，在需要执行代码块的时候，调用结构执行即可，函数分为两类，系统函数和自定义函数

**函数的调用：**任何函数都有返回值，因此函数的调用是通过 `select` 调用

### 1.1. 系统函数
系统定义好的函数，直接调用就可以了，常用系统函数点击[查看](/blog/info-md.html/EB096351606D21F9?type=40)

### 1.2. 自定义函数
自定义函数与系统函数的调用方式一样，自定义的函数属于数据库，跨库调用需要使用库名前缀【库名.dbo.函数名】，函数中不可以执行编辑数据的语句，insert、update 和 delete 都不能使用

**函数要素：**函数名，参数列表[形参和实参]，返回值，函数体（函数体作用域）

+ 形参：定义时的参数叫形参，形参要求指定数据类型
+ 实参：调用时的参数叫实参

**查询可编辑函数设置：**

```SQL
show variables like '%log_bin_trust_function_creators%';
-- 输出结果为ON表示开启了，OFF表示关闭状态
```

**启用可编辑函数设置：**

```SQL
set global log_bin_trust_function_creators = 1;
-- 输出结果为ON表示开启了
```

#### 1.2.1. 创建函数

**语法：**

```SQL
delimiter 自定义结束符
create function 函数名([形参列表]) returns 数据类型 -- 规定要返回的数据类型
begin
  -- 函数体
  -- 返回值 return 类型(数据类型);
end
自定义结束符
delimiter ; -- 将结束符修改回;分号
```

**示例：**

```SQL
delimiter //
create function total(height int) returns double
begin
return height*height*22/1000;
end
//
delimiter ;
-- 创建成功输出：Query OK, 0 rows affected (0.47 sec)
-- 调用示例函数
select total(153)
```

**结果：**

| total(153) |
|------------|
|    514.998 |

#### 1.2.2. 查看函数

**语法：**show function status\G或show function status like '%%'\G

```SQL
show function status like '%total%'\G
```

**结果：**

```SQL
                 Db: study
                Name: total
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2023-05-28 11:01:21
             Created: 2023-05-28 11:01:21
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
```

#### 1.2.3. 查看函数创建语句

**语法：**show create function 函数名\G

```SQL
show create function total;
```

**结果：**

```SQL
            Function: total
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `total`(height int) RETURNS double
begin
return height*height*22/1000;
end
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
```

#### 1.2.4. 删除函数
函数只能先删除后新增

**语法：**drop function 函数名;

```SQL
-- 删除自定义的函数 total
drop function total;
-- 删除成功输出：Query OK, 0 rows affected (0.39 sec)
```

## 2. 存储过程
存储过程（procedure）是一组为了完成特定功能的 SQL 语句集合

### 2.1. 创建存储过程
创建存储过程和触发器、函数一样，需要注意，MySQL 遇到 `；` 分割符就会执行，而存储过程中需要多条语句是需要用 `;` 分割，所以创建存储过程时需要想将 MySQL 遇 `；` 执行的设置修改下，使用delimiter来修改，`delimiter //` 这样就将 `;` 执行变更为 `//` 执行了，创建完存储过程后记得修改回 `;` 执行

**语法：**

```SQL
delimiter //
create procedure 存储过程名称([形参])
begin
end //
delimiter ;
```

#### 2.1.1. 无参存储过程

**示例：**

```SQL
delimiter //
create procedure pr1()
begin
select * from my_student;
select * from my_classs;
end //
delimiter ;
```

**示例调用结果：**

```SQL
call pr1();
```

学生表

| id | birthday   | name   | gender | height | age  | nation |
|--|--|--|--|--|--|--|
|  4 | 1990-11-25 | 关羽| 男|1.85 |52 | 苗|
|  5 | 1992-09-08 | 张飞| 男|1.82 |50 | 汉|
|  7 | 1993-10-22 | 甄宓| 女|1.62 |38 | 汉|

班级表

| id | name | level | createTime | endTime | endReason |
|--|--|--|--|--|--|
|  1 | 1班  |7 | 2023-05-12 | NULL| NULL|
|  2 | 2班  |7 | 2023-05-12 | NULL| NULL|
|  3 | 3班  |7 | 2023-05-12 | NULL| NULL|

#### 2.1.2. 普通参数存储过程

**示例：**

```SQL
delimiter //
create procedure pr2(age int)
begin
select * from my_student where age > age;
end //
delimiter ;
```

**示例调用结果：**获取学生年龄大于50的学生

```SQL
call pr2(50);
```

| id | birthday   | name   | gender | height | age  | nation |
|--|--|--|--|--|--|--|
|  4 | 1990-11-25 | 关羽| 男|1.85 |52 | 苗|

#### 2.1.3. 限定参数存储过程
MySQL 中存储过程有三类限定参数类型，分别是 in、out、inout，存储过程创建时需要的参数都是局部参数

+ in 无返回参数：数据只是从外部传入给内部使用，纯粹的值传递，可以是数值也可以是变量
 + out 有返回参数：只允许存储过程内部使用，不用外部传递数据，可以给外部使用，这种参数限定也可以叫做引用传递，外部的数据会被先清空才会进入到内部，out传递的是只能是变量
 + inout 有返回参数：外部可以在内部使用，内部修改也可以给外部使用，典型的引用传递，只能传变量

**限定参数说明：**存储过程对于返回变量的操作是滞后的，是在存储过程调用结束的时候，才会重新将内部修改的值赋值给外部传入的全局变量

**语法：**

```SQL
delimiter //
create procedure 存储过程名称(in [形参名字] 数据类型,out [形参名字] 数据类型,inout [形参名字] 数据类型)
begin
end //
delimiter ;
```

**示例：**

```SQL
delimiter //
create procedure paramPro(in a int, out b int, inout c int)
begin
-- b的值一定是NULL，因为out限定的参数传入时一定会被清空
set b = a + b;
set c = a + c;
set a = a + b + c;
select a,b,c;
end //
delimiter ;
```

**调用：**out 和 inout 限定的参数必须传入变量，而不能是数值

```SQL
set @a = 1;
set @b = 2;
set @c = 3;
call paramPro(1,@b,@c);
select @a,@b,@c;
```

**调用结果：**

存储过程中的select

| a    | b    | c    |
|------|------|------|
| NULL | NULL |    4 |

> a = a + b + c，b传入时被清空为null了，null和任何数值相加都是null
b = a + b，b传入时被清空为null了，null和任何数值相加都是null
c = a + c，即外部传入的 1(@a) + 3(@b) = 4

外部调用的select

| @a   | @b   | @c   |
|------|------|------|
|    1 | NULL |    4 |

> @a 传入到存储过程中使用的是 in，因此未发生变化，还是1
@b 传入到存储过程中使用的是 out，但传入后立即被清空变成了null，null与任何数值计算都是null，所以参数返回后 @b = null
@c 传入到存储过程中使用的是 inout，存储过程中 c = a + c，所以参数返回后 全局变量@c = 局部变量c = 1 + 3 =4

### 2.2. 查看存储过程

**语法：**show procedure status\G或show procedure status like '%%'\G

```SQL
show procedure status like '%pr1%'\G
```

**结果：**

```SQL
                  Db: study
                Name: pr1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2023-05-28 16:37:19
             Created: 2023-05-28 16:37:19
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
```

### 2.3. 查看存储过程创建语句

**语法：**show create procedure 存储过程名称\G

```SQL
show create procedure pr1;
```

**结果：**

```SQL
           Procedure: pr1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pr1`()
begin
select * from my_student;
select * from my_classs;
end
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
```

### 2.4. 删除存储过程
存储过程只能先删除后新增

**语法：**drop procedure 存储过程名称;

```SQL
drop procedure pr1;
```