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>