【MySQL必知必会(二十)】【使用存储过程】

上一篇：【MySQL必知必会(十九)】【使用视图】

+++++++++++++开始线++++++++++++++++

一、 存储过程

需要MySQL5
MySQL5添加了对存储过程的支持，适用于5以后的版本

存储过程

就是为以后的使用而保存的一条或多条MySQL语句的集合，可将其视为批文件，虽然它们的作用不仅限与批处理

二、 为什么使用存储过程

1.通过把处理封装在容易使用的单元中，简化复杂的操作
2.由于不要求反复建立一系列处理步骤，这保证了数据的完整性
3.简化对变动的管理，步骤越多，出错的可能性越大
4.提高性能，使用存储过程比使用单独的SQL语句快
5.存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
6.存储过程的编写比简单SQL语句复杂，编写存储过程需要更高的技能和丰富的经验

三、 使用存储过程

3.1 创建存储过程

一个返回产品平均价格的存储过程

mysql命令行客户机的分隔符

默认的MySQL语句分隔符为;，为了输入以下语句，解决办法是临时更改命令行实用程序的语句分隔符

mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    ->  SELECT Avg(prod_price) AS priceaverage
    ->  FROM products;
    -> END//

3.2 执行存储过程

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

3.3 删除存储过程

仅当存在时删除

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

3.4 使用参数

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

变量

内存中一个特定的位置，用来临时存储数据

mysql> DELIMITER //
mysql> 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指出相应的参数用来从存储过程传出一个值(返回给调用者)。
关键字IN(传递给存储过程)，INOUT(对存储过程传入和传出)，INTO(保存到相应的变量)。

调用productpricing存储过程

mysql> CALL productpricing(@pricelow,@pricehigh,@priceaverage);
    -> //

变量名

所有MySQL变量必须以@开始

检索产品平均价格

使用IN和OUT参数

mysql> 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，从存储过程返回合计。

调用这个新存储过程

mysql> CALL ordertotal(20005, @total);
    -> //

显示合计

3.5 检查存储过程

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

mysql> SHOW CREATE PROCEDURE ordertotal;
    -> //

+++++++++++++结束线++++++++++++++++

下一篇：【MySQL必知必会(二十)】【使用游标】