MySQL之存储过程与触发器

# MySQL之存储过程

## 变量

@@是系统变量

@是用户自定义的变量

### 系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。

查看系统变量

#### 模板

```
SHOW [ SESSION | GLOBAL] VARIABLES ; -- 查看所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '........'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
```

#### 实操

```
show session variables ;

show session variables like 'auto%';
show global variables like 'auto%';

select @@autocommit;
select @@global.autocommit;

-- 设置系统变量
set session autocommit = 0;
```

---

 

---

#### 注意：

1. 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。

2. mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

---

### 用户定义的变量

**用户定义变量 **是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接（当前会话）

注意:
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

#### 赋值

​ 赋值建议使用 := ，因为MySQL中没有 == ， = 既可以做赋值运算符，又可以做比较运算符，所以赋值建议使用 :=

```
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
```

```
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;
```

#### 使用

```
SELECT @var_name ;
```

#### 实操

```
-- 变量：用户变量
-- 赋值
set @myname := 'itheng';
set @myage := 20;
set @mygender := '男',@myhobby := 'Java';

-- 使用
select @myname,@myage,@mygender,@myhobby;

select count(*) into @mycount from tb_user;
select @mycount;

select @myname; #用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
```

---

### 局部变量

**局部变量** 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ...END块。

#### 声明

```
DECLARE 变量名 变量类型[DEFAULT ...];
```

变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

#### 赋值

```
set 变量名 = 值；
set 变量名 := 值；
SELECT 字段名 INTO 变量名 FROM 表名...;
```

```
-- 变量：局部变量
-- 声明：declare
-- 赋值
create procedure p2()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;

call p2();
```

## 存储过程语法（基于局部变量）

### if

语法：

```
if 条件1 then
...
elseif 条件2 then --可选
...
else --可选
...
end if;
```

#### 实操：

```
create procedure p1()
begin
declare score int default 59;
declare degree char(10) ;
if score < 60 then
set degree := '不及格';
elseif score >=60 and score <85 then #注意：elseif如果分开写，则会报错
set degree :='及格';
else
set degree :='优秀';
end if;
select degree;
end;

call p1();
```

### 参数

| 类型 | 含义 |
| :---: | :------------------------------------------: |
| IN | 该类参数作为输入，也就是需要调用时传入值 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |

#### 用法：

```
create procedure 存储过程名称([IN/OUT/INOUT参数名 参数类型])
begin
--SQL语句
end;
```

#### 实操：

```
#给分数分等级
create procedure p2(in score int , out degree varchar(10))
begin
if score < 60 then
set degree := '不及格';
elseif score >=60 and score <85 then #注意：elseif如果分开写，则会报错
set degree :='及格';
else
set degree :='优秀';
end if;
select degree;
end;

call p2(58,@degree);
```

```
#将传入的200分制的分数，换算成百分制。
create procedure p3(inout score double)
begin
set score = 0.5 * score;
select @score;
end;

set @score = 78 ;
call p3(@score);
-- 不能在p3括号里对@score进行赋值，存储过程的参数需要在 CALL 语句中指定，并且不能使用赋值操作符。
```

---

### case

#### 语法1：

```
case case_balue
when when_value1 then statement_list1
[when when_value2 then statement_list2]...
[else statement_list]
end case;
```

#### 语法2：

```
case
when search_condition then statement_list1
[when search condition2 then statement_list2]....
[else statement_list]
end case;
```

#### 实操：

```
DELIMITER //

CREATE PROCEDURE p6(IN month INT)
BEGIN
DECLARE result VARCHAR(10);
CASE
WHEN month >= 1 AND month <= 3 THEN
SET result := '第一季度';
WHEN month >= 4 AND month <= 6 THEN
SET result := '第二季度';
WHEN month >= 7 AND month <= 9 THEN
SET result := '第三季度';
WHEN month >= 10 AND month <= 12 THEN
SET result := '第四季度';
ELSE
SET result := '非法参数';
END CASE;

SELECT CONCAT('您输入的月份为:', month, '，这所属的季度为:', result);
END //

DELIMITER ;
call p6(5);
```

---

### while

#### 语法

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

```
#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑。
while 条件 do
sql逻辑...
end while;
```

---

#### 实操

```
create procedure p7(in n int)
begin
declare total int default 0;
while n > 0 do
set total := total + n;
set n := n - 1;
end while;
select total;
end;

call p7(10);
```

---

### repeat

#### 语法

repeat是有条件的循环控制语句，当满足条件的时候退出循环，具体语法为：

```
#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环。
repeat
sql逻辑...
until 条件
end repeat;
```

#### 实操

```
create procedure p8(in n int)
begin
declare total int default 0;

repeat
set total := total + n;
set n := n - 1;
until n <= 0
end repeat;

select total;
end;

call p8(10);
```

---

### loop

#### 语法

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用:

LEAVE:配合循环使用，退出循环。
ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

```
[begin_label:] LOOP
SQL逻辑..
END LooP [end_labell;
```

---

```
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
```

#### 实操

```
create procedure p9(in n int)
begin
declare total int default 0;

sum: loop
if n <= 0 then
leave sum;
end if;

set total := total + n;
set n := n - 1;
select total;

end loop sum;
end;

call p9(10);
```

---

```
create procedure p10(in n int)
begin
declare total int default 0;

sum: loop
if n <= 0 then
leave sum;
end if;

if n % 2 = 1 then
set n := n - 1;
iterate sum;
end if;
set total := total + n;
set n := n - 1;
select total;

end loop sum;
end;

call p10(10);
```

### 游标

游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下:

声明游标

```
DECLARE 游标名称 CURSOR FOR 查询语句
```

打开游标

```
OPEN 游标名称 ;
```

获取游标记录

```
FETCH 游标名称 INTO 变量[,变量];
```

关闭游标

```
CLOSE 游标名称;
```

#### 实操

根据传入的参数uage，来查询用户表tb user中，所有的用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新(id,name,profession)中。

```
create procedure p11(in uage int)
begin

declare u_cursor cursor for select name,profession from student where student.age <= uage;
#注意：游标定义要在变量定义之前
declare uname varchar(100);
declare upro varchar(100);

drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null , uname , upro);
end while;
close u_cursor;
end;

call p11(40);
```

### 条件处理程序

条件处理程序(Handler) 可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为:

```
DECLARE handler action HANDLER FOR condition value [, condition value] ... statement ;
handler_action
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition value
SQLSTATE sglstate_value: 状态码，如 02000
SOLWARNING: 所有以01开头的SOLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
```

#### 实操

```
create procedure p12(in uage int)
begin

declare u_cursor cursor for select name,profession from student where student.age <= uage;
#注意：游标定义要在变量定义之前
declare exit handler for SQLSTATE '02000' close u_cursor;
#declare exit handler for not found close u_cursor;
declare uname varchar(100);
declare upro varchar(100);

drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null , uname , upro);
end while;
close u_cursor;
end;

call p12(40);
```

### 存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下:

```
CREATE FUNCTION 存储函数名称([ 参数列表])
RETURNS type[characteristic ...]
BEGIN
--SQL语句
RETURN ...;
END ;

characteristic说明:
DETERMINISTIC: 相同的输入参数总是产生相同的结果
NO SQL: 不包含 SQL 语句
READS SOL DATA: 包含读取数据的语句，但不包含写入数据的语句
```

#### 实操

计算从1累加到n的值，n为传入的参数值。

```
create function fun1(n int)
returns int deterministic
#这行必须有，因为MySQL8.x版本以后二进制日志（binary logging）默认开启，一旦开启就必须指定characteristic类型
begin
declare total int default 0;

while n > 0 do
set total := total + n;
set n := n - 1;
end while;

return total;
end;

select fun1(100);
```

### 触发器

触发器是与表有关的数据库对象，指在 nset/update/delete 之前或之后，发并执行发器中定义的SL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名 OLD和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语合级触发。

| 触发器类型 | NEW 和 OLD |
| :------------- | :---------------------------------------------------: |
| INSERT型触发器 | NEW表示将要或者已经新增的数据 |
| UPDATE型触发器 | OLD表示修改之前的数据，NEW 表示将要或已经修改后的数据 |
| DELETE型触发器 | OLD表示将要或者已经删除的数据 |

#### 语法

创建

```
create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row --行级触发器
begin
trigger_stmt;
end;
```

查看

```
show triggers;
```

删除

```
drop trigger[schema_name] trigger_name;--如果没有指定schema_name，默认为当前数据库。
```

#### 实操

通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加，修改，删除。

1. 先创建一个日志表

```
create table user_log(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型,insert/update/delete',
operate_time datetime not null comment'操作时间',
operate_id int(11) not null comment'操作的id',
operate_params varchar(500) comment '操作参数',
primary key(id)
)engine=innodb default charset=utf8;
```

2. 插入数据触发器

```
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
insert into user_log(id,operation,operate_time,operate_id,operate_params) values
(null,'insert',now(),new.id,concat('插入的数据内容为：id=',new.id,'name=',new.name,'phone=',new.phone',email='new.email,',profession='new.profession));
end;
```

3. 查看触发器

```
show triggers;
```

4. 删除触发器

```
drop trigger tb_user_insert_trigger;
```

5. 修改数据触发器

```
create trigger tb_user_update_trigger
after update on tb_user for each row
begin
insert into user_log(id,operation,operate_time,operate_id,operate_params) values
(null,'insert',now(),new.id,
concat('更新之前的内容为:id=',old.id,'name=',old.name,'phone=',old.phone',email='old.email,',profession='old.profession)
|'更新之后的内容为：id=',new.id,'name=',new.name,'phone=',new.phone',email='new.email,',profession='new.profession));
end;
```

6. 删除数据触发器

```
create trigger tb_user_trigger
after delete on tb_user for each row
begin
insert into user_log(id,operation,operate_time,operate_id,operate_params) values
(null,'insert',now(),new.id,concat('插入的数据内容为：id=',old.id,'name=',old.name,'phone=',old.phone',email='old.email,',profession='old.profession));
end;
```

## 总结

### 存储过程

1. 实现定义并存储在数据库的一段SQL语句的集合。
2. 减少网络交互，提高性能，封装重用。
3. 变量，if，else，参数（in/out/inout)，while，repeat，loop，cursor，handler

### 存储函数（func同）

1. 存储函数是有返回值的存储过程，参数类型只能为in类型。
2. 存储函数可以被存储过程替代。

### 触发器（trigger）

1. 可以再表数据进行insert，update，delete之前或之后触发
2. 保证数据完整性，日志记录，数据校验。