mysql 【高级篇】学习笔记--触发器、存储过程、游标，文末有视频链接

mysql 【高级篇】 学习笔记

1、触发器

什么是触发器：

可以监视某个表的某种操作，如insert、delete、update，当表被改变时，可以在表被改变前或改变后，进行某类操作如insert、delete、update；

触发器的四要素：

监视地点：table
监视事件：增、删、改
触发时间：after、before
触发时间：增、删、改

触发器的使用：

-- 创建触发器1（当ord表增加一条数据时，更新goods表相应数据）
delimiter $ -- 设置结束符
create TRIGGER t1
AFTER
INSERT
on ord
for each row
BEGIN
update goods set num=num-new.much where gid=new.gid;-- 更改数据
END$

-- 查看触发器
show TRIGGERs

-- 删除触发器t1
DROP TRIGGER t1

案例：

创建库存表、订单表

-- 库存表
create table goods (
	gid int,
	name VARCHAR(20),
	num int
);
-- 订单表
create table ord(
	oid int,
	gid int,
	much int
);
-- 插入数据
INSERT into goods VALUES (1,'cat',30),(2,'horse',30),(3,'dog',30);

创建几个触发器

-- 触发器1（当ord表增加一条数据时，更新goods表相应数据）
delimiter $ -- 设置结束符
create TRIGGER t1
AFTER
INSERT
on ord
for each row
BEGIN
update goods set num=num-new.much where gid=new.gid;-- 更改数据
END$

-- 触发器2（当ord表删除一条数据时，更新goods表相应数据）
delimiter $ -- 设置结束符
create TRIGGER t2
AFTER
DELETE
on ord
for each row
BEGIN
update goods set num=num+old.much where gid=old.gid;-- 还原数据
END$

-- 触发器3（当ord表更新一条数据时，更新goods表相应数据）
delimiter $ -- 设置结束符
create TRIGGER t3
AFTER
UPDATE
on ord
for each row
BEGIN
update goods set num=num+old.much-new.much where gid=old.gid;-- 先还原数据，再改变更新数据
END$

-- 触发器4（当ord表插入数据超过库存时，限制插入大小，避免爆仓）
delimiter $ -- 设置结束符
create TRIGGER t4
BEFORE -- 此处用before插入前，如果使用after new将无意义
INSERT
on ord
for each row
BEGIN
-- 设置变量
DECLARE rnum int;
-- 变量赋值
SELECT num into rnum FROM goods WHERE gid = new.gid;
-- 判断
if new.much > rnum THEN
	set new.much = rnum;
END IF;
update goods set num=num-new.much where gid=new.gid;-- 改变更新数据
END$

触发器1，插入数据观察两张表的变化

INSERT into ord VALUES (101,1,5);

for each row 代表什么：

在编写触发器的时候，mysql必须要带上 for each row，表示行级触发器
相对的在oracle中有行级触发器，语句级触发器
行级触发器：每改变一行数据都会触发一次
语句级触发器：执行完一个语句只触发一次
oracle中不写 for each row 默认为语句级触发器
mysql中不支持语句级触发器，所以必须要写for each row

---

2、存储过程

2.1、什么是存储过程？

过程：封装了若干条语句，调用时，这些封装体执行
函数：是一个有返回值的过程
过程：没有返回值的函数
存储过程：把若干条sql封装起来，起个名字，并把此过程存储在数据库中。
存储过程创建语法：

create procedure procedureName()
begin
 -- sql语句
end$

查询存储过程

show procedure status

执行存储过程

call procedureName;

删除存储过程

drop procedure procedureName;

2.2、引入变量与控制结构

2.2.1、声明变量

declare 变量名 变量类型 default 默认值;

案例：

delimiter $
create PROCEDURE p2()
begin
	declare age int DEFAULT 18;
	declare heigh int DEFAULT 180;
	SELECT CONCAT('年龄是',age,'身高是',heigh);
end$

结果：

2.2.2、运算赋值、控制结构

运算赋值

delimiter $
create PROCEDURE p3()
begin
	declare age int DEFAULT 18;
	set age := age+20; -- 运算并赋值
	SELECT CONCAT('20年后年龄是',age);
end$

if else控制结构

delimiter $
create PROCEDURE p4()
begin
	declare age int DEFAULT 18;
	-- 条件结构
	if age > 18 then 
	SELECT '你已成年' from dual;
	else
	SELECT '未成年' from dual;
	end if;
end$

给存储过程传参

delimiter $
create procedure p5(width int , height int)
begin
	select concat('你的面积是',width * height) as area;
	if width > height then
	select '你挺胖';
	elseif width < height then
	select '你挺瘦';
	elseif width = height then
	select '你挺方';
	end if;
end$

循环控制结构

while循环

delimiter $
CREATE PROCEDURE p6()
BEGIN
	DECLARE total int DEFAULT 0;
	DECLARE num 	int DEFAULT 0;
	WHILE num < 100 DO
		set num := num+1;
		set total := total+num;
	END WHILE;
	SELECT total;
END;
$

case 结构

delimiter $
create procedure p7(pos int)
begin
	declare pos int default 0;
	case pos
	when 1 then select '你输入了1';
	when 2 then select '你输入了2';
	when 3 then select '你输入了3';
	else select '请输入1~3数字';
	end case;
end;$

repeat 循环

语法：

repeat 
	sql1;
	sql2;
until condition end repeat;

案例：

delimiter $
create procedure p8()
begin
	declare total int default 0;
	declare i int default 0;
	repeat
	set i :=i+1;
	set total := total+i;
	until i>=100 end repeat;
	select total;
end;$

---

3、游标

3.1、什么是游标

1条sql，对应N条结果资源，取出资源的接口/句柄，就是游标，沿着游标，就可以取出一行。
普通的select语句会全部显示查询到的数据，而游标可以控制显示的数据，有了控制权就为编写函数提供方便。

• declare 声明； declare 游标名 cursor for select_语句
• open 打开； open 游标名
• fetch 取值； fetch 游标名 into var1，var2 [,…]
• close 关闭；close 游标名

3.2、游标的使用

delimiter $
create procedure p9()
begin
	-- 声明变量
	declare row_gid int ;
	declare row_num int ;
	declare row_name varchar(20);
	
	declare cnt int default 0;
	declare i int default 0;
	
	-- 声明游标
	declare getgoods CURSOR for SELECT gid,num,name FROM goods;
	
	-- 设置游标长度
	SELECT count(*) into cnt from goods;
	
	-- 打开游标
	open getgoods;
 
	-- 循环输入游标数据
	REPEAT
		SET i := i+1;
	  -- 取出游标数据
		FETCH getgoods into row_gid,row_num,row_name;
		SELECT row_num,row_name;
	UNTIL i >= cnt END REPEAT;
	-- 关闭游标
	close getgoods;

end;$

3.3、continue和exit的区别

上面的案例为了防止游标越界报错，采用了repeat循环，那么游标取值越界时，有没有标识？是否可以利用标识来结束呢？
在mysql cursor中，可以declare continue handler来操作一个越界标识。
declare continue handler for NOT FOUND statement；

delimiter $
create procedure p10()
begin
	declare row_gid int ;
	declare row_num int ;
	declare row_name varchar(20);
	
	declare flag int default 1;-- 设置游标结束标识
	declare getgoods CURSOR for SELECT gid,num,name FROM goods;
	declare continue handler for NOT FOUND set flag =0; -- continue会多打出一行
	-- declare exit handler for NOT FOUND set flag =0;
	open getgoods;
	
	REPEAT
		FETCH getgoods into row_gid,row_num,row_name;
		SELECT row_num,row_name;
	UNTIL flag = 0  END REPEAT;
	
	close getgoods;
end;$

declare exit handler for NOT FOUND set flag =0;
continue：是触发后，后面的语句继续执行
exit：是触发后，后面的语句不在执行
undo：是出发后，前面的语句撤销（但是，目前mysql还不支持undo）

游标循环读取的正确逻辑：

delimiter $
create procedure p12()
begin
	declare row_gid int ;
	declare row_num int ;
	declare row_name varchar(20);
	
	declare flag int default 1;-- 设置游标结束标识
	declare getgoods CURSOR for SELECT gid,num,name FROM goods;
	declare continue handler for NOT FOUND set flag =0; 
	open getgoods;
	-- 先取出一遍数据
	FETCH getgoods into row_gid,row_num,row_name;
	REPEAT
		SELECT row_num,row_name;
		FETCH getgoods into row_gid,row_num,row_name;
	UNTIL flag = 0  END REPEAT;
	
	close getgoods;
end;$

当游标fetch取出数据的时候，如果数据没有就会触发declare continue handler for NOT FOUND ，因此在调整一下语句逻辑顺序，使得逻辑更通畅。

---

END
视频源：【数据库】Mysql高级篇（全24讲）