数据库操作：存储过程

使用的大多数SQL语句都是针对一个或多个表的单条语句，并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。可以创建存储过程。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合(一组为了完成特定功能的SQL语句集)。

存储过程简单、安全、高性能。

 

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

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

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

 

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

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，将在()中列举出来。BEGIN和END语句用来限定存储过程体，此过程体是一个简单的SELECT语句。

 

MySQL命令行的分隔符

MySQL默认以";"为分隔符，如果没有声明分隔符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”意为把分隔符还原。

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

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

删除存储过程：DROP PROCEDURE productpricing;    注意后面没有()

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

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

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;

此存储过程接受三个参数：pl--最低价格，ph--最高价格，pa--平均价格。

关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。

IN：传递给存储过程； OUT：从存储过程传出；INOUT：对存储过程传入和传出。

调用存储过程：CALL productpricing(@pricelow,@pricehigh,@priceaverage);

变量以@开始。

在调用时，这条语句不显示任何数据。它返回以后可以显示的变量。

为了显示检索出的产品平均价格：SELECT @priceaverage;

为了获得三个值：SELECT @pricehigh,@pricelow,@priceaverage;

 

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，因为要从存储过程返回合计，INTO使用ototal存储计算出来的合计。

调用存储过程：CALL ordertotal(2005，@total);

必须给ordertotal传递两个参数：第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。

SELECT @total;

@total已由ordertotal的CALL语句填写，SELECT显示它包含的值。

 

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

SHOW CREATE PROCEDURE ordertotal;

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

SHOW PROCEDURE STATUS列出所有存储过程，为限制其输出，可使用LIKE指定一个过滤模式。

SHOW PROCEDURE STATUS LIKE 'ordertotal';