MySQL 学习笔记（6）— 存储过程创建、调用、删除以及带参数的存储过程

1. 存储过程总结

存储过程是一种存储在数据库中的程序。它可以包含多个 SQL 语句，并提供许多过程语言的功能，例如变量定义、条件控制语句、循环语句、游标以及异常处理等。

1.1 存储过程优点

• 实现代码的重用和管理性。存储过程创建后可以在被重复调用，不同的应用可以共享相同的存储过程；
• 实现业务的封装和隔离。应用程序通过接口访问存储过程，而不关系具体实现；当业务发生变化时，只需要修改存储过程的逻辑，但对应用程序源代码却毫无影响
• 提高应用的执行效率。存储过程经过编译之后存储在数据库中，执行时可以进行缓存，可以提高执行的速度；
• 减少了应用与数据库之间的网络流量。调用存储过程时，只需要传递参数，在一定程度上可以减轻网络负担；
• 存储过程可以提高安全性。应用程序通过存储过程进行数据访问，而不需要直接访问数据表，保证数据的安全。

1.2 存储过程缺点

• 不同数据库的实现不同。Oracle 中称为 PL/SQL，MySQL 中称为 PSM，其他数据库也都有各自的语法实现；
• 存储过程需要占用数据库服务器的资源，包括 CPU、内存等，而数据库的扩展性不如应用程序；
• 存储过程的开发和维护需要专业的技能，存储过程的调试不如其他编程语言方便。

2. 创建存储过程

2.1 默认分隔符

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

解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

DELIMITER  //
....
DELIMITER  ;

其中，DELIMITER // 告诉命令行实用程序使用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的 END 定义为 END // 而不是 END;。这样，存储过程体内的 ; 仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用 DELIMITER ; 。

除 \ 符号外，任何字符都可以用作语句分隔符。

2.2 创建存储过程

mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    -> 	SELECT AVG(prod_price) AS price_average
    ->  FROM products;
    -> END;
Query OK, 0 rows affected

此存储过程名为 productpricing，用 CREATE PROCEDURE productpricing() 语句定义。如果存储过程接受参数，它们将在 () 中列举出来。此存储过程没有参数，但后跟的 () 仍然需要。BEGIN 和 END 语句用来限定存储过程体。

3. 调用过程

SQL 使用 CALL 命令调用存储过程，大部分数据库遵循该标准。

mysql> CALL productpricing();
+---------------+
| price_average |
+---------------+
| 16.133571     |
+---------------+
1 row in set

4. 删除存储过程

使用 DROP PROCEDURE 语句可以从数据库中删除存储过程。存储过程在创建之后，被保存在服务器上以供使用，直至被删除。

mysql> DROP PROCEDURE IF EXISTS productpricing;
Query OK, 0 rows affected

mysql> CALL productpricing();
1305 - PROCEDURE aaa.productpricing does not exist

5. 带参数的存储过程创建和调用

5.1 创建存储过程

productpricing 只是一个简单的存储过程，它简单地显示 SELECT 语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

变量（variable）内存中一个特定的位置，用来临时存储数据。

mysql> CREATE PROCEDURE productpricing(
    -> 		OUT min_price DECIMAL(8,2),
    -> 		OUT max_price DECIMAL(8,2),
    -> 		OUT avg_price DECIMAL(8,2)
    -> )
    -> BEGIN
    -> 		SELECT min(prod_price) INTO min_price FROM products;
    -> 		SELECT max(prod_price) INTO max_price FROM products;
    -> 		SELECT avg(prod_price) INTO avg_price FROM products;
    -> END;
Query OK, 0 rows affected

此存储过程接受 3 个参数：min_price 存储产品最低价格，max_price 存储产品最高价格，avg_price 存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。

关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。

MySQL 支持 IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于 BEGIN 和END 语句内，如前所见，它们是一系列 SELECT 语句，用来检索值，然后保存到相应的变量（通过指 INTO 关键字）。

5.2 调用存储过程

mysql> CALL productpricing(@price_min, @price_max, @price_avg);
Query OK, 1 row affected

由于此存储过程要求 3 个参数，因此必须正好传递 3 个参数，不多也不少。所以，这条 CALL 语句给出 3 个参数。它们是存储过程将保存结果的 3 个变量的名字。

变量名 所有 MySQL 变量都必须以 @开始。

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

5.3 显示存储过程结果

mysql> SELECT @price_min, @price_max, @price_avg;
+------------+------------+------------+
| @price_min | @price_max | @price_avg |
+------------+------------+------------+
| 2.50       | 55.00      | 16.13      |
+------------+------------+------------+
1 row in set

mysql> 

6. 同时携带 IN 和 OUT 参数的存储过程

6.1 创建存储过程

mysql> CREATE PROCEDURE ordertotal(
    -> 		IN  order_number INT,
    -> 		OUT total_price DECIMAL(8,2)
    -> )
    -> BEGIN
    -> 		SELECT sum(item_price * quantity) 
    			FROM orderitems 
    			WHERE order_num = order_number 
    		INTO total_price;
    -> END;
Query OK, 0 rows affected

mysql> 

order_number 定义为 IN，因为订单号被传入存储过程。
total_price 定义为 OUT，因为要从存储过程返回合计。
SELECT 语句使用这两个参数，WHERE 子句使用 order_number 选择正确的行，INTO 使用 total_price 存储计算出来的合计。

6.2 调用存储过程

必须给 ordertotal 传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。

mysql> CALL ordertotal(20005, @total_price);
Query OK, 1 row affected

mysql> SELECT @total_price;
+--------------+
| @total_price |
+--------------+
| 149.87       |
+--------------+
1 row in set

mysql> 

查询另外一个订单

mysql> CALL ordertotal(20006, @total_price);SELECT @total_price;
Query OK, 1 row affected

+--------------+
| @total_price |
+--------------+
| 55.00        |
+--------------+
1 row in set

mysql> 

7. 检查存储过程

为显示用来创建一个存储过程的 CREATE 语句，使用 SHOW CREATE PROCEDURE 语句：

mysql> SHOW CREATE PROCEDURE ORDERTOTAL;
+------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure  | sql_mode                                                       | Create Procedure                                                                                                                                                                                                           | character_set_client | collation_connection | Database Collation |
+------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| ordertotal | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `ordertotal`(

		IN  order_number INT,

		OUT total_price DECIMAL(8,2)

)
BEGIN

		SELECT sum(item_price * quantity) FROM orderitems WHERE order_num = order_number INTO total_price;

END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set

mysql>