C C++最全【MySQL进阶教程】视图 存储过程 触发器(2)，2024年最新C C++基础面试

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

– 赋值
set @myname = ‘itcast’;
set @myage := 10;
set @mygender := ‘男’,@myhobby := ‘java’;
select @mycolor := ‘red’;
select count(*) into @mycount from tb_user;
– 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;
select @abc;

🍀（3）局部变量


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


（1）声明

DECLARE 变量名 变量类型 [DEFAULT … ] ;

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


（2）赋值

SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 … ;

（3）演示示例:

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

### 4️⃣if


🍀（1）介绍


if 用于做条件判断，具体的语法结构为：

IF 条件1 THEN
…
ELSEIF 条件2 THEN – 可选
…
ELSE – 可选
…
END IF;

在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有。


🍀（2）案例


根据定义的分数score变量，判定当前分数对应的分数等级。


* score >= 85分，等级为优秀。
* score >= 60分 且 score < 85分，等级为及格。
* score < 60分，等级为不及格。

create procedure p3()
begin
declare score int default 58;
declare result varchar(10);
if score >= 85 then
set result := ‘优秀’;
elseif score >= 60 then
set result := ‘及格’;
else
set result := ‘不及格’;
end if;
select result;
end;
call p3();

上述的需求我们虽然已经实现了，但是也存在一些问题，比如：score 分数我们是在存储过程中定义死的，而且最终计算出来的分数等级，我们也仅仅是最终查询展示出来而已。


那么我们能不能，把score分数动态的传递进来，计算出来的分数等级是否可以作为返回值返回呢？答案是肯定的，我们可以通过接下来所讲解的 参数 来解决上述的问题。


### 5️⃣参数


🍀（1）介绍


参数的类型，主要分为以下三种：IN、OUT、INOUT。 具体的含义如下：




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


用法：

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
– SQL语句
END ;

🍀（2）案例一


根据传入参数score，判定当前分数对应的分数等级，并返回。


* score >= 85分，等级为优秀。
* score >= 60分 且 score < 85分，等级为及格。
* score < 60分，等级为不及格。

create procedure p4(in score int, out result varchar(10))
begin
if score >= 85 then
set result := ‘优秀’;
elseif score >= 60 then
set result := ‘及格’;
else
set result := ‘不及格’;
end if;
end;
– 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
select @result;

🍀（3） 案例二


将传入的200分制的分数，进行换算，换算成百分制，然后返回。

create procedure p5(inout score double)
begin
set score := score * 0.5;
end;
set @score = 198;
call p5(@score);
select @score;

### 6️⃣case


🍀（1）介绍


case结构及作用，和我们在基础篇中所讲解的流程控制函数很类似。有两种语法格式：


语法1：

– 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，执行statement_list2， 否则就执行 statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] …
[ ELSE statement_list ]
END CASE;

语法2：

– 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] …
[ELSE statement_list]
END CASE;

🍀（2）案例


根据传入的月份，判定月份所属的季节（要求采用case结构）。


* 1-3月份，为第一季度
* 4-6月份，为第二季度
* 7-9月份，为第三季度
* 10-12月份，为第四季度

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;
call p6(16);

**注意：** 如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接。


### 7️⃣while


🍀（1）介绍


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

– 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑…
END WHILE;

🍀（2）案例


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

– A. 定义局部变量, 记录累加之后的值;
– B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
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(100);

### 8️⃣repeat


🍀（1）介绍


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

– 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑…
UNTIL 条件
END REPEAT;

🍀（2）案例


计算从1累加到n的值，n为传入的参数值。(使用repeat实现)

– A. 定义局部变量, 记录累加之后的值;
– B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
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);
call p8(100);

### 9️⃣loop


🍀（1）介绍


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


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

[begin_label:] LOOP
SQL逻辑…
END LOOP [end_label];

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

上述语法中出现的 begin\_label，end\_label，label 指的都是我们所自定义的标记。


🍀（2）案例一


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

– A. 定义局部变量, 记录累加之后的值;
– B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
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;
end loop sum;
select total;
end;
call p9(100);

🍀（3）案例二


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

– A. 定义局部变量, 记录累加之后的值;
– B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
– C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
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;
end loop sum;
select total;
end;
call p10(100);

### 🔟游标


🍀（1）介绍


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


A. 声明游标

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

B. 打开游标

OPEN 游标名称 ;

C. 获取游标记录

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

D. 关闭游标

CLOSE 游标名称 ;

🍀（2）案例


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

– 逻辑:
– A. 声明游标, 存储查询结果集
– B. 准备: 创建表结构
– C. 开启游标
– D. 获取游标中的记录
– E. 插入数据到新表中
– F. 关闭游标
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
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(30);

上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。


![在这里插入图片描述](https://img-blog.csdnimg.cn/47d1f213492545ff98d3d8699e3cbfbf.png#pic_center)  
 但是此时，tb\_user\_pro表结构及其数据都已经插入成功了，我们可以直接刷新表结构，检查表结构中的数据。


![在这里插入图片描述](https://img-blog.csdnimg.cn/027cf7d7d8cf42d8aae4bc7815e5ddd5.png#pic_center)


上述的功能，虽然我们实现了，但是逻辑并不完善，而且程序执行完毕，获取不到数据，数据库还报错。 接下来，我们就需要来完成这个存储过程，并且解决这个问题。


要想解决这个问题，就需要通过MySQL中提供的 条件处理程序 Handler 来解决。


### 🔟1️⃣条件处理程序


🍀（1）介绍


条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。、


具体语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
… statement ;
handler_action 的取值：
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition_value 的取值：
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

🍀（2）案例


我们继续来完成在上一小节提出的这个需求，并解决其中的问题。根据传入的参数uage，来查询用户表tb\_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id, name, profession )中。


A. 通过SQLSTATE指定具体的状态码

– 逻辑:
– A. 声明游标, 存储查询结果集
– B. 准备: 创建表结构
– C. 开启游标
– D. 获取游标中的记录
– E. 插入数据到新表中
– F. 关闭游标
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
– 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
declare exit handler for SQLSTATE ‘02000’ close u_cursor;
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(30);

B. 通过SQLSTATE的代码简写方式 NOT FOUND


02 开头的状态码，代码简写为 NOT FOUND

create procedure p12(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
– 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02开头时，将关闭游标u_cursor，并退出
declare exit handler for not found close u_cursor;
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(30);

具体的错误状态码，可以参考官方文档：  
 <https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html>  
 <https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html>


## 三、存储函数


### 1️⃣介绍


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

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

characteristic说明：


* DETERMINISTIC：相同的输入参数总是产生相同的结果
* NO SQL ：不包含 SQL 语句。
* READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。


### 2️⃣案例


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

create function fun1(n int)
returns int deterministic
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(50);

在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定characteristic特性，否则就会报如下错误：


![在这里插入图片描述](https://img-blog.csdnimg.cn/f6053701902449299df3d1780be92aa7.png#pic_center)


## 四、触发器


### 1️⃣介绍


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


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




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


### 2️⃣语法


🍀（1）创建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW – 行级触发器
BEGIN
trigger_stmt ;
END;

🍀（2）查看

SHOW TRIGGERS ;

🍀（3）删除

DROP TRIGGER [schema_name.]trigger_name ; – 如果没有指定 schema_name，默认为当前数
据库 。

### 3️⃣案例


通过触发器记录 tb\_user 表的数据变更日志，将变更日志插入到日志表user\_logs中, 包含增加, 修改 , 删除 ;


表结构准备:

– 准备工作 : 日志表 user_logs
create table user_logs(
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;

**A. 插入数据触发器**

create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
insert into user_logs(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;

测试:

– 查看
show triggers ;
– 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status,
createtime) VALUES (26,‘三皇子’,‘18809091212’,‘erhuangzi@163.com’,‘软件工
程’,23,‘1’,‘1’,now());

测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。


**B. 修改数据触发器**

create trigger tb_user_update_trigger
after update on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, ‘update’, now(), new.id,

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

d;

测试:

– 查看
show triggers ;
– 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status,
createtime) VALUES (26,‘三皇子’,‘18809091212’,‘erhuangzi@163.com’,‘软件工
程’,23,‘1’,‘1’,now());

测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。


**B. 修改数据触发器**

create trigger tb_user_update_trigger
after update on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, ‘update’, now(), new.id,

[外链图片转存中…(img-Z8DuyZ90-1715701917108)]
[外链图片转存中…(img-tjRhjMKC-1715701917109)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！