23.1 存储过程
存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然它们的作用不仅限于批处理。
23.2 为什么要使用存储过程
使用存储过程有3个主要的好处,即简单、安全、高性能。显然,它们都很重要。不过,在将SQL代码转换为存储过程前,也必须知道它的一些缺陷。
一般来说,存储过程的编写比基本SQL语句复杂,编写存储过程需要更高的技能,更丰富的经验。
你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限,允许用户使用存储过程,但不允许他们创建存储过程。
23.3 使用存储过程
23.3.1 执行存储过程
MySQL称存储过程的执行为调用,因此MySQL执行存储过程的语句为CALL。 CALL接受存储过程的名字以及需要传递给它的任意参数。请看以下例子:
分析:其中,执行名为productpricing的存储过程,它计算并返回产品的最低、最高和平均价格。
23.3.2 创建存储过程
一个返回产品平均价格的存储过程。以下是其代码:
CREATE PROCEDURE productpricing( )
BEGIN
SELECT Avg ( prod_price) AS priceaverage FROM products;
END ;
分析:我们稍后介绍第一条和最后一条语句。此存储过程名为productpricing,用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数,它们将在()中列举出来。此存储过程没有参数,但后跟的()仍然需要。 BEGIN和END语句用来限定存储过程体,过程体本身仅是一个简单的SELECT语句。
注意:这里会报错,因为默认的MySQL语句分隔符为
;
解决办法是临时更改命令行实用程序的语句分隔符,如下所示:
最后,为恢复为原来的语句分隔符,可使用DELIMITER
;
。
在MySQL处理这段代码时,它创建一个新的存储过程productpricing。没有返回数据,因为这段代码并未调用存储过程,这里只是为以后使用而创建它。
mysql> CALL productpricing( ) ;
+
| priceaverage |
+
| 16.133571 |
+
分析:CALL productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数,所以存储过程名后需要有()符号(即使不传递参数也需要)。
23.3.3 删除存储过程
为删除刚创建的存储过程,可使用以下语句:
DROP PROCEDURE productpricing;
分析:这条语句删除刚创建的存储过程。请注意没有使用后面的(),只给出存储过程名。
仅当存在时删除
如果指定的过程不存在,则DROP PROCEDURE将产生一个错误。当过程存在想删除它时(如果过程不存在也不产生错误)可使用DROP PROCEDURE IF EXISTS。
23.3.4 使用参数
productpricing只是一个简单的存储过程,它简单地显示SELECT语句的结果。一般,存储过程并不显示结果,而是把结果返回给你指定的变量。
变量(variable)
内存中一个特定的位置,用来临时存储数据。
以下是productpricing的修改版本(如果不先删除此存储过程,则不能再次创建它):
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 ;
分析:此存储过程接受3个参数: pl存储产品最低价格, ph存储产品最高价格, pa存储产品平均价格。每个参数必须具有指定的类型,这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。 MySQL支持IN(传递给存储过程)、 OUT(从存储过程传出,如这里所用)和INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内,如前所见,它们是一系列SELECT语句,用来检索值,然后保存到相应的变量(通过指定INTO关键字)
为调用此修改过的存储过程,必须指定3个变量名,如下所示:
CALL productpricing( @pricelow , @pricehigh , @priceaverage ) ;
分析:由于此存储过程要求3个参数,因此必须正好传递3个参数,不多也不少。所以,这条CALL语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。
变量名
所有MySQL变量都必须以@开始。
为了显示检索出的产品平均价格,可如下进行:
SELECT @priceaverage ;
为了获得3个值,可使用以下语句:
SELECT @pricehigh , @pricelow , @priceaverage ;
下面是另外一个例子,这次使用IN和OUT参数。 ordertotal接受订单号并返回该订单的合计:
CREATE PROCEDURE ordertotal(
IN onumber INT ,
OUT ototal DECIMAL ( 8 , 2 )
)
BEGIN
SELECT Sum ( item_price* quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END ;
分析:onumber定义为IN,因为订单号被传入存储过程。 ototal定义为OUT,因为要从存储过程返回合计。 SELECT语句使用这两个参数, WHERE子句使用onumber选择正确的行, INTO使用ototal存储计算出来的合计。
为调用这个新存储过程,可使用以下语句:
CALL ordertotal( 20005 , @total ) ;
分析:必须给ordertotal传递两个参数;第一个参数为订单号,第二个参数为包含计算出来的合计的变量名。
为了显示此合计,可如下进行:
SELECT @total ;
分析:@total已由ordertotal的CALL语句填写, SELECT显示它包含的值。
为了得到另一个订单的合计显示,需要再次调用存储过程,然后重新显示变量:
CALL ordertotal( 20009 , @total ) ;
SELECT @total ;
23.3.5 建立智能存储过程
只有在存储过程内包含业务规则和智能处理时,它们的威力才真正显现出来。
考虑这个场景。你需要获得与以前一样的订单合计,但需要对合计增加营业税,不过只针对某些顾客(或许是你所在州中那些顾客)。那么,你需要做下面几件事情:
获得合计(与以前一样);
把营业税有条件地添加到合计;
返回合计(带或不带税)。
存储过程的完整工作如下:
CREATE PROCEDURE ordertotal(
IN onumber INT ,
IN taxable BOOLEAN ,
OUT ototal DECIMAL ( 8 , 2 )
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
DECLARE total DECIMAL ( 8 , 2 ) ;
DECLARE taxrate INT DEFAULT 6 ;
SELECT Sum ( item_price* quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
IF taxable THEN
SELECT total+ ( total/ 100 * taxrate) INTO total;
END IF ;
SELECT total INTO
ototal;
END ;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
分析:此存储过程有很大的变动。首先,增加了注释(前面放置–)。在存储过程复杂性增加时,这样做特别重要。添加了另外一个参数taxable,它是一个布尔值(如果要增加税则为真,否则为假)。在存储过程体中,DECLARE语句定义了两个局部变量。 DECLARE要求指定变量名和数据类型,它也支持可选的默认值(这个例子中的taxrate的默认被设置为6%)。 SELECT语句已经改变,因此其结果存储到total(局部变量)而不是ototal。 IF语句检查taxable是否为真,如果为真,则用另一SELECT语句增加营业税到局部变量total。最后,用另一SELECT语句将total(它增加或许不增加营业税)保存到ototal
这显然是一个更高级,功能更强的存储过程。为试验它,请用以下两条语句:
CALL ordertotal( 20005 , 0 , @total ) ;
SELECT @total ;
CALL ordertotal( 20005 , 1 , @total ) ;
SELECT @total ;
分析:BOOLEAN值指定为1表示真,指定为0表示假(实际上,非零值
都考虑为真,只有0被视为假)。通过给中间的参数指定0或1,可以有条件地将营业税加到订单合计上。
23.3.6 检查存储过程
为显示用来创建一个存储过程的CREATE语句,使用SHOW CREATEPROCEDURE语句:
SHOW CREATE PROCEDURE ordertotal;
为了获得包括何时、由谁创建等详细信息的存储过程列表, 使用SHOW PROCEDURE STATUS。
限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出,可使用LIKE指定一个过滤模式,例如:SHOW PROCEDURE STATUS LIKE ‘ordertotal’;