MySQL基础之使用存储过程

存储过程

在数据库中经常会有一个完整的操作需要多条语句才能完成, 那么可以使用存储过程

存储过程简单来说, 就是为以后的使用而保存的一条或多条MySQL语句的集合。可视为批文件, 虽然他们的作用不仅限于批处理。

为什么要使用存储过程

使用存储过程的优点:

通过把处理封装在容易处理的单元中, 简化复杂的操作
由于不要求反复建立一系列处理步骤, 这保证了数据的完整性
简化对变动的管理
提高性能
存在一些只能用在单个请求中的MySQL元素和特性, 存储过程可以使用他们来编写功能更强更灵活的代码

使用存储过程的缺点:

一般来说, 存储过程的编写比基本SQL语句复杂, 编写存储过程需要更高的技能, 更丰富的经验
许多数据库管理员限制存储过程的创建和权限, 允许用户使用存储过程, 但不允许他们创建存储过程

使用存储过程

执行存储过程

MySQL称存储过程的执行为调用, 因此MySQL执行存储过程的语句为CALL。CALL接收存储过程的名字以及需要传递给它的参数。

MariaDB [crashcourse]> CALL productpricing(@pricelow, @pricehigh, @priceaverage);

创建存储过程

MariaDB [crashcourse]> DELIMITER $
MariaDB [crashcourse]> CREATE PROCEDURE productpricing()
    -> BEGIN
    -> SELECT Avg(prod_price) AS priceaverage FROM products;
    -> END $
Query OK, 0 rows affected (0.002 sec)

MariaDB [crashcourse]>

默认的MySQL语句分隔符为; 如果命令行实用程序要解释存储过程自身内的;字符, 则它们最终不会成为存储过程的成分。这会使存储过程的SQL出现句法错误。

解决办法是临时更改命令行实用程序的语句分隔符, 如下:
MariaDB [crashcourse]> DELIMITER $

使用这个存储过程的代码如下

MariaDB [crashcourse]> CALL productpricing();

执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数, 所以存储过程名后需要有()符号(即使不传参数也需要)

删除存储过程

存储过程在创建之后, 被保存在服务器上以供使用, 直至被删除。

MariaDB [crashcourse]> DROP PROCEDURE productpricing;
Query OK, 0 rows affected (0.005 sec)

注意: 这条语句后面没有使用(), 只给出了存储过程名

如果指定的过程不存在, 则DROP PROCEDURE将产生一个错误, 当过程存在想删除它时可使用DROP PROCEDURE IF EXISTS

使用参数

一般, 存储过程并不显示结果, 而是把结果返回给指定的变量

MariaDB [crashcourse]> DELIMITER $
MariaDB [crashcourse]> CREATE PROCEDURE productpricing(OUT pl DECIMAL(8,2) OUT ph DECIMAL(8, 2), OUT pa DECIMAL(8, 2))
    -> BEGIN
    -> SELECT Min(prod_price) INTO pl FROM products;
    -> SELECT Max(prod_price) INTO ph FROM products;
    -> SELECT Avg(prod_price) INTO pa FROM products;
    -> END $ 

Query OK, 0 rows affected (0.002 sec) MariaDB [crashcourse]>

关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型的参数。

为调用此存储过程, 必须传递三个变量名

MariaDB [crashcourse]> CALL productpricing(@pricelow, @pricehigh, @priceaverage)

在调用时, 这条语句并不显示任何数据。它返回以后可以显示(或在其他处理中使用)的变量。

MariaDB [crashcourse]> SELECT @priceaverage;

检查存储过程

为显示用来创建一个存储过程的CRATE语句, 可以使用SHOW CREATE PROCEDURE语句

MariaDB [crashcourse]> SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、由谁创建等详细信息的存储过程列表, 使用SHOW PROCEDURE STATUS;