一、存储过程
版本要求：MySQL5+
存储过程，简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
可将其视为批文件，虽然他们的作用不仅限于批处理。
1. 为什么要使用存储过程
使用存储过程的理由：
-
优点
□ 通过把处理封装在容易使用的单元中，保证数据的完整性。
□ 由于不要求反复简历一系列处理步骤，保证了数据的完整性。如果所有开发人员和应用程序都使用同一存储过程，则使用的代码都是相同的。(减少错误)
□ 简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要修改存储过程的代码。使用它的人员甚至不需要知道这些变化。(安全性)
□ 提高性能。存储过程比单独的SQL语句要快。
□ 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写共鞥更强更灵活的代码。 -
缺点
□ 存储过程比SQL语句复杂得多
□ 并不是所有人都有创建存储过程的安全访问权限。
三、使用存储过程
1. 执行存储过程
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。
CALL接收存储过程的名字以及需要传递给它的任意参数。
CALL 存储过程名(@param1,
@param2,
...
);
CALL productpricing(@pricelow,
@pricehigh,
@priceaveage
);
2. 创建存储过程
- 例如：返回产品平均价格的存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
mysql命令行客户机的分隔符
默认的MySQL分隔符为;(和SQL语句一样)，mysql命令行实用程序也使用，作为语句分隔符。如果命令行实用程序要解释存储过程自身内；字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL语句出现句法错误。
解决办法就是临时更改命令行实用程序的语句分隔符：
DELIMITER // CREATE PRODUCER productpricing() BEGIN SELECT Avg(prod_price) AS priceaverage FROM products; END // DELEMITER ;
DELEMITER // 告诉实用程序实用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END //。这样存储体内的;可以保持不变，正确地传递给引擎，最后恢复为原来的语句分隔符。
3. 删除存储过程
DROP PROCEDURE 存储过程名;
仅当存在时删除，如果指定的不存在，则DROP PROCEDURE将产生一个错误。
DROP PROCUDURE 存储过程名 IF EXISTS;
4. 使用参数
一般，存储过程并不显示结果，而是把结果返回给你指定的变量。
变量：内存中一个特定的位置，用来临时存储数据。
DELIMITER //
CREATE PRODUCER 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 //
DELEMITER ;
此存储过程接收3个参数：pl:最低价格，ph: 最高价格，pa:平均价格。
每个参数必须具有指定的类型，这里使用十进制值。
- OUT：指出相应的参数用来从存储过程穿出一个值(返回给调用者)
- IN：传递给存储过程
- INOUT：对存储过程传入和传出
注意：记录集是不允许的类型，因此不能通过一个参数返回多个行和列。这就是前面的例子为啥要使用三个参数的原因。
- 调用前面的存储过程
CALL productpricing(@pricelow,
@pricehigh,
@priceaveage
);
变量名：所有MySQL的变量都必须以@开始
在调用的时候，这条语句并不显示任何数据。它返回以后可以显示的变量。
- 显示检索出的产品价格
SELECT @priceaverage;
SELECT @pricelow, @pricehigh, @priceaveage;
举例
DELIMITER //
CREATE PRODUCER ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8, 2)
)
BEGIN
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END //
DELEMITER ;
onumber定义为IN，因为订单号被传入存储过程。
ototal定义为OUT，因为要从存储过程中返回合计
- 调用这个新存储过程
CALL orderitems(20005, @total);
- 查看合计
select @total;
四、建立智能存储过程
考虑这个场景：需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些客户。那么，需要做以下几件事儿：
□ 获得合计
□ 把营业税有条件地添加到合计
□ 返回合计(带税或不带税)
-- Name：ordertotal
-- Parameters：onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
DELIMITER //
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8, 2)
) COMMIT “obtain order total, optionally adding tex”
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 it 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：条件语句，还支持ELSEIF， ELSE语句
- DECLARE：定义局部变量
- COMMIT：不是必需的，但是如果给出，将在SHOW PROCEDURE STATUS的结果中显示
- 调用这个存储过程
CALL orderototal(20005, 0, @total);
SELECT @total;
五、检查存储过程
SHOW CREATE PROCEDURE ordertotal;
网友评论