第二节 使用存储过程

使用存储过程

1. 存储过程

MySQL5之后的版本支持存储过程。

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

2. 为什么要使用

1. 通过把处理封装在容易使用的单元中，简化复杂操作；
2. 通过复用存储过程，防止因为步骤太多出现错误，保证数据的一致性；
3. 简化对变动的管理，表名、列名或者业务逻辑变动，只需要更改存储过程，使用存储过程的人员不需要知道这些变化。
4. 提高性能；
5. 通过使用只能在单个请求中的Mysql元素或特性，可以写出更强大灵活的代码；

简而言之，更简单、安全，高性能

1. 使用存储过程需要更多经验；
2. 需要相应的创建权限；

3. 使用存储过程

3.1 执行存储过程

调用存储过程，CALL语句，CALL接收存储过程的名字以及需要传递给它的任意参数

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

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

3.2 创建存储过程

创建名为productpricing的存储过程，用CREATE PROCEDURE语句，BEGIN和END语句用来限定存储过程体。

3.3 删除存储过程

DROP PROCEDURE productpricing

3.4 使用参数

productpricing只是一个简单的存储过程，简单显示Select语句结果。一般存储过程不显示结果，将结果返回给你指定的变量

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

调用

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

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

SELECT @priceaverage

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
	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 fianlly, save to out variable
	SELECT total INTO ototal;
END;

分析一下以上代码：

1. 增加了注释，在复杂性增加的存储过程中非常重要
2. 在存储过程中定义了两个局部变量，使用DECLARE语句。DECIMAL(8,2)表示一个十进制数，总共有8位，其中2位小数部分。
3. 用了一个IF语句检查taxable是否为真，如果为真则执行SELECT语句，增加营业税到局部变量total
4. 最后执行SELECT语句，将局部变量保存到ototal中。

3.6 检查存储过程

SHOW CREATE PROCEDURE ordertotal;

SHOW语句可以获取创建存储过程的CREATE 语句

SHOW PROCEDURE STATUS LIKE 'ordertotal';

可以获取何时、由谁创建等详细的信息