MySQL存储过程

MySQL存储过程

1.定义

为以后的使用而保存的一条或多条MySQL语句的集合。

2.优点

（1）简单：通过把处理封装在容易使用的单元中，简化复杂的操作。
（2）安全：由于不要求反复建立一系列处理步骤，这保证了数据的完整性；简化对变动的管理。
（3）高性能：使用存储过程比使用单独的SQL语句要快。

3.缺点

（1）复杂：一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
（2）没有权限：你可能没有创建存储过程的安全访问权限。许多数据库管理员限 制存储过程的创建权限，允许用户使用存储过程，但不允许他们 创建存储过程。

4.创建存储过程

基本语句：

CREATE PROCEDURE 存储过程名字()
BEGIN
	***
END;

例子：

--用//代替;做结尾
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;
--将;换回;
DELIMITER ;

原因：默认的MySQL语句分隔符为;(正如你已经在迄今为止所使用 的MySQL语句中所看到的那样)。mysql命令行实用程序也使 用;作为语句分隔符。如果命令行实用程序要解释存储过程自 身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符。

5.调用存储过程

基本语句：

CALL 存储过程名();

例子：

CALL productpring();

6.删除存储过程

基本语句：

DROP PROCEDURE 存储过程名;

例子：

DROP productpring IF EXITS;

注意：没有后面的()

7.使用参数

存储过程不显示结果，而是把结果返回你指定的变量。
变量：内存中一个特定的位置，用来临时存储数据。
基本语句：

CREATE PROCEDURE 存储过程名(
	OUT 变量名 变量类型,
	IN 变量名 变量类型
)
BEGIN
	 SELECT ***
	 INTO 变量名
	 FROM ***
END;

注意：MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型的参数。
例子：

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;

调用：

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

MySQL变量都必须以@开始。
显示检索出的结果：

SELECT @priceaverage;

8.一个完整的存储过程例子

delimiter //
create procedure ordertotal(
	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//
delimiter ;

call ordertotal(20005,1,@total);

select @total;

分析：添加了一个参数taxable，它是一个布尔值(如果要增加税则为真，否则为假)。在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定 变量名和数据类型，它也支持可选的默认值(这个例子中的taxrate的默 认被设置为6%)。SELECT语句已经改变，因此其结果存储到total(局部变量)而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将 total(它增加或许不增加营业税)保存到ototal。
COMMENT关键字：本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。
结果：

BOOLEAN值指定为1表示真，指定为0表示假(实际上，非零值都考虑为真，只有0被视为假)。通过给中间的参数指定0或1，可以有条件地将营业税加到订单合计上。

9.检查存储过程

基本语句：SHOW CREATE PROCEDURE 存储过程名;
例子：

SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。
SHOW PROCEDURE STATUS列出所有存储过 程。为限制其输出，可使用LIKE指定一个过滤模式，例如:

SHOW PROCEDURE STATUS LIKE 'ordertotal';

参考资料：《MySQL必知必会》23章