MySQL基础知识_存储过程与触发器
1,存储过程(了解)
MySQL 5.0 版本开始支持存储过程。
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。
优点:
存储过程一旦调试完成后,就可以稳定运行,(前提是,业务需求要相对稳定,没有变化)
存储过程减少业务系统与数据库的交互,降低耦合,数据库交互更加快捷(应用服务器,与数据库服务器不在同一个地区)
缺点:
在互联网行业中,大量使用MySQL,MySQL的存储过程与Oracle的相比较弱,所以较少使用,并且互联网行业需求变化较快也是原因之一。尽量在简单的逻辑中使用,存储过程移植十分困难,数据库集群环境,保证各个库之间存储过程变更一致也十分困难。
阿里的代码规范里也提出了禁止使用存储过程,存储过程维护起来的确麻烦。
存储过程创建
--定义存储过程
--方式1:
DELIMITER $$ -- 声明语句结束符,可以自定义 一般使用$$
CREATE PROCEDURE 过程名称() -- 声明存储过程
BEGIN -- 开始编写存储过程
-- 要执行的操作
END $$ -- 存储过程结束
--调用存储过程
call 存储过程名
-- 编写存储过程, 查询所有商品数据
DELIMITER $$
CREATE PROCEDURE goods_proc()
BEGIN
select * from goods;
END $$
-- 调用存储过程 查询goods表所有数据
call goods_proc;
--方式2:
CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型)
--接收一个商品id, 根据id删除数据
DELIMITER $$
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
DELETE FROM goods WHERE gid = goods_id ;
END $$
# 删除 id为2的商品
CALL goods_proc02(2);
--方式3:
变量赋值
SET @变量名=值
OUT 输出参数:表示存储过程向调用者传出值
OUT 变量名 数据类型
--向订单表 插入一条数据, 返回1,表示插入成功
# 创建存储过程 接收参数插入数据, 并返回受影响的行数
DELIMITER $$
CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT
out_num INT)
BEGIN
-- 执行插入操作
INSERT INTO orders VALUES(o_oid,o_gid,o_price);
-- 设置 num的值为 1
SET @out_num = 1;
-- 返回 out_num的值
SELECT @out_num;
END $$
# 调用存储过程插入数据,获取返回值
CALL orders_proc(1,2,30,@out_num);
测试表与数据
# 商品表
CREATE TABLE goods(
gid INT,
NAME VARCHAR(20),
num INT -- 库存
);
#订单表
CREATE TABLE orders(
oid INT,
gid INT,
price INT -- 订单价格
);
# 向商品表中添加3条数据
INSERT INTO goods VALUES(1,'奶茶',20);
INSERT INTO goods VALUES(2,'绿茶',100);
INSERT INTO goods VALUES(3,'花茶',25);
2,MySQL触发器(了解)
触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete, update)时就会激活它执行。
简单理解: 当我们执行一条sql语句的时候,这条sql语句的执行会自动去触发执行其他的sql语句。
触发器创建的四个要素
- 监视地点(table)
- 监视事件(insert/update/delete)
- 触发时间(before/after)
- 触发事件(insert/update/delete)
触发器创建
delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误
CREATE TRIGGER Trigger_Name -- 触发器名,在一个数据库中触发器名是唯一的
before/after(insert/update/delete) -- 触发的时机 和 监视的事件
on table_Name -- 触发器所在的表
for each row -- 固定写法 叫做行触发器, 每一行受影响,触发事件都执行
begin
-- begin和end之间写触发事件
end
$ -- 结束标记
--需求: 在下订单的时候,对应的商品的库存量要相应的减少,卖出商品之后减少库存量。
-- 1.修改结束标识
DELIMITER $
-- 2.创建触发器
CREATE TRIGGER t1
-- 3.指定触发的时机,和要监听的表
AFTER INSERT ON orders
-- 4.行触发器 固定写法
FOR EACH ROW
-- 4.触发后具体要执行的事件
BEGIN
-- 订单+1 库存-1
UPDATE goods SET num = num -1 WHERE gid = 1;
END$
--向表中插入一条数据
INSERT INTO goods VALUES(1,'book',40);
--向订单表中添加一条数据
INSERT INTO orders VALUES(1,1,25);
--goods表中的数据随之 -1