MySQL高级（四）_sql where pid=new.pid-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_46536962/article/details/114838576

本文介绍了数据库中的触发器和游标技术。触发器是一种特殊存储过程，可以在数据被删除或修改时执行，提供更复杂的数据控制能力。文章详细解释了触发器的创建和使用方法，并通过实例展示了如何利用触发器维护数据完整性。此外，还介绍了游标的基本概念及MySQL中的使用方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

四.触发器与游标

1.触发器简介

触发器是和表关联的特殊的存储过程
删除或修改表中的数据时触发执行，比数据库本身标准的功能有更精细和更复杂的数据控制能力。
优点
1. 安全性：可以基于数据库的值使用户具有操作数据库的某种权利。
2. 审计：可以跟踪用户对数据库的操作；
3. 实现复杂的数据完整性规则。
4. 提供了运行计划任务的另一种方法。

2.创建触发器

监控地点：table；
监控事件：insert/update/delete；
触发时间：after/before；
触发事件：insert/update/delete
trigger_name：触发器的名称；
tirgger_time：触发时机，为 BEFORE 或者 AFTER；
trigger_event：触发事件，为 INSERT、DELETE 或者 UPDATE；
tb_name：表示建立触发器的表名，在哪张表上建立触发器；
trigger_stmt：触发器的程序体，可以是一条 SQL 语句或者是用 BEGIN 和 END 包含的多条语句；

FOR EACH ROW 表示任何一条记录上的操作满足触发事件都会触发该触发器。

1.创建触发器语法

CREATE TRIGGER trigger_name 
trigger_time trigger_event ON tb_name 
FOR EACH ROW 
begin
trigger_stmt
end

2.应用

1.当在orders表添加后修改product表

AFTER -- 关键词（触发条件名称）
INSERT -- 触发条件
on -- 固定语句
orders -- 触发表名
for EACH ROW -- 固定语法
BEGIN-- 用begin end包裹语句
-- 里面可以添加条件等
update product set num=num-3 where pid=1;
end;
INSERT INTO orders VALUES(NULL,1,3);
SELECT * FROM product;

2.删除触发器

drop trigger if exists 触发器名称;

3.新旧记录

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改的新数据；

在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据。

1.使用new来获取值

create TRIGGER mytgl1-- 名称
AFTER -- 在....之后
INSERT-- 触发语句
ON-- 固定语句
orders-- 触发表
for each ROW-- 固定语句
BEGIN
-- 使用new来获取值
update product set num=num-new.num where pid=new.pid;
END;
INSERT into orders values(null,2,5);
SELECT * FROM product;

2.使用old来获取值

create TRIGGER mytgl2
AFTER -- 在....之后
DELETE-- 触发语句   
ON 
orders-- 触发表
FOR EACH ROW
BEGIN
-- 当删除orders表记录时触发还原成原数据
UPDATE product SET num=num+old.num WHERE pid=old.pid;
END;

DELETE FROM orders WHERE oid=1
SELECT * FROM product;

3.before 和 after 的区别

before 在…之后

after 在…之前

之前的并不完善,我们发现在进行修改数据后会出现负数所以我们使用before在修改之前将传入的值修改

-- before在执行触发器前可以对插入数据进行操作
-- after执行触发器后
create TRIGGER mytgl3
before INSERT ON orders -- 创建触发器触发条件前进行num判断
for each ROW
BEGIN
-- 创建容器
DECLARE n int DEFAULT 0;
-- 查询库存 放入
SELECT num INTO n from product where pid=new.pid;
-- 判断该值是否大于库存
if new.num>n THEN
-- 成立时放入
SET new.num =n;
END IF;
-- 修改
UPDATE product SET num=num-new.num where pid=new.pid;
END
INSERT into orders values(NULL,2,60);
select *FROM product
SELECT * FROM orders;

3. 游标

1.游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作

游标是只读的，也就是不能更新它；
游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；
避免在已经打开游标的表上更新数据。

2.MySQL 中游标的使用

定义游标：declare 游标名 cursor for select 语句；
打开游标：open 游标名；
获取结果：fetch 游标名 into 变量名[,变量名]；
关闭游标:close 游标名；

3.应用

基础
基础

create PROCEDURE p6()-- 创建游标
BEGIN
DECLARE pid INT;-- 定义容器
DECLARE pname VARCHAR(255);
DECLARE num INT;
-- 定义游标
DECLARE mc cursor for select * FROM product;
-- 开启游标
OPEN mc;
-- 获取结果
FETCH mc INTO pid,pname,num;
-- 打印
SELECT pid,pname,num;
-- 关闭
CLOSE mc;
END;
call p6();

将其打印到另一表中

缺陷：调用该方法时报错

create PROCEDURE p2() 
BEGIN
declare pid int;
declare pname varchar(20);
declare num int;
-- 声明游标 
declare mc cursor for select * from product;
-- 打开游标 
open mc;
-- 提取结果 
loop 
-- 循环提取数据 
FETCH mc into pid,pname,num;
-- 将提取的每一行数据插入到 product2 表中 
insert into product2 values(pid,pname,num); 
end loop;
close mc;
end;
-- 调用过程 
call p2();
-- 查询 student2 表 
select * from product2;

完善：

-- 循环
create PROCEDURE p2()
BEGIN
DECLARE pid INT;
DECLARE pname VARCHAR(255);
DECLARE num INT;
DECLARE flag INT DEFAULT 0;-- 定义容器
-- 定义游标
DECLARE mc cursor for select * FROM ord;
declare continue handler for not found set flag = 1;
-- 开启
OPEN mc;
-- 获取
 a:LOOP
FETCH mc INTO pid,pname,num;
-- 当无法 fetch 时触发 continue handler
if flag=1 THEN  
leave a; 
end if;
INSERT into ord values(pid,pname,num);
END LOOP;
CLOSE mc;
END;
call p2()
SELECT * FROM ord