数据库Mysql高级——触发器和游标

文章目录

• 一、触发器简介？
• 二、MySQL 中使用触发器

---

以下是本篇文章正文内容

一、触发器简介？

触发器是和表关联的特殊的存储过程，可以在插入，删除或修改表中的数据时触发执行，比数据库本身标准的功能有更精细和更复杂的数据控制能力。

1.触发器优点

1）安全性
2）审计
3）实现复杂的数据完整性规则
4）提供了运行计划任务的另一种方法

二、MySQL 中使用触发器

1.触发器的四要素

监控地点：table
监控事件：insert/update/delete
触发时间：after/before
触发事件：insert/update/delete

2.创建触发器的基本语法

CREATE TRIGGER trigger_name trigger_time trigger_event ON
 tb_name FOR EACH ROW trigger_stmt
 --------------------------------------------------------
trigger_name：触发器的名称
tirgger_time：触发时机，为 BEFORE 或者 AFTER
trigger_event：触发事件，为 INSERT、DELETE 或者 UPDATE
tb_name：表示建立触发器的表名，在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条 SQL 语句或者是用 BEGIN 和 END 包含的多条语句
FOR EACH ROW 表示任何一条记录上的操作满足触发事件都会触发该触发器
注意：对同一个表相同触发时间的相同触发事件，只能定义一个触发器

3.案例

如你正在开发一个网上购物的应用，使用的数据库（shop）中有一张商品表（product）和订单表（orders），表结构及测试数据。代码如下：

CREATE TABLE `orders` (
`oid` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`pid` int(11) NOT NULL COMMENT '商品编号',
`num` int(11) NOT NULL COMMENT '购买数量',
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `product` (
`pid` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品编号',
`pname` varchar(255) NOT NULL COMMENT '商品名称',
`num` int(11) NOT NULL COMMENT '库存',
PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `product` VALUES ('1', 'p30', '10');
INSERT INTO `product` VALUES ('2', 'mate20', '50');
INSERT INTO `product` VALUES ('3', 'nova5', '100');

1）当用户购买 p30 时，同时更新 p30 库存记录

create trigger mytg1
before insert on orders
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;-- 1）让购买的数量等于库存
end if;
update product set num = num-new.num where pid=new.pid;-- 更新库存
end;
-- 往订单表插入记录
insert into orders value(null,3,200);
drop trigger mytg1;

当用户删除订单时，同时更新对应的商品库存记录

create trigger mytg2
after delete on orders
for each ROW
begin 
  update product set num=num+old.num where pid=old.pid;
end;
delete from orders where oid=3; 

查询数据

select * from orders;
select * from product;

三.游标

1、游标简介

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

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

2、MySQL 中游标的使用

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

-- 定义过程
create PROCEDURE p1()
BEGIN
declare id int;
declare name varchar(20);
declare age int;
-- 声明游标
declare mc cursor for select * from student;
-- 打开游标
open mc;
-- 提取结果
FETCH mc into id,name,age;
-- 显示获取的结果
select id,name,age;
close mc;
end;
-- 调用过程
call p1();

以上就是今天要讲的内容