4.1 视图

4.1.1 介绍

视图 (View) 是一种虚拟存在的表。视图中的数据并不实际存储在数据库中，而是通过定义视图的查询语句动态生成。这意味着视图只保存了查询的 SQL 逻辑，而不保存查询结果。创建视图的关键在于编写正确的 SQL 查询语句。

4.1.2 语法

1. 创建视图

   CREATE [OR REPLACE] VIEW 视图名称 [(列名列表)]
   AS
   SELECT 语句
   [ WITH [CASCADED | LOCAL] CHECK OPTION ];

   • CREATE OR REPLACE VIEW: 用于创建或替换已存在的视图。
   • 视图名称: 指定视图的名称。
   • 列名列表: 可选，用于为视图中的列指定别名。
   • SELECT 语句: 定义视图的查询逻辑。
   • WITH CHECK OPTION: 可选，用于启用检查选项，确保通过视图进行的数据修改符合视图的定义。CASCADED 表示级联检查，LOCAL 表示本地检查。

2. 查询视图

   SHOW CREATE VIEW 视图名称;
   SELECT * FROM 视图名称;

   • SHOW CREATE VIEW: 显示创建视图的 SQL 语句。
   • SELECT * FROM: 查询视图中的数据。

3. 修改视图

   -- 方式一
   CREATE [OR REPLACE] VIEW 视图名称 [(列名列表)]
   AS
   SELECT 语句
   [ WITH [CASCADED | LOCAL] CHECK OPTION ];
   
   -- 方式二
   ALTER VIEW 视图名称 [(列名列表)]
   AS
   SELECT 语句
   [ WITH [CASCADED | LOCAL] CHECK OPTION ];

   • 可以使用 CREATE OR REPLACE VIEW 语句或 ALTER VIEW 语句修改视图的定义。

4. 删除视图

   DROP VIEW [IF EXISTS] 视图名称 [, 视图名称];

   • DROP VIEW: 用于删除视图。
   • IF EXISTS: 可选，防止在视图不存在时报错。

接下来的部分展示了如何使用 SQL 语句创建、查询、修改和删除视图。首先准备数据：

create table student(
id int auto_increment comment '主键ID' primary key,
name varchar(10) null comment '姓名',
no varchar(10) null comment '学号'
)comment '学生表';
  
insert into student (
name, no
)
values
('黛绮丝', '2000100101'),
('谢逊', '2000100102'),
('殷天正', '2000100103'),
('韦一笑', '2000100104');

创建视图

基于 student 表创建初始视图 stu_v_1：

CREATE OR REPLACE VIEW stu_v_1 AS SELECT id, name FROM student WHERE id <= 10;

此视图封装查询逻辑，仅返回 id <= 10 的 id 和 name 列。

查询视图

• 显示视图定义语句

  mysql> show create view stu_v_1;
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  | View    | Create View                                                                                                                                                                                       | character_set_client | collation_connection |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  | stu_v_1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_v_1` AS select `student`.`id` AS `id`,`student`.`name` AS `name` from `student` where (`student`.`id` <= 10) | gbk                  | gbk_chinese_ci       |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  1 row in set (0.00 sec)

• 查询全部视图数据

  mysql> select * from stu_v_1;
  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | 黛绮丝 |
  |  2 | 谢逊   |
  |  3 | 殷天正 |
  |  4 | 韦一笑 |
  +----+--------+
  4 rows in set (0.00 sec)

• 添加额外条件查询

  mysql> select * from stu_v_1 where id < 3;
  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | 黛绮丝 |
  |  2 | 谢逊   |
  +----+--------+
  2 rows in set (0.00 sec)

修改视图

• 方式一：

  CREATE OR REPLACE VIEW stu_v_1 AS SELECT id, name, no FROM student WHERE id <= 10; -- 包含新列 'no'

• 方式二：

  ALTER VIEW stu_v_1 AS SELECT id, name FROM student WHERE id <= 10; -- 还原为仅 'id' 和 'name' 列

两种修改方式功能等价，ALTER VIEW 更精准针对现有结构。视图修改后，所有查询立即体现新逻辑。

通过视图插入数据

• ID=6 的插入结果可见于视图：输出显示五条记录（ID=1-4 加新增 ID=6 'Tom'）。

  mysql> insert into stu_v_1 values (6, 'Tom');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from stu_v_1;
  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | 黛绮丝 |
  |  2 | 谢逊   |
  |  3 | 殷天正 |
  |  4 | 韦一笑 |
  |  6 | Tom    |
  +----+--------+
  5 rows in set (0.00 sec)

• ID=17 的插入在基表中存在（可通过基表查询确认），但因 id > 10 不符合视图条件，在视图查询中不显示。

  mysql> insert into stu_v_1 values (17, 'Tom22');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from stu_v_1;
  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | 黛绮丝 |
  |  2 | 谢逊   |
  |  3 | 殷天正 |
  |  4 | 韦一笑 |
  |  6 | Tom    |
  +----+--------+
  5 rows in set (0.00 sec)

删除视图

清理视图对象：

DROP VIEW IF EXISTS stu_v_1;

4.1.3 检查选项

在 MySQL 中，数据库管理员经常使用视图来抽象和简化数据访问。然而，当通过视图修改数据（如 INSERT、UPDATE 或 DELETE 操作）时，需要确保这些修改不会违反视图定义的条件。为此，MySQL 提供了一个关键子句 WITH CHECK OPTION，它主要用于强制数据修改满足视图的限制条件。

• CASCADED：级联检查，不仅检查当前视图，还会检查所有依赖的视图。 假设视图 v2 是基于另一个视图 v1 创建的。如果 v2 在创建时启用了 CASCADED 选项，但 v1 创建时未声明任何检查选项，则当通过 v2 修改数据时，MySQL 会执行以下检查：
  • 验证 v2 定义的任何过滤条件（如 WHERE 子句）。
  • 级联验证 v1 所有定义的过滤条件，因为 v2 直接依赖 v1，这保证了整个依赖链上的数据一致性。
• LOCAL：本地检查，只检查当前视图。 假设视图 v2 基于视图 v1。如果 v2 创建时启用了 LOCAL 选项，但 v1 创建时未声明检查选项，则通过 v2 修改数据时，MySQL 只执行：
  • 验证 v2 自身的定义条件（例如 v2 的 SELECT 语句中的过滤器）。
  • 不检查依赖视图 v1 的任何规则，除非 v1 自身启用了检查选项，这在 v2 的 LOCAL 选项中不会被触发，从而简化了操作流程。

4.1.4 视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

• 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT() 等）
• DISTINCT
• GROUP BY
• HAVING
• UNION 或者 UNION ALL

例如，以下视图包含聚合函数，因此不可更新：

create view stu_v_count as select count(*) from student;
insert into stu_v_count values (10); -- 报错

4.1.5 视图作用

1. 简化查询

   视图可以简化用户对数据的理解和操作。可以将常用的查询定义为视图，从而避免用户每次都指定全部条件。

2. 安全性

   数据库可以授权给用户访问视图的权限，而不是直接授权访问底层表。这样可以限制用户只能查询和修改他们能够看到的数据。

3. 数据独立性

   视图可以帮助用户屏蔽底层表结构的变化。即使底层表结构发生改变，只要视图的定义保持不变，用户仍然可以通过视图访问数据，而无需修改他们的应用程序。

4.1.6 案例

敏感字段屏蔽

为保证数据库表的安全性，开发人员操作 tb_user 表时需隐藏手机号和邮箱字段。

-- 创建视图（仅暴露非敏感字段）
CREATE VIEW tb_user_view AS 
SELECT 
    id, name, profession, age, gender, status, createtime 
FROM 
    tb_user;

-- 通过视图查询数据
SELECT * FROM tb_user_view;

视图 tb_user_view 筛除 phone 和 email 字段，保留用户 ID、姓名、职业、年龄、性别、状态和创建时间。

多表关联查询简化

频繁查询学生选课信息（涉及 student、student_course、course 三表联查）时，可定义一个视图：

-- 创建视图封装多表连接逻辑
CREATE VIEW tb_stu_course_view AS
SELECT
    s.name AS student_name,  -- 学生姓名
    s.no AS student_no,      -- 学号
    c.name AS course_name    -- 课程名称
FROM
    student s
    INNER JOIN student_course sc ON s.id = sc.studentid
    INNER JOIN course c ON sc.courseid = c.id;

-- 通过视图直接获取结果
SELECT * FROM tb_stu_course_view;

这个视图简化了学生选课信息的查询，避免了每次查询都需要编写复杂的多表连接 SQL 语句。

4.2 存储过程

4.2.1 介绍

存储过程是预先编译并存储在数据库中的 SQL 语句集合。调用存储过程可以简化应用开发人员的很多工作，减少数据库和应用服务器之间的传输，提高数据处理的效率。存储过程本质上是数据库 SQL 语言层面的代码封装与重用。

4.2.2 基本语法

1. 创建存储过程

   CREATE PROCEDURE 存储过程名称 ([参数列表])
   BEGIN
       SQL语句;
   END;

   • CREATE PROCEDURE: 用于创建存储过程。
   • 存储过程名称: 指定存储过程的名称。
   • 参数列表: 可选，指定存储过程的输入和输出参数。
   • BEGIN ... END: 包含存储过程的 SQL 语句。

2. 调用存储过程

   CALL 名称([参数]);

   • CALL: 用于调用存储过程。
   • 名称: 指定要调用的存储过程的名称。
   • 参数: 可选，传递给存储过程的参数值。

3. 查看存储过程

   SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';
   SHOW CREATE PROCEDURE 存储过程名称;

   • INFORMATION_SCHEMA.ROUTINES: 包含关于存储过程的信息的系统表。
   • ROUTINE_SCHEMA: 指定数据库名称。
   • SHOW CREATE PROCEDURE: 显示创建存储过程的 SQL 语句。

4. 删除存储过程

   DROP PROCEDURE [IF EXISTS] 存储过程名称;

   • DROP PROCEDURE: 用于删除存储过程。
   • IF EXISTS: 可选，防止在存储过程不存在时报错。

警告

在命令行中，执行创建存储过程的 SQL 时，需要通过关键字 delimiter 指定 SQL 语句的结束符。防止 MySQL 客户端提前将过程体内的分号错误地当成结束符。

接下来的代码演示了存储过程的基本语法，包括创建、调用、查看和删除存储过程。

-- 创建存储过程
create procedure p1()
begin
    select count(*) from student;
end;

-- 调用存储过程
call p1();

-- 查看存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'hmmysql';
show create procedure p1;

-- 删除存储过程
drop procedure if exists p1;

这段代码创建了一个名为 p1 的存储过程，该过程查询 student 表中的记录数。

4.2.3 变量

在 MySQL 中，变量分为三种类型：系统变量、用户定义变量和局部变量。

类型	前缀	作用域	声明方式
系统变量	无	全局/会话	系统预定义
用户定义变量	@	当前会话	直接赋值
局部变量	无	BEGIN...END 块	DECLARE 声明

4.2.3.1 系统变量

系统变量是 MySQL 服务器提供的，属于服务器层面，分为：

• 全局变量 (GLOBAL)：对所有会话生效
• 会话变量 (SESSION)：仅对当前会话生效

使用方式：

• 查看系统变量

  SHOW [SESSION | GLOBAL] VARIABLES [LIKE 'pattern'];
  SELECT @@[SESSION | GLOBAL] 系统变量名;

  • SHOW VARIABLES: 显示所有系统变量。
  • LIKE 'pattern': 可选，用于模糊匹配变量名。
  • SELECT @@: 查看指定变量的值。

• 设置系统变量

  SET [SESSION | GLOBAL] 系统变量名 = 值;
  SET @@[SESSION | GLOBAL] 系统变量名 = 值;

  • 如果没有指定 SESSION/GLOBAL，默认是 SESSION，即会话变量。
  • MySQL 服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 my.cnf 中配置。
  • 全局变量 (GLOBAL)：全局变量针对于所有的会话。
  • 会话变量 (SESSION)：会话变量针对于单个会话，在另外一个会话窗口失效。

示例

-- 查看系统变量
show session variables;
show session variables like 'auto%';
show global variables like 'auto%';

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

-- 设置系统变量
set session autocommit = 1;
insert into course (id, name) values (6, 'ES');

set global autocommit = 0;
select @@global.autocommit;

以上代码演示了如何查看和设置 MySQL 的系统变量，包括会话变量和全局变量。

相关信息

autocommit 变量用于控制是否自动提交事务。

4.3.2 用户定义变量

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

使用方式如下：

• 赋值

  -- 方式一
  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;

注

用户定义的变量无需对其进行声明或初始化，此时获取到的值为 NULL。

示例

-- 赋值
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;

这段代码演示了如何定义和使用用户变量，包括赋值和查询变量的值。用户变量以 @ 符号开头。

4.3.2.3 局部变量

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

• 声明

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

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

• 赋值

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

示例

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

call p2();

这段代码创建了一个名为 p2 的存储过程，该过程声明了一个局部变量 stu_count，用于存储 student 表中的记录数。

4.2.4 IF

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

IF 条件1 THEN
	...
ELSEIF 条件2 THEN   -- 可选
	...
ELSE                  -- 可选
	...
END IF;

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

示例

根据定义的分数 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();

这段代码创建了一个名为 p3 的存储过程，该过程根据 score 变量的值判断分数等级，并将结果存储在 result 变量中。

4.2.5 参数

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

• IN：该类参数作为输入，也就是需要调用时传入值（默认）。
• OUT：该类参数作为输出，也就是该参数可以作为返回值。
• INOUT：既可以作为输入参数，也可以作为输出参数。

用法：

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

判定等级

根据传入参数 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;

这段代码创建了一个名为 p4 的存储过程，该过程接收一个输入参数 score，并返回一个输出参数 result，表示分数等级。存储过程通过 IF 语句判断分数等级，并将结果存储在 result 参数中。调用存储过程时，需要定义一个用户变量 @result 来接收返回的数据。

分制转换

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

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

set @score = 198;
call p5(@score);
select @score;

在这个例子中，存储过程 p5 接收一个 INOUT 类型的参数 score，表示 200 分制的分数。存储过程将分数转换为百分制，并将结果存储在 score 参数中。

4.2.6 CASE

CASE 结构及作用，和流程控制函数很类似。有两种语法格式：

• 语法 1

  CASE case_value
  	WHEN when_value1 THEN statement_list1
  	[WHEN when_value2 THEN statement_list2] ...
  	[ELSE statement_list]
  END CASE;

  含义：当 case_value 的值为 when_value1 时，执行 statement_list1，当值为 when_value2 时，执行 statement_list2，否则就执行 statement_list。

• 语法 2

  CASE
  	WHEN search_condition1 THEN statement_list1
  	[WHEN search_condition2 THEN statement_list2] ...
  	[ELSE statement_list]
  END CASE;

  含义：当条件 search_condition1 成立时，执行 statement_list1，当条件 search_condition2 成立时，执行 statement_list2，否则就执行 statement_list。

示例

根据传入的月份，判定月份所属的季节（要求采用 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);

这段代码创建了一个名为 p6 的存储过程，该过程接收一个输入参数 month，表示月份。存储过程使用 CASE 结构判断月份所属的季节，并将结果存储在 result 变量中。

注

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

4.2.7 WHILE

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

WHILE 条件 DO
	SQL逻辑...
END WHILE;

先判定条件，如果条件为 true，则执行逻辑，否则，不执行逻辑。

示例

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

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);

这段代码创建了一个名为 p7 的存储过程，该过程接收一个输入参数 n，表示累加的最大值。存储过程使用 WHILE 循环计算从 1 累加到 n 的值，并将结果存储在 total 变量中。

4.2.8 REPEAT

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

REPEAT
	SQL逻辑...
UNTIL 条件
END REPEAT;

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

示例

计算从 1 累加到 n 的值，n 为传入的参数值。(使用 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);
call p8 (100);

这段代码创建了一个名为 p8 的存储过程，该过程接收一个输入参数 n，表示累加的最大值。存储过程使用 REPEAT 循环计算从 1 累加到 n 的值，并将结果存储在 total 变量中。

4.2.9 LOOP

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

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

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

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

上述语法中出现的 begin_label，end_label，label 指的都是自定义的标记。

累加

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

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);

这段代码创建了一个名为 p9 的存储过程，该过程接收一个输入参数 n，表示累加的最大值。存储过程使用 LOOP 循环计算从 1 累加到 n 的值，并将结果存储在 total 变量中。使用 LEAVE 语句在 n <= 0 时退出循环。

偶数累加

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

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);

这段代码创建了一个名为 p10 的存储过程，该过程接收一个输入参数 n，表示累加的最大值。存储过程使用 LOOP 循环计算从 1 到 n 之间的偶数累加的值，并将结果存储在 total 变量中。使用 ITERATE 语句跳过奇数，只累加偶数。

4.2.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 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);

这段代码创建了一个名为 p11 的存储过程，该过程接收一个输入参数 uage，表示用户年龄的最大值。存储过程使用游标循环遍历 tb_user 表中年龄小于等于 uage 的用户，并将用户的姓名和专业插入到 tb_user_pro 表中。

但是，在调用的过程中会报错：

SQL 错误 [1329] [02000]: No data - zero rows fetched, selected, or processed

因为 while 循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行，因此最终仍能得到 tb_user_pro 表。

4.2.11 条件处理程序

条件处理程序 (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 代码的简写。

借助条件处理程序，可修复上一示例中的报错：

示例

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

• 通过 SQLSTATE 指定具体的状态码。

  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 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 p12 (30);

• 通过 SQLSTATE 的代码简写方式 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

4.3 存储函数

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

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

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

示例

计算从 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);

这段代码创建了一个名为 fun1 的存储函数，该函数接收一个输入参数 n，表示累加的最大值。存储函数使用 WHILE 循环计算从 1 累加到 n 的值，并将结果返回。

警告

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

SQL 错误 [1418] [HY000]: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

4.4 触发器

4.4.1 介绍

触发器是与表相关的数据库对象。它是在 INSERT、UPDATE 或 DELETE 操作之前 (BEFORE) 或之后 (AFTER) 触发并执行预定义的 SQL 语句集合。触发器常用于在数据库端确保数据完整性、记录日志和进行数据校验等操作。

触发器可以使用别名 OLD 和 NEW 来引用发生变化的记录内容。目前触发器仅支持行级触发器，不支持语句级触发器。

4.4.2 语法

4.4.2.1 创建触发器

创建触发器的基本语法如下：

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW
BEGIN
  trigger_stmt;
END;

• trigger_name: 触发器的名称。
• BEFORE/AFTER: 触发时机，指明在操作之前或之后触发。
• INSERT/UPDATE/DELETE: 触发事件，指明是插入、更新还是删除操作触发。
• tbl_name: 表名，指定在哪个表上监听触发事件。
• FOR EACH ROW: 指定这是一个行级触发器，即每一行记录都会触发。
• trigger_stmt: 触发器执行的 SQL 语句。

不同类型的触发器对 NEW 和 OLD 的使用有所不同：

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

4.4.3.2 查看触发器

可以使用以下语句查看数据库中的触发器：

SHOW TRIGGERS;

4.4.3.3 删除触发器

可以使用以下语句删除触发器：

DROP TRIGGER [schema_name.]trigger_name;

如果没有指定 schema_name，则默认为当前数据库。

4.4.3 案例

本案例演示如何使用触发器记录 tb_user 表的数据变更日志，并将日志插入到 user_logs 表中，包括新增、修改和删除操作。

首先，需要准备 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;

接下来，分别创建插入、修改和删除数据的触发器。

4.4.3.1 插入数据触发器

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;

以上代码创建了一个名为 tb_user_insert_trigger 的触发器，它在 tb_user 表执行插入操作之后被触发。触发器会将插入的数据信息记录到 user_logs 表中，包括操作类型、操作时间、操作 ID 和操作参数等。NEW.id 表示新插入行的 id 值，NEW.name 表示新插入行的 name 值，以此类推。

创建完成后，可以执行 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());

测试完毕后，检查 user_logs 表中的数据是否正确插入。

4.4.3.2 修改数据触发器

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,
  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;

以上代码创建了一个名为 tb_user_update_trigger 的触发器，它在 tb_user 表执行更新操作之后被触发。触发器会将更新前后的数据信息都记录到 user_logs 表中。OLD.id 表示更新前行的 id 值，NEW.id 表示更新后行的 id 值，以此类推。通过对比 OLD 和 NEW 的值，可以知道哪些数据被修改了。

创建完成后，可以执行 SHOW TRIGGERS; 语句来查看已创建的触发器。

更新 tb_user 表中的数据：

UPDATE tb_user SET profession = '会计' WHERE id = 23;
UPDATE tb_user SET profession = '会计' WHERE id <= 5;

测试完毕后，检查 user_logs 表中的数据是否正确插入。

4.4.3.3 删除数据触发器

CREATE TRIGGER tb_user_delete_trigger
AFTER DELETE ON tb_user FOR EACH ROW
BEGIN
  INSERT INTO user_logs (id, operation, operate_time, operate_id, operate_params)
  VALUES
  (NULL, 'delete', NOW(), OLD.id,
  CONCAT('删除之前的数据: id=',OLD.id,',name=',OLD.name, ', phone=', OLD.phone, ', email=', OLD.email, ', profession=', OLD.profession));
END;

以上代码创建了一个名为 tb_user_delete_trigger 的触发器，它在 tb_user 表执行删除操作之后被触发。触发器会将删除前的数据信息记录到 user_logs 表中。OLD.id 表示被删除行的 id 值，OLD.name 表示被删除行的 name 值，以此类推。

创建完成后，可以执行 SHOW TRIGGERS; 语句来查看已创建的触发器。

删除 tb_user 表中的数据：

DELETE FROM tb_user WHERE id = 26;

测试完毕后，检查 user_logs 表中的数据是否正确插入。