MySql 游标 触发器

游标

1.什么是游标

MySQL游标是一种数据库对象，它用于在数据库查询过程中迭代访问结果集中的每一行。游标可以被看作是一个指向查询结果集的指针，通过移动游标，可以按行读取和处理结果集的数据。在MySQL中，游标可以用于在存储过程或函数中处理复杂的业务逻辑，例如逐行处理查询结果、循环操作数据等。使用游标可以让我们更加灵活地处理结果集。

2.使用游标的步骤

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。如果我们想要使用游标，一般需要经历四个步骤。不同的 DBMS 中，使用游标的语法可能略有不同

2.1 声明游标

使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;
1

要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集。比如:

DECLARE cur_score CURSOR FOR
	SELECT stu_id,grade FROM score;
1
2

2.2 打开游标

OPEN 游标名称
-- 例如
open cur_score;
1
2
3

2.3 使用游标

这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。
注意：var_name必须在声明游标之前就定义好.

FETCH cursor_name INTO var_name [, var_name] ...

FETCH cur_score INTO stu_id, grade ;
1
2
3

注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误

2.4 关闭游标

CLOSE 游标名称;

CLOSE cur_score;
1
2
3

3.案例

创建一个存储过程，实现累加考试成绩最高的几个学员的总分，直到总和大于我们传入的limit_total_grade的参数值，并且返回累加的人数:total_count;

CREATE PROCEDURE PROC_CURSOR(IN LIMIT_TOTAL_GRADE INT, OUT TOTAL_COUNT INT )
BEGIN
# 声明相关的变量
DECLARE SUM_GRADE INT DEFAULT 0; # 累加的总成绩
DECLARE CURSOR_GRADE INT DEFAULT 0; # 记录某条成绩
DECLARE SCORE_COUNT INT DEFAULT 0; # 记录累加的记录数
# 定义游标
DECLARE SCORE_CURSOR CURSOR FOR SELECT GRADE FROM SCORE ORDER BY GRADE ;
# 打开游标
OPEN SCORE_CURSOR;
# 使用游标
REPEAT
FETCH SCORE_CURSOR INTO CURSOR_GRADE; # 从游标中获取一条数据
SET SUM_GRADE = SUM_GRADE + CURSOR_GRADE; # 成绩累加
SET SCORE_COUNT = SCORE_COUNT + 1; # 记录累加的次数
UNTIL SUM_GRADE > LIMIT_TOTAL_GRADE # 退出条件
END REPEAT ;
# 复制OUT参数
SET TOTAL_COUNT = SCORE_COUNT;
# 关闭游标
CLOSE SCORE_CURSOR;
END;

DROP PROCEDURE PROC_CURSOR

# 调用存储过程
SET @s_count = 0;
CALL PROC_CURSOR(400,@s_count) ;
SELECT @s_count;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

触发器

1.触发器概述

MySQL触发器是MySQL数据库中的一种特殊对象，它允许在表中插入、更新或删除数据时自动执行一系列指定的操作。触发器可以在特定的数据库操作（例如INSERT、UPDATE、DELETE）发生时被触发。MySQL触发器可以用于实现各种自动化任务和业务逻辑。它们可以执行诸如数据验证、审计记录、数据同步等操作。通过触发器，可以在数据库层面上处理数据相关的逻辑，避免了在应用程序中手动编写重复的代码。

2.触发器创建

2.1 语法结构

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
1
2
3
4

说明：

• 表名 ：表示触发器监控的对象。
• BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
  INSERT|UPDATE|DELETE ：表示触发的事件。
  INSERT 表示插入记录时触发；
  UPDATE 表示更新记录时触发；
  DELETE 表示删除记录时触发。
• 触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

2.2 代码案例

创建案例表

CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30) );

CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);
1
2
3
4
5
6
7
8

创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息。

CREATE TRIGGER BEFORE_INSERT
BEFORE INSERT ON TEST_TRIGGER
FOR EACH ROW
BEGIN
INSERT INTO TEST_TRIGGER_LOG(T_LOG)VALUES('BEFORE_INSERT ....') ;
END;
1
2
3
4
5
6

向test_trigger中插入对应的记录

insert into test_trigger(t_note)values('test data');
1

查看test_trigger_log中是否有记录

select * from test_trigger_log;
1

3.查看和删除

3.1 查看触发器

• 方式1：查看当前数据库的所有触发器的定义

  SHOW TRIGGERS\G
  1

• 方式2：查看当前数据库中某个触发器的定义

  SHOW CREATE TRIGGER 触发器名
  1

• 方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息

  SELECT * FROM information_schema.TRIGGERS;
  1

3.2 删除触发器

DROP TRIGGER IF EXISTS 触发器名称;
1