sql必知必会学习记录（十八）- 存储过程

sql必知必会学习记录（十八）- 存储过程

一、为什么要使用存储过程

• 通过把处理封装在容易使用的单元中，简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。但是存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

二、存储过程的基础操作

1. 执行存储过程

CALL接受存储过程的名字以及需要传递给它的任意参数。

CALL productpricing(@pricelow,@pricehigh, @priceaverage);调用存储过程
select  @pricelow
变量名所有MySQL变量都必须以@开始。

2. 建立存储过程

CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage FROM products; 
END;
此存储过程名为productpricing，用CREATE PROCEDURE productpricing（）语句定义。BEGIN和END语句用来限定存储过程体


CREATE PROCEDURE productpricing(
OUT pl 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;
关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

3. 更改语句分隔符

DELIMITER // 将语句分隔符改为//

4. 删除 存储过程

DROP PROCEDURE  IF EXISTS processorders;

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

END;

IF语句这个例子给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）。