mysql 存储过程

MySQL5 中添加了存储过程的支持。

    大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单。经常会有一个完整的操作需要多条才能完成

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

    为什么要使用存储过程：优点

        1 通过吧处理封装在容易使用的单元中，简化复杂的操作

        2 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果开发人员和应用程序都使用了同一存储过程，则所使用的代码是相同的。还有就是防止错误，需要执行的步骤越多，出错的可能性越大。防止错误保证了数据的一致性。

        3 简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码了都。

        4 提高性能，因为使用存储过程比使用单条SQL语句要快

        5 存在一些职能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

        换句话说3个主要好处简单、安全、高性能

    缺点

        1 一般来说，存储过程的编写要比基本的SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

        2 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建，允许用户使用存储过程，但不允许创建存储过程

    存储过程是非常有用的，应该尽可能的使用它们

    执行存储过程

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

            CALL productpricing(@pricelow , @pricehigh , @priceaverage);

            //执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

    创建存储过程

        CREATE  PROCEDURE 存储过程名()

           一个例子说明：一个返回产品平均价格的存储过程如下代码：

           CREATE  PROCEDURE  productpricing()

           BEGIN

            SELECT Avg(prod_price)  AS priceaverage

           FROM products;

           END;

        //创建存储过程名为productpricing，如果存储过程需要接受参数，可以在（）中列举出来。即使没有参数后面仍然要跟（）。BEGIN和END语句用来限定存储过程体，过程体本身是个简单的SELECT语句

        在MYSQL处理这段代码时会创建一个新的存储过程productpricing。没有返回数据。因为这段代码时创建而不是使用存储过程。

 

    Mysql命令行客户机的分隔符

        默认的MySQL语句分隔符为分号 ; 。Mysql命令行实用程序也是 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身的 ; 字符，则他们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误

        解决方法是临时更改命令实用程序的语句分隔符

            DELIMITER //    //定义新的语句分隔符为//

            CREATE PROCEDURE productpricing()

            BEGIN

            SELECT Avg(prod_price) AS priceaverage

            FROM products;

            END //

            DELIMITER ;    //改回原来的语句分隔符为 ;

            除\符号外，任何字符都可以作为语句分隔符

        CALL productpricing();  //使用productpricing存储过程

        执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后面要有()符号

    删除存储过程

        DROP PROCEDURE productpricing ;     //删除存储过程后面不需要跟(),只给出存储过程名

        为了删除存储过程不存在时删除产生错误，可以判断仅存储过程存在时删除

        DROP PROCEDURE IF EXISTS

        使用参数

        Productpricing只是一个简单的存储过程，他简单地显示SELECT语句的结果。

        一般存储过程并不显示结果，而是把结果返回给你指定的变量

            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个参数，p1存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须指定类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传给一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程中传出、如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一些列SELECT语句，用来检索值，然后保存到相应的变量（通过INTO关键字）

        调用修改过的存储过程必须指定3个变量名：

        CALL productpricing(@pricelow , @pricehigh , @priceaverage);

        这条CALL语句给出3个参数，它们是存储过程将保存结果的3个变量的名字

    变量名  所有的MySQL变量都必须以@开始

    使用变量

        SELECT @priceaverage ;

        SELECT @pricelow , @pricehigh , @priceaverage ;   //获得3给变量的值

        下面是另一个例子，这次使用IN和OUT参数。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;

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

    为了调用这个新的过程，可以使用下列语句：

        CALL ordertotal(2005 , @total);   //这样查询其他的订单总计可直接改变订单号即可

        SELECT @total;

    建立智能的存储过程

        上面的存储过程基本都是封装MySQL简单的SELECT语句，但存储过程的威力在它包含业务逻辑和智能处理时才显示出来

        例如：你需要和以前一样的订单合计，但需要对合计增加营业税，不活只针对某些顾客（或许是你所在区的顾客）。那么需要做下面的事情：

            1 获得合计（与以前一样）

            2 吧营业税有条件地添加到合计

            3 返回合计（带或不带税）

        存储过程的完整工作如下：

            -- Name: ordertotal

            -- Parameters: onumber = 订单号

            --           taxable = 1为有营业税 0 为没有

            --           ototal = 合计

            CREATE  PROCEDURE ordertotal(

            IN onumber INT,

            IN taxable BOOLEAN,

            OUT ototal DECIMAL(8,2)

            -- COMMENT()中的内容将在SHOW PROCEDURE STATUS ordertotal()中显示，其备注作用

            ) COMMENT 'Obtain order total , optionally adding tax'

            BEGIN

            -- 定义total局部变量

            DECLARE total DECIMAL(8,2)

            DECLARE taxrate INT DEFAULT 6;

 

            -- 获得订单的合计，并将结果存储到局部变量total中

            SELECT Sum(item_price*quantity)

            FROM orderitems

            WHERE order_num = onumber

            INTO total;

 

            -- 判断是否需要增加营业税，如为真，这增加6%的营业税

            IF taxable THEN

            SELECT total+(total/100*taxrate) INTO total;

                  END IF;

            -- 把局部变量total中才合计传给ototal中

            SELECT total INTO ototal;

            END;

            此存储过程有很大的变动，首先，增加了注释（前面放置--）。在存储过程复杂性增加时，这样很重要。在存储体中，用DECLARE语句定义了两个局部变量。DECLARE要求制定变量名和数据类型，它也支持可选的默认值（这个例子中taxrate的默认设置为6%），SELECT 语句已经改变，因此其结果存储到total局部变量中而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total，最后用另一SELECT语句将total（增加了或没有增加的）保存到ototal中。

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

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

    检查存储过程

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

            SHOW CREATE PROCEDURE ordertotal;

        为了获得包括何时、有谁创建等详细信息的存储过程列表。使用SHOW PROCEDURE STATUS.限制过程状态结果，为了限制其输出，可以使用LIKE指定一个过滤模式，例如：SHOW PROCEDURE STATUS LIKE ''ordertotal;