MySQL之存储管理

存储过程

1. 什么是存储过程

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

2. 为什么要使用存储过程

• 将复杂的SQL语句封装在容易使用的单元中，简化复杂的操作。
• 由于没有反复建立一系列处理步骤，这就保证了数据的完整性。不同的开发人员可以使用相同的存储过程，防止了错误的发生。如果没有存储过程进行封装，执行的步骤越多，出错的可能性就越高。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑有变化，只需更改存储过程的代码，使用它的人员甚至不需要知道这些的变化。
• 提高性能。存储过程比使用单独的SQL语句更快

总的来说，使用存储过程有3个有点，即简单、安全、高性能。

当然也有些缺陷，存储过程的编写比基本的SQL语句更加复杂、你可能没有创建存储过程的安全访问权限。

3. 使用存储过程

3.1 执行存储过程

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

例：

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

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

存储过程可以显示结果，也可以不显示结果。

3.2 创建存储过程

例：返回平均价格的存储过程

CREATE PROCEDURE productpriceing()
BEGIN 	
    SELECT Avg(prod_price) AS priceavg
    FROM products;
END;

分析：BEGIN和END语句用来限定存储过程体，此过程体仅是一个简单的select语句。如果接受参数，可以在（）中列举。

如何使用这个存储过程？

输入：

CALL prodcutpricing();

即可以在屏幕上看见平均价格。

分析：执行刚创建的存过程并显示返回结果。因为存储过程实际上是一种函数，所以在存储过程名后面要有（）符号。

3.3 删除存储过程

输入：

DROP PROCEDURE productpricing;

分析：存储过在创建后，直至被删除。注意使用使用后面的（），只给出了存储过程名。

仅当存在时删除。如果指定的存储过程不存在，则会产生一个错误可以使用DROP PROCEDURE IF EXISTS。

3.4 使用参数

一般来说存储过程不显示结果，而是把结果返回给指定的变量。

例：

CREATE PROCEDURE productpricing(
       OUT p1 DECIMAL(8,2),
       OUT ph DECIMAL(8,2),
       OUT pa DECIMAL(8,2)
)
BEGIN 
	SELECT Min(prod_price)
	INTO p1 FROM products;
	SELECT Max(prod_price)
	INTO ph FROM products;
	SELECT Avg(prod_price)
	INTO pa FROM products;
END;

分析：此存储过程接受3个参数。关键字OUT指出相应的参数用来从存储过程传出一个值。MySQL还支持IN（传递给存储过程）、OUT（从存储过程中传出）和INOUT（对存储过程传入传出）类型的参数。

参数和表中的数据类型应相同

调用此存储过程

输入：

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

MySQL中的变量都必须以@开头。

此调用过程没有显示任何数据，可以输入一下内容显示变量的值。

SELECT @pricelow,@pricehigh,@priceavg;

这次使用IN和OUT参数。

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

调用上述存储过程

CALL ordertotal(2000,@total);
SELECT @total;

屏幕上显示订单的合计显示。

3.5 建立智能存储过程

上述例子可以直接使用被封装的语句就能完成。只有在存储过程包含业务逻辑规则和智能处理时才能将存储过程的威力真正发挥。

例如：

CREATE PROCEDURE ordertotal(
            IN onumber INT,
            -- if taxable is ture, add tax
            IN taxable BOOLEAN,
            OUT ototal DECIMAL(8,2)
-- COMMENT is not necessary which will be shown using `SHOW PROCEDURE STATUS`
 )COMMENT 'Obtain order total, optionally adding tax'
 BEGIN
 	-- declare local variables
 	DECLARE total DECIMAL(8,2);
 	DECLARE taxrate INT DEFAULT 6;
 	
 	SELECT Sum(item_price*quantity)
 	FROM orderitems
 	WHERE ordernum =  onumber
 	INTO total;
 	
 	IF taxable THEN 
 	-- increase 6% tax
 		SELECT total+(total/100*taxrate) INTO total;
 	END IF;
 	SELECT total INTO ototal;
 END;

上述是一个高级的存储过程。

可以验证，输入：

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

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

BOOLEN（0为假，其余为真）

3.6 检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用下述例子

SHOW CREATE PROCEDURE ordertotal;

为获得包括何时、由谁创建的等详细信息，可以使用下述例子

SHOW PROCEDURE STATUS [like '...']