MySQL变量、存储过程、函数、流程控制

2021/03/14 MySQL

> 本文档整理自视频教程：[尚硅谷_MySQL核心技术](http://www.atguigu.com/download_detail.shtml?v=3)

# 变量

## 系统变量

> 说明：变量由系统定义，不是用户定义，属于服务器层面

注意：全局变量需要添加`global`关键字，会话变量需要添加`session`关键字；如果不写则**默认会话级别**。

### 全局变量

> 作用域：针对于所有会话（连接）有效，但不能跨重启

- 查看所有全局变量<br>`SHOW GLOBAL VARIABLES;`
- 查看满足条件的部分系统变量<br>`SHOW GLOBAL VARIABLES LIKE '%char%';`
- 查看指定的系统变量的值<br>`SELECT @@global.autocommit;`
- 为某个系统变量赋值
  - `SET @@global.autocommit=0;`
  - `SET GLOBAL autocommit=0;`

### 会话变量

> 作用域：针对于当前会话（连接）有效

- 查看所有会话变量
  - `SHOW SESSION VARIABLES;`
  - `SHOW VARIABLES;`
- 查看满足条件的部分会话变量
  - `SHOW SESSION VARIABLES LIKE '%char%';`
  - `SHOW VARIABLES LIKE '%char%';`
- 查看指定的会话变量的值
  - `SELECT @@autocommit;`
  - `SELECT @@session.autocommit;`
- 为某个会话变量赋值
  - `SET @@session.autocommit=1;`
  - `SET SESSION autocommit=1;`
  - `SET autocommit=1;`

## 自定义变量

> 说明：变量由用户自定义，而不是系统提供的

使用步骤：

1. 声明
2. 赋值
3. 使用（查看、比较、运算等）

### 用户变量

作用域：针对于当前会话（连接）有效，作用域同于会话变量

> 赋值操作符：`=` 或 `:=`

1. 声明并初始化
  - SET @变量名=值;
  - SET @变量名:=值;
  - SELECT @变量名:=值;
2. 赋值（更新变量的值）
  - 方式一：
    - SET @变量名=值;
    - SET @变量名:=值;
    - SELECT @变量名:=值;
  - 方式二：
    - SELECT 字段 INTO @变量名
    - FROM 表;
3. 使用（查看变量的值）
  - SELECT @变量名;

> 使用示例：声明两个变量，求和并打印

```bash
mysql> SET @m=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @n=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sum=@m+@n;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sum;
+------+
| @sum |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
```

### 局部变量

作用域：仅仅在定义它的`begin end`块中有效，应用在`begin end`中的第一句话

1. 声明
  - DECLARE 变量名 类型;
  - DECLARE 变量名 类型 【DEFAULT 值】;
2. 赋值（更新变量的值）
  - 方式一：
    - SET 局部变量名=值;
    - SET 局部变量名:=值;
    - SELECT 局部变量名:=值;
  - 方式二：
    - SELECT 字段 INTO 具备变量名 FROM 表;
3. 使用（查看变量的值）
  - SELECT 局部变量名;

> 使用示例：

后面在**函数**中举例

### 用户变量与局部变量的比较

类型 | 作用域 | 定义位置 | 语法
---|---|---|---
用户变量 | 当前会话 | 会话的任何地方 | 加`@`符号，不用指定类型
局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加`@`，需要指定类型

# 存储过程

## 介绍

> 含义：一组预先编译好的SQL语句的集合，理解成批处理语句

1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

## 语法

### 创建和调用

#### 创建语法

```sql
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END
```

> 注意：

1. 参数列表包含三部分
  - 参数模式
    - in：该参数可以作为输入，也就是该参数需要调用方传入值
    - out：该参数可以作为输出，也就是该参数可以作为返回值
    - inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值又可以返回值
  - 参数名
  - 参数类型
2. 如果存储过程体仅仅只有一句话，begin end可以省略
3. 存储过程体中的每条sql语句的结尾要求必须加分号。
4. 存储过程的结尾可以使用 delimiter 重新设置<br>语法：`delimiter 结束标记`

> 举例

- 参数列表举例：`in stuname varchar(20)`
- 结束标记举例：`delimiter $`

#### 调用语法

`CALL 存储过程名(实参列表);`

#### 示例

注：以下示例中

- 若在`cmd`脚本中，需要使用了`DELIMITER $`修改结束标记，后面的语句结束符变更为`$`，即调用使用`CALL myp1()$`
- 若在`SQLyog`或者`Navicat`等客户端中，建议使用**存储过程/函数设计工具**，工具中不需要使用`DELIMITER $`，且`END`后不需要添加分隔符，保存后`CALL`使用正常的`;`分隔符，即`CALL myp1();`

##### 空参列表

> 案例1：插入到user表中三条记录

```sql
DELIMITER $
# 创建
CREATE PROCEDURE myp1 () BEGIN
	INSERT INTO USER ( username, `password` )
	VALUES
		( 'john1', '123' ),
		( 'lily', '123' ),
		( 'rose', '123' )
	;
END $
# 调用
CALL myp1 ()$
```

> 注意事项

该事项下同

##### 创建带`in`模式参数的存储过程

> 案例2：根据用户名称查询对应的用户（单参数）

```sql
DELIMITER $
# 创建
CREATE PROCEDURE myp2 (
	IN username VARCHAR ( 20 )
	)
BEGIN
	SELECT
		u.* 
	FROM
		user u
	WHERE
		u.username LIKE CONCAT( '%', username, '%' );
END $
# 调用
CALL myp2('rose')$
```

> 案例3：验证用户密码是否正确（多参数）

```sql
DELIMITER $
# 创建
CREATE PROCEDURE myp3 (
	IN username VARCHAR ( 20 ),
	IN PASSWORD VARCHAR ( 20 )
	)
BEGIN
	DECLARE
		result INT DEFAULT 0;#声明并初始化
	SELECT
		COUNT(*) INTO result #赋值
	FROM `user` u
	WHERE
		u.username = username 
		AND u.`password` = PASSWORD;
	SELECT IF ( result > 0, '成功', '失败' );#使用
END $
#调用
CALL myp3 ( '张飞', '8888' )$
```

##### 创建`out`模式参数的存储过程

> 案例4：根据输入的用户名返回对应的用户ID（单参数，多参数同上）

注意：若`SELECT`返回多条结果，则执行报错

```sql
DELIMITER $
# 创建
CREATE PROCEDURE myp4(IN username VARCHAR(20),OUT id INT(11))
BEGIN
	SELECT
		u.id INTO id
	FROM
		user u
	WHERE
		u.username = username;
END $
# 调用
CALL myp4('小昭',@userId) $
SELECT @userId $
```

##### 创建带`inout`模式参数的存储过程

> 案例5：传入a和b两个值，最终a和b都翻倍并返回

```sql
DELIMITER $
# 创建
CREATE PROCEDURE myp8 ( INOUT a INT, INOUT b INT ) BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $
# 调用
SET @m = 10 $ 
SET @n = 20 $
CALL myp8 ( @m, @n ) $
SELECT @m, @n$
```

### 删除

> 语法：`DROP PROCEDURE 存储过程名`

### 查看

- `DESC myp2;`
- `SHOW CREATE PROCEDURE  myp2;`

# 函数

## 介绍

> 含义同存储过程

区别：

- 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
- 函数：有且仅有1个返回，适合做处理数据后返回一个结果

## 语法

### 创建和调用

#### 创建

```sql
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END
```

> 注意：

1. 参数列表 包含两部分：
  - 参数名
  - 参数类型
2. 函数体肯定会有return语句，如果没有会报错。如果return语句没有放在函数体的最后也不报错，但不建议
3. 函数体中仅有一句话，则可以省略begin end
4. 使用`delimiter`语句设置结束标记

#### 调用

`SELECT 函数名(参数列表)`

#### 示例

##### 无参有返回值

> 案例1：返回用户个数

```sql
# 创建
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0; # 定义局部变量
	SELECT COUNT(*) INTO c # 赋值
	FROM `user`;
	RETURN c;
END
# 调用
SELECT myf1()
```

##### 有参有返回值

> 案例2：查询密码为`123`的用户数（单个参数）

```sql
# 创建
CREATE FUNCTION myf2 ( `password` VARCHAR ( 20 ) ) RETURNS DOUBLE BEGIN
	SET @sal = 0;#定义用户变量
	SELECT
		COUNT( u.id ) INTO @sal #赋值
	FROM
		`user` u 
	WHERE
		u.`password` = PASSWORD;
	RETURN @sal;
END
# 调用
SELECT myf2 ('123')
```

> 案例3：创建函数，实现传入两个float，返回二者之和（多个参数）

```
# 创建
CREATE FUNCTION myf3 ( num1 FLOAT, num2 FLOAT ) RETURNS FLOAT BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM = num1 + num2;
	RETURN SUM;
END
# 调用
SELECT test_fun1(1,2)
```

### 查看

`SHOW CREATE FUNCTION myf2;`

### 删除

`DROP FUNCTION myf3;`

# 流程控制

- 顺序
- 分支
- 循环

## 分支结构

### if函数

> 语法：

if(条件,值1，值2)
    
实现双分支，应用在`begin end`中或外面

> 案例1：创建函数，实现传入成绩，成绩>90返回A，成绩>80返回B，成绩>60返回C，否则返回D

```sql
# 创建
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END
# 调用
SELECT test_if(87)
```

### case结构

> 语法：

情况1：类似于switch

case 变量或表达式
    when 值1 then 语句1;
    when 值2 then 语句2;
    ...
    else 语句n;
    end

情况2：

case 
    when 条件1 then 语句1;
    when 条件2 then 语句2;
    ...
    else 语句n;
    end

应用在`begin end`中或外面

> 案例2：创建存储过程，如果工资<2000则删除，如果5000>工资>2000则涨工资1000，否则涨工资500

```sql
# 准备表结构和数据
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `salary` double(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;
INSERT INTO `employees` VALUES (1, 300.00);
INSERT INTO `employees` VALUES (2, 3000.00);
INSERT INTO `employees` VALUES (3, 6000.00);
# 创建
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
	ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
	END IF;
END
# 调用
CALL test_if_pro(300);
```

### if结构

> 语法：

if 条件1 then 语句1;
    elseif 条件2 then 语句2;
    ....
    else 语句n;
    end if;

类似于多重if，只能应用在begin end 中

> 案例3：创建函数，实现传入成绩，成绩>90返回A，成绩>80返回B，成绩>60返回C，否则返回D

```sql
# 创建
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	RETURN ch;
END
# 调用
SELECT test_case(56)
```

##  循环结构

> 分类：

- while
- loop
- repeat

> 循环控制：

- `iterate`类似于`continue`继续，结束本次循环，继续下一次
- `leave`类似于`break`跳出，结束当前所在的循环

### while

> 语法：

【标签:】while 循环条件 do
    	循环体;
    end while【 标签】;

联想：

while(循环条件){
    
    	循环体;
    }

### 2.loop

> 语法：

【标签:】loop
    	循环体;
    end loop 【标签】;

可以用来模拟简单的死循环

### repeat

> 语法：

【标签：】repeat
    	循环体;
    until 结束循环的条件
    end repeat 【标签】;

### 案例

#### 没有添加循环控制语句

> 批量插入，根据次数插入到admin表中多条记录

```sql
# 创建
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO user(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
END
# 调用
CALL pro_while1(100)
```

#### 添加leave语句

> 批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止

```sql
# 创建
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO user(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END
# 调用
CALL test_while1(100)
```

#### 添加iterate语句

> 批量插入，根据次数插入到admin表中多条记录，只插入偶数次

```sql
# 创建
CREATE PROCEDURE test_while2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO user(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
	END WHILE a;
END 
# 调用
CALL test_while2(100)
```