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 affectedmysql> 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 setmysql>
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 affectedmysql>
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 affectedmysql> SELECT @total_price;+--------------+| @total_price |+--------------+| 149.87 |+--------------+1 row in setmysql>
查询另外一个订单
mysql> CALL ordertotal(20006, @total_price);SELECT @total_price;Query OK, 1 row affected+--------------+| @total_price |+--------------+| 55.00 |+--------------+1 row in setmysql>
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))BEGINSELECT 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 setmysql>