存储过程

存储过程

当一个完整的操作需要多条语句才能完成，如

• 为处理订单，需要核对以保证库存中有相应的物品
• 如果库存里有物品，则将物品预订以边不将其卖给他人，并且要减少可用的物品数量以反映正确的库存量
• 库存中没有的物品需要订购，与供货商进行交互
• 关于哪些物品入库并可以立即发货的，和那些物品退订，要通知相应的客户

处理多表多MySQL语句

• 单独编写每条语句，并根据结果有条件地执行另外的语句。在每次需要这个处理时都要重复做
• 创建存储过程，即为之后的使用而保存的一条或多条语句的集合。可以视为批文件，但作用不仅限于批处理

存储过程的优势

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

存储过程的缺陷

• 存储过程的编写比基本SQL语句复杂，编写存储过程需要更高技能，更丰富经验
• 创建存储过程权限限制，未必有权限

存储过程使用

执行存储过程

调用存储过程CALL，接收存储过程名字已经所需传参 

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

创建存储过程

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

创建存储过程： 

CREATE PROCEDURE productpricing()
BEGIN
　　SELECT Avg(prod_price) AS priceaverage
　　FROM products;
END
#存储过程名productpricing，若存储过程要接收参数，在()中列举
#BEGIN ENG 限定存储过程体，过程体本身只是简单的SELECT语句

当MySQL处理这段代码将创建一个新的存储过程productpricing。没有调用存储过 程，故没有返回数据。

使用存储过程：

CALL productpricing()

删除存储过程

DROP PROCEDURE productpricing;

注意删除存储过程时，没有使用后面的()，只用给出存储过程名。

仅当存在时删除：

如果指定的过程不存在，删除会报错，可以用

DROP PROCEDURE IF EXISTS productpricing;

传递参数

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

变量variable：用来临时存储数据

--例1、创建存储过程： 

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 指出相应的参数用来从存储过程传出一个值返回给调用者
• IN(传递给存储过程)，OUT(从存储过程传出)，INOUT(对存储过程传入和传出)
• 通过指定INTO将检索结果保存到相应变量
• 不能通过一个参数返回多个行和列

--例1、调用存储过程：

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

• 存储过程要求3个参数，则必须传参3个。
• 所有MySQL变量都必须@开始

--例1、显示检索结果：  

SELECT @priceaverage;
############
SELECT @pricelow,@pricehigh,@priceaverage;

----------------------------------------------------------------------------

--例2、创建存储过程： 

# 接收订单号并返回该订单的合计
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，从存储过程返回合计

INTO使用otoal存储计算出来的合计

--例2、调用存储过程： 

CALL ordertotal(20005,@total);

--例2、显示检索结果： 

SELECT @total

建立智能存储过程

发挥存储过程真正的实例，在存储过程中包含业务规则和智能处理，如：

• 获得订单的合计
• 把营业税有条件的添加到合计
• 返回合计

‐‐ 方法名：ordertotal
‐‐ 规则：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
　　　　‐‐ 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定义了两个局部变量并指定变量名和数据类型

在CREATE PROCEDURE语句中包含一个comment值，即将在SHOW PROCEDURE STATUS的结果中显示

调用 

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

检查存储过程

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

SHOW CREATE PROCEDURE ordertotal;

 

为获取何时由谁创建的详细信息的存储过程列表使用：

SHOW PROCEDURE STATUS LIKE 'ordertotal';