存储过程

存储过程概念

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译（效率比较高），用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象（针对SQL编程而言）。

 

存储过程：简称过程

与函数的区别

相同点

1、 存储过程和函数目的都是为了可重复地执行操作数据库的sql语句的集合。

2、 存储过程函数都是一次编译，后续执行

不同点

1、标识符不同。函数的标识符为FUNCTION，过程为：PROCEDURE。

2、函数中有返回值，且必须返回，而过程没有返回值。

3、过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，调用时，除在select中，必须将返回值赋给变量。

4、函数可以在select语句中直接使用，而过程不能：函数是使用select调用，过程不是。

 

 

存储过程操作

创建过程

基本语法

Create procedure 过程名字([参数列表])

Begin

过程体

End

结束符

 

如果过程体中只有一条指令，那么可以省略begin和end

 

 

过程基本上也可以完成函数对应的所有功能

 

查看过程

查看过程与查看函数完全一样：除了关键字

 

查看全部存储过程：show procedure status [like ‘pattern’];

 

 

查看过程创建语句：show create procedure 过程名字;

 

调用过程

过程：没有返回值，select不可能调用

 

调用过程有专门的语法：call 过程名([实参列表]);

 

删除过程

基本语法：drop procedure 过程名字;

 

存储过程的形参类型

存储过程也允许提供参数（形参和实参）：存储的参数也和函数一样，需要指定其类型。

 

但是存储过程对参数还有额外的要求：自己的参数分类

In

表示参数从外部传入到里面使用（过程内部使用）：可以是直接数据也可以是保存数据的变量

Out

表示参数是从过程里面把数据保存到变量中，交给外部使用：传入的必须是变量

如果说传入的out变量本身在外部有数据，那么在进入过程之后，第一件事就是被清空，设为NULL

Inout

数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返还给外部。

 

 

参数使用级别语法（形参）

过程类型  变量名  数据类型; //in int_1 int

 

 

分析结果：out类型的数据会被清空，其他正常

 

 

在执行过程之后，再次查看会话变量（外部）

 

 

触发器

触发器概念

基本概念

触发器是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。

 

触发器：trigger，是一种非常接近于js中的事件的知识。提前给某张表的所有记录（行）绑定一段代码，如果改行的操作满足条件（触发），这段提前准备好的代码就会自动执行。

作用

1、可在写入数据表前，强制检验或转换数据。（保证数据安全）

2、触发器发生错误时，异动的结果会被撤销。（如果触发器执行错误，那么前面用户已经执行成功的操作也会被撤销：事务安全）

3、部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。

4、可依照特定的情况，替换异动的指令 (INSTEAD OF)。（mysql不支持）

触发器优缺点

优点

1、触发器可通过数据库中的相关表实现级联更改。（如果某张表的数据改变，可以利用触发器来实现其他表的无痕操作[用户不知道]）

2、保证数据安全：进行安全校验

 

缺点

1、对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

2、造成数据在程序层面不可控。（PHP层）

触发器基本语法

创建触发器

基本语法

Create trigger 触发器名字 触发时机 触发事件 on 表 for each row

Begin

 

End

 

触发对象：on 表 for each row，触发器绑定实质是表中的所有行，因此当每一行发生指定的改变的时候，就会触发触发器。

触发时机

触发时机：每张表中对应的行都会有不同的状态，当SQL指令发生的时候，都会令行中数据发生改变，每一行总会有两种状态：数据操作前和操作后

 

Before：在表中数据发生改变前的状态

After：在表中数据已经发生改变后的状态

触发事件

触发事件：mysql中触发器针对的目标是数据发生改变，对应的操作只有写操作（增删改）

 

Insert：插入操作

Update：更新操作

Delete：删除操作

注意事项

一张表中，每一个触发时机绑定的触发事件对应的触发器类型只能有一个：一张表中只能有一个对应after insert触发器

 

因此，一张表中最多的触发器只能有6个：before insert，before update，before delete，after insert，after update，after delete

 

需求：有两张表，一张是商品表，一张是订单表（保留商品ID），每次订单生成，商品表中对应的库存就应该发生变化。

 

1、 创建两张表：商品表和订单表

 

2、 创建触发器：如果订单表发生数据插入，对应的商品就应该减少库存

Create trigger 名字  after insert on my_orders for each row

 

查看触发器

1、 查看全部触发器

Show triggers;

 

 

2、 查看触发器的创建语句

Show create trigger 触发器名字;

 

 

触发触发器

想办法让触发器执行：让触发器指定的表中，对应的时机发生对应的操作即可。

1、 表为my_orders

2、 在插入之后

3、 插入操作

 

删除触发器

基本语法：drop trigger 触发器名字;

 

 

触发器应用

记录关键字：new、old

触发器针对的是数据表中的每条记录（每行），每行在数据操作前后都有一个对应的状态，触发器在执行之前就将对应的状态获取到了，将没有操作之前的状态（数据）都保存到old关键字中，而操作后的状态都放到new中。

 

在触发器中，可以通过old和new来获取绑定表中对应的记录数据。

基本语法：关键字.字段名

 

Old和new并不是所有触发器都有：

Insert：插入前全为空，没有old

Delete：清空数据，没有new

商品自动扣除库存

 

 

验证结果

 

 

如果库存数量没有商品订单多怎么办？

操作目标：订单表，操作时机：下单前；操作事件：插入

 

 

结果验证