第二十三章-使用存储过程

23.1存储过程

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

23.2 为什么要使用存储过程

使用存储过程有3个主要的好处，即简单、安全、高性能。

数据库可能允许用户使用存储过程，但不一定允许他们创建存储过程。

23.3使用存储过程

23.3.1执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给他的任意参数

CALL productpricing(
    @pricelow,
    @pricehigh,
    @priceaverage;
)

分析

其中，执行名为productpricing的存储过程，他计算并返回产品的最低、最高和平均价格

23.3.2创建存储过程

输入

# 返回产品平均价格的存储过程
create procedure productpricing()
begin
	select avg(prod_price) as priceaverage from products;
end; 

分析

此储存过程名为productpricing,用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，他们将在()中列举出来。此存储过程没有参数但后跟的括号仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句

那么，如何使用这个存储过程

输入

CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+

分析

CALL productpricing();执行刚创建的存储国产过程并显示返回的结果，因为存储过程实际上是一种函数，所以存储过程名后需要有()符号

23.3.3删除存储过程

DROP PROCEDURE productprincing；

分析：

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

23.3.4使用参数

输入

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语句内

调用存储过程

CALL productpricing(
    @pricelow,
    @pricehigh,
    @priceaverage
);

分析

由于存储过程要求3个参数，因此必须正好传递三个参数，在调用时，这条语句并不显示任何数据。这条CALL语句给出3个参数。它们是存储过 程将保存结果的3个变量的名字。

为了显示检索的产品平均价格，如下

SELECT @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存储计算 出来的合计。

调用这个存储过程：

# 给ordertotal传递两个参数；
# 第一个参数为订单号，第二个参数为包含计算出来的合计的变量名
call ordertotal(20005,@total);

显示此合计：

select @total;

23.3.5建立智能存储过程

只有在存储过程内包含业务规则和智能处理时， 存储过程的威力才真正显现出来。

例：

考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计 增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么， 你需要做下面几件事情：

• 获得合计（与以前一样）；
• 把营业税有条件地添加到合计；
• 返回合计（带或不带税）。

# 获得与以前一样的订单合计，但只针对某些顾客对合计增加营业税
-- Name:ordertotal
-- Parameters: onumber = order number
-- 			   taxable = 0 if not taxable, 1 if taxable
-- 			   ototal  = order total variable

create procedure ordertotal(
	in onumber int,
	in taxable boolean,
    out ototal decimal(8,2)
) comment 'obtain order total, optionally adding tax'
begin
	-- declare variable for total 定义局部变量total
    declare total decimal(8,2);
    -- declare tax percentage 定义局部变量税率 
    declare taxrate int default 6;
    -- get the order total 获得订单合计
	SELECT SUM(item_price * quantity)
	FROM orderitems
	WHERE order_num = onumber INTO total;
	-- is this taxable? 是否要增加营业税？ 
	if taxable then
		-- Yes,so add taxrate to the total 给订单合计增加税率
		select total+(total/100*taxrate) into total;
	end if;
	-- and finally,save to out variable 最后，传递给输出变量 
	SELECT total INTO ototal;
END 

分析：

此存储过程有很大的变动。首先，增加了注释（前面放置–）。

在存储过程复杂性增加时，这样做特别重要。添加了另外一个 参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。

在 存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定 变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默 认被设置为6%）。

SELECT语句已经改变，因此其结果存储到total（局部 变量）而不是ototal。

IF语句检查taxable是否为真，如果为真，则用另 一SELECT语句增加营业税到局部变量total。

最后，用另一SELECT语句将 total（它增加或许不增加营业税）保存到ototal。

COMMENT关键字本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

IF语句这个例子给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）。在以后章节中我们将会看到IF的其他用法（以及其他流控制语句）。

验证上面的存储过程：

# 调用上述存储过程，不加税 
call ordertotal(20005,0,@total);
select @total;
# 调用上述存储过程，加税 
call ordertotal(20005,1,@total);
select @total;

23.3.6检查存储过程

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

# 显示用来创建一个存储过程的CREATE语句
show create procedure ordertotal;

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

# 获得包括何时、由谁创建等详细信息的存储过程列表
# 该语句列出所有存储过程
show procedure status;
# 过滤模式 
show procedure status like 'ordertotal';

23.4小结

本章介绍了什么是存储过程以及为什么要使用存储过程。我们介绍 了存储过程的执行和创建的语法以及使用存储过程的一些方法。