MySQL存储过程

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

创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
    SELECT AVG(prod_price)AS priceaverage
    FROM products;
end;

• 此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体。
• 如果你使用的是mysql命令行实用程序：

mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    ->     SELECT AVG(prod_price)AS priceaverage
    ->     FROM products;
    -> end //
Query OK, 0 rows affected (0.05 sec)
mysql> DELIMITER ;

• DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END//而不是END;。这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER ;。除符号外，任何字符都可以用作语句分隔符。

执行存储过程

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

mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

• CALL productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要）。

删除存储过程

• 存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令（类似于第21章所介绍的语句）从服务器中删除存储过程。

mysql> DROP PROCEDURE productpricing;
Query OK, 0 rows affected (0.03 sec)

• 这条语句删除刚创建的存储过程。请注意没有使用后面的()，只给出存储过程名。

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

使用参数

DELIMITER //
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 //
DELIMITER ;

CALL ordertotal(20009,@total);
SELECT @total;

• 此存储过程接受2个参数：onumber定义为IN，因为订单号被传入存储过程。ototal定义
  为OUT，因为要从存储过程返回合计。SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计。

• 关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。

• 存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

• 所有MySQL变量都必须以@开始。

建立智能存储过程

DELIMITER //
CREATE PROCEDURE ordertotal1(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total,optionally adding tax'
BEGIN
    DECLARE total DECIMAL(8,2);
    DECLARE taxrate INT DEFAULT 6;
    SELECT SUM(item_price*quantity)
    FROM orderitems
    WHERE order_num=onumber
    INTO total;
    IF taxable THEN
        SELECT total+(total/100*taxrate)INTO total;
    END IF;
    SELECT total INTO ototal;
END //

CALL ordertotal1(20005,0,@total);
SELECT @total;

CALL ordertotal1(20005,1,@total);
SELECT @total;

• 在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。SELECT语句已经改变，因此其结果存储到total（局部变量）而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到ototal。

• COMMENT关键字 本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

检查存储过程

• 为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATEPROCEDURE语句：

SHOW CREATE PROCEDURE ordertotal;

• 为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。

• 限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：

SHOW PROCEDURE STATUS LIKE 'ordertotal';