一文读懂存储过程

 

0 导读

经常听别人说，“调用一个存储过程“，“把处理过程改写为存储过程之后就快了”此类的话，本篇文章我们来聊一聊存储过程。将从以下几个方面去描述存储过程。

1 存储过程解决了什么样的问题？

我们看这样一个场景：假设用户现在正在进行下单操作，那你的数据库需要做这些事。

 1）核对保证库存中有对应的商品。

 2）如果有商品，那商品需要预定防止卖给别人，并且要减少可用的商品数量 以反应正确的库存量。

 3）库存中没有的商品需要订购，这需要与供应商进行某种交互。

 4）关于那些物品入库和哪些物品退订，需要通知到对应的客户。

之前我们接触的场景都是单条的SQL语句，现在这种场景也可以使用一条一条的SQL去处理，但是如果一条一条的SQL去处理，每次需要重新写语句，还得保证不写错。那么存储过程这个时候就诞生了。

存储过程简单来说，就是为以后的使用而保存的一条或多条SQL的集合，通过把零散的处理封装到一个单元中，简化复杂的操作。由于不要求反复建立一系列的处理步骤，这里保证了开发人员和应用系统使用的是同样的一段代码，保障了数据的完整性。简化对变动的管理，如果业务或者表名列名改变，只需要去修改存储过程即可，调用者无需知道具体实现。

每个技术的诞生解决了问题，但是也带来了缺点。

2 存储过程的优缺点是什么？

先讲优点：

提高性能：因为存储过程只需要编译一次，而我们单独的SQL语句每次执行前都需要编译，所以存储过程比SQL要快。

使用存储过程写的代码更加的灵活。

再谈一谈缺点：

一般来说，存储过程比单独的SQL要复杂，这就需要有经验的老开发来编写。而且很多时候可能还没有创建存储过程的权限，许多数据库管理员允许调用，但是不准创建，因为维护的成本比较高。

3 应用场景有哪些？

存储过程内部包含业务规则和智能处理时，他的威力才能真正的显示出来。

对查询出来的订单进行加税处理，这时候用存储过程是比较好的处理方式。

总之在数据量大，计算复杂的场景，就可以考虑是否可以用存储过程来解决。

4 存储过程有哪些组成部分？

首先看创建的语句

1 2 3 4 5

create procedure readdata() begin   select AVG(read_count) AS readaverage   from blog; end;

　　

这里得注意如果是MySQL需要重定义分隔符，因为mysql默认结束符是“；”，如果按照上面的语句，MySQL以为到from blog;这里存储过程就结束了，不完整。MySQL中正确的定义如下：

1 2 3 4 5 6 7

delimiter // create procedure readdata() begin   select AVG(read_count) AS readaverage   from blog; end // delimiter ;

　　

这里需要注意delimiter后面是要空一格，否则执行失败。

其次看一下调用，只需使用call即可：

call readdata();

删除存储过程：

drop procedure readdata;

这里只需要给出名字即可删除。

注：存储过程还可以携带参数，这里只是介绍简单的原理，具体使用需要自己去查。

5 底层原理是怎样的？

create之后数据库做了什么？call调用的时候又是怎样找到的？

创建一个存储过程 (procedure) 时，数据库底层会将其编译成一个可执行的二进制代码，以便在需要执行该存储过程时能够直接调用该代码，而无需重新解析 SQL 查询语句。这有助于提高执行速度，降低数据库服务器的负载。

在存储过程被编译时，数据库会执行以下步骤：

1. 语法检查：数据库会检查存储过程的语法是否正确，如果存在语法错误则会报错。
2. 语义分析：数据库会检查存储过程中所引用的表、视图、函数等对象是否存在，并检查参数的数据类型是否正确。
3. 优化：数据库会对存储过程进行优化，以便在执行时能够尽可能地提高执行效率。
4. 生成可执行代码：数据库会将存储过程转换成可执行的二进制代码，并将其存储在系统表中，以便在需要执行该存储过程时能够直接调用。
5. 缓存可执行代码：数据库会将生成的可执行代码缓存到内存中，以便在需要执行该存储过程时能够快速地调用。

当调用存储过程 (procedure) 时，数据库会执行以下步骤：

1. 检查权限：数据库会检查当前用户是否有执行该存储过程的权限，如果没有则会拒绝执行。
2. 加载可执行代码：数据库会从系统表中加载该存储过程的可执行代码，并将其缓存到内存中。
3. 解析参数：如果存储过程有参数，则数据库会解析传入的参数，并将其传递给存储过程。
4. 执行存储过程：数据库会执行存储过程中的代码，并根据代码的逻辑执行相应的操作，如查询、插入、更新或删除数据等。
5. 返回结果：存储过程执行完成后，数据库会将执行结果返回给调用者。

总之，调用存储过程可以让数据库执行预定义的逻辑操作，避免了每次执行一组 SQL 语句的开销。数据库会加载存储过程的可执行代码，并解析传入的参数，执行存储过程中的代码并返回执行结果，从而提高了执行效率和性能。