存储过程

需要MySQL 5：MySQL 5添加了对存储过程的支持。
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
可将其视为批文件，虽然他们的作用不仅限于批处理。

为什么要使用存储过程

为什么要使用他们呢？下面是一些主要的理由：
1. 通过把处理封装在容易使用的单元中，简化复杂的操作。
2. 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
  如果所有开发人员和应用程序都使用同一（实验和测试）存储过程，则所使用的代码是相同的。
  这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能越大。防止错误保证了数据的一致性。
3. 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
  这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
4. 提高性能。因为使用存储过程比使用单独的SQL语句要快。
5. 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用他们来编写功能更强更灵活的代码（下一章的例子中可以看到）。
使用存储过程有3个主要的好处，即简单、安全、高效性。
不过，在将SQL代码转换为存储过程前，也必须知道她的一些缺陷：
1. 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
2. 你可能没有创建存储过程的安全访问权限。
  许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。
尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能的使用。
不能编写存储过程？你依然可以使用：MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来。
即使不能（或不想）编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程。

使用存储过程

执行存储过程

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

-- 执行名为productpricing的存储过程，计算并返回产品的最低、最高和平均价格
CALL productpricing(@pricelow
                    @pricehigh
                    @priceaverage);

创建存储过程

-- 创建返回产品平均价格的存储过程
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

/*
    此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义；
    如果存储过程接受参数，他们将在()中列举出来，此存储过程没有参数，但后跟的()仍然需要；
    BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。
*/

-- 使用此存储过程
CALL productpricing();

/*
    存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要）。
*/

mysql命令行客户机的分隔符：如果使用的是mysql命令行实用程序，应该仔细阅读以下：
- 默认的MySQL语句分隔符为;。mysql命令行实用程序也使用;作为语句分隔符。
  如果命令行实用程序要解释存储过程自身内的;字符，则他们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。解决办法是临时更改命令行使用程序的语句分隔符，如下所示：

DELIMITER //

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

DELIMITER ;

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

删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。

1 2	-- 删除上边创建的存储过程，注意没有使用()，只给出存储过程名 DROP PROCEDURE prodtuctpricing;

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

使用参数

一般存储过程不显示结果，而是把结果返回给你指定的变量。
以下是productpricing的修改版本（如果不先删除此存储过程，则不能再次创建它）：

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指出相应的参数用来从存储过程传出一个值（返回给调用者）。
*/

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。
参数的数据类型：记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。
变量名：所有MySQL变量都必须以@开始。

-- 为调用此修改过的存储过程，必须指定3个变量名
CALL productpricing(@pricelow
                    @pricehigh
                    @priceaverage);
                   
-- 此条语句不显示任何数据，它返回以后可以显示（或在其他处理中使用的变量）
-- 检索出产品平均价格
SELECT @priceaverage;

-- 获得3个值
SELECT @priceaverage, @pricelow, @pricehigh;

-- ordertotal接受订单号并返回该订单的合计
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;

-- 调用此新的存储过程
CALL ordertotal(20005, @total);

-- 显示合计
SELECT @total;

建立智能存储过程

只有在存储过程内包含业务规则和智能处理时，他们的威力才真正显现出来。

/*
    获得与之前一样的订单合计，但需要对合计增加营业税，不过只针对某些客户，需要做：
        1.获得营业合计（与之前一样）；
        2.把营业税有条件的添加到合计；
        3.返回合计（带税或不带税）。
*/

-- 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 taxble THEN
        -- Yes, so add taxrate to the total
        SELECT total + (total / 100 * taxrate) INTO total;
    END IF;
        
    -- And finally, save to our variable
    SELECT total INTO ototal;
        
END;

/*
    此存储过程有很大变动，增加了注释（前面放置--）。
    添加了另外一个参数taxable，它是一个布尔值（增加税为真，否则为假）。
    在存储过程中，用DECLARE语句定义了两个局部变量。
    IF语句检查taxable是否为真。
    用另一SELECT语句将total（它增加或不增加营业税）保存到ototal。
        
    COMMENT关键字：它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。
*/

-- 试验存储过程
CALL ordertotal(20005, 0, @total);
SELECT @total;

/*
    BOOLEAN值指定为1表示真，指定为0表示假（实际上，非零值都考虑为真，只有0被视为假）。
    此例子给出了MySQL的IF语句的基本用法；
    IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）。
*/