《MySQL必知必会》第二十二章 使用存储过程

存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

为什么要使用存储过程

使用存储过程的一些主要的理由：

• 通过把处理封装在容易使用的单元中，简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

存储过程的一些缺陷：

• 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
• 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

使用存储过程

存储过程的执行远比其定义更经常遇到。

执行存储过程

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

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

创建存储过程

定义一个返回产品执行价格的存储过程。

CREATE PROCEDUCE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

如果使用的是mysql命令行实用程序，需要临时更改命令行实用程序的语句分隔符，否则会出现句法错误。

DELIMITER //

CREATE PROCEDUCE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END //

DELIMITER ;

执行刚创建的存储过程

CALL productpricing();

删除存储过程

删除刚创建的存储过程：

DROP PROCEDURE productpricing;

使用参数

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 pl
	FROM products;
	SELECT Avg(prod_price)
	INTO pl
	FROM products;
END;

每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。

调用存储过程：

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

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

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

显示检索出的产品平均价格：

SELECT @priceaverage;

显示3个值：

SELECT @pricehigh, @pricelow, @priceaverage;

定义存储过程ordertotal，它接收订单号并返回该订单的合计：

CREATE PROCEDURE ordertotal(
	IN onnumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO ototal;
END;

调用ordertotal

CALL ordertotal(20005, @total);

显示此合计

SELECT @total;

建立智能存储过程

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

创建存储过程ordertotal，它同样是计算订单的合并，但还需要考虑增值税的情况：

-- Name: ordertotal
-- Parameters: onumber = order number
--             texable = 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)
) COMMECT 'Obtain order total, optionally adding tax'
BEGIN
	
	-- Declare variable for total
	DECLARE total DECIMAL(8,2)l
	-- 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 otatal;

END;

执行存储过程，不加增值税：

CALL ordertotal(20005, 0, @total);
SELECT @total;

执行存储过程，加增值税：

CALL ordertotal(20005, 1, @total);
SELECT @total;

检查存储过程

显示创建存储过程的语句：

SHOW CREATE PROCEDURE ordertotal;

获得包含何时、由谁创建等详细信息的存储过程列表：

SHOW PROCEDURE STATUS