mysql 存储过程复制_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';