使用存储过程

前言：存储过程的好处

1、使用了存过程，很多相似性的删除，更新，新增等操作就变得轻松了，并且以后也便于管理！
2、存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。   
3、存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。     
4、存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。     
5、存储过程主要是在服务器上运行，减少对客户机的压力。   
6、存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。   
7、存储过程可以在单个存储过程中执行一系列SQL语句。   
8、存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

一、创建存储过程

1.基本语法：

create procedure sp_name()
begin
.........
end

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

mysql命令行客户机的分隔符:

 如何临时更改命令行使用程序的语句分隔符，下面举个例子：

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

其中DELIMITER//告诉命令行实用程序使用//作为新的语句结束分隔符，我们看到END//，并不是用的END；最后恢复为原来的语句分隔符还是用DELIMITER；。

2.参数传递

   MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

   CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])

  IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默值；

 OUT 输出参数:该值可在存储过程内部被改变，指出相应的参数用来从存储过程传出的一个值返回给调用者；

 INOUT 输入输出参数:调用时指定，并且可被改变和返回。

下面我们来看两个例子：

首先以使用OUT参数为例：

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8, 2),//十进制数，精度为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个变量名，如下图：

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

为显示检索出的产品的平均价格：

SELECT @priceaverage;

下面再看使用IN和OUT参数：

创建存储过程：

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(2005, @total);
SELECT @total;

由于第一个参数用的IN所以需要传入值，而OUT则表示相应的参数用来从存储过程传出的一个值返回给调用者；

还有就是IN一般与WHERE子句配套使用，而OUT则没有必要。

参数的数据类型：存储过程的参数允许的数据类型与表中的使用的数据类型相同。

二、调用存储过程

1.基本语法：call sp_name()

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

注意：所有的变量名都必须以@开始；

         存储过程名称后面必须加括号，哪怕该存储过程没有参数传递。

三、删除存储过程

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

1.基本语法：
drop procedure sp_name//

DROP PROCEDURE productpricing;

当过程存在想删除它时（如果过程不存在也不产生错误）可以使用：

DROP PROCEDURE IF EXISTS;

2.注意事项：
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程;

(2)请注意没有是使用后面的（），只需要给出存储过程名即可。

四、检查存储过程

        1.显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

 SHOW PROCEDURE STATUS;

        2.显示某一个存储过程的详细信息

SHOW CREATE PROCEDURE sp_name;

        3.限制过程状态结果

 SHOW PROCEDURE STATUS LIKE ‘sp_name';