《MySQL必知必会》--存储过程

存储过程（存储程序）

概念

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

为什么使用存储过程？

  • 通过把处理封装在容易使用的单元中，简化复杂的操作；
  • 由于不要求反复建立一系列处理步骤，这保证了数据的完整性（所有开发人员使用同一代码，减少出错的机率）；
  • 简化对变动的管理（只需在存储过程中修改变动，使用人员甚至可能都不知道已经发生了改变）；
  • 提高性能（使用存储过程比单独使用SQL语句要更快）；
  • 简单而言就是：简单、安全、高性能。

创建存储过程

存储过程有两种形式，一是没有参数的存储过程，一是包含参数的存储过程。具体形式见代码，及其后解读。

无参数存储过程：

1
2
3
4
5
6
7
delimiter //
create procedure product_avg_price()
begin
	select avg(prod_price) as priceaverage
    from products;
end//
delimiter ；

有参数存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
delimiter //
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//
delimiter ；

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 ;
  • 在MySQL WORKBENCH 中使用存储过程需要首先使用DELIMITER将分隔符从; 转换为//，以便在存储过程中存储;；
  • 在存储过程编写完毕后不要忘记将默认分隔符转换为;；
  • 关键字OUT指出相应的参数用来从存储过程传出一个值；MySQL还支持IN（传递给存储过程）和ONOUT（对存储过程传入和传出）类型的参数；
  • 存储过程写在BEGIN与END之间；
  • 无论是否需要使用参数，命名的存储过程后必须有( )，当使用参数时，将参数写入( )中；
  • 每个参数必须指定参数类型，本代码中制定为十进制类型。

使用存储过程

对应上面两类型的存储过程，有两种存储过程使用类型。

1
2
3
4
5
6
7
8
9
10
11
-- 无参数类型：
call product_avg_price();

-- 有参数类型：
call productpricing(@pricelow,
 					@procehigh,
                    @priceaverage);
select @pricelow;
-- 有IN的参数类型
call ordertotal(20005, @ototal);
select @ototal;
  • 没有参数的存储过程直接使用CALL语句加存储过程名就可以；
  • 对于包含OUT参数的存储过程，首先对各自参数命名一个对应的变量名；
  • IN参数代表需要向存储过程输入数据，因此需要先IN对应的输入参数数据和OUT对应的变量名。
  • MySQL系统中所有的变量名必须以@开头。

删除存储过程

1
2
DROP PROCEDURE product_avg_price;
DROP PROCEDURE productpricing IF EXISTS;
  • 删除存储过程不用在存储过程名后加( );
  • 第一条代码是直接删除，如果该存储过程不存在，则系统会报错；
  • 第二条代码是仅当存在删除，如果不存在也不会产生错误。

建立智能存储过程

至今为止使用的所有存储过程基本上都是封装MySQL简单的SELECT语句。虽然有效，但是效率或许甚至不如直接使用这些简单的SELECT语句。

只有在存储过程内包含业务规则和智能处理时，存储过程的威力才能显示出来。

考虑这个场景。你获得和以前一样的订单合计，但是需要对合计增加营业税，不过只针对某些客户。那么，你需要做下面几件事：

  1. 获得合计（与以前一样）；
  2. 把营业税有条件地添加到合计；
  3. 返回合计（带或者不带税）。

存储过程完整工作如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
drop procedure ordertotal;
-- 在之前已经创建该存储过程，需要先删除
DELIMITER //
-- NAME: ordertotal
-- Parameters: onumber = order number
-- 				taxable = 0 if not taxable, 1 if tanable
-- 				ototal = order total variable
-- -----------------------------------------------------
create procedure ordertotal(
	in onumber int,
    in taxable boolean,
    OUT ototal decimal(8,2)
)
comment'Obtain order total, optionally adding tax'
BEGIN
	-- declare variable for total
    DECLARE total decimal(8,2);
    -- declare tax percentage
    declare taxrate INT DEFAULT 6;
    
    -- Get the order total
    SELECT sum(item_price*quantity)
    FROM orderitems
    where order_num = onumber
    INTO total;
    
    -- Is this taxable?
    IF taxable THEN
		select total+(total/100*taxrate) INTO total;
	END IF;
    
		-- AND finally, save to out variable
        SELECT total INTO ototal;
END//
DELIMITER ;
  • 当代码复杂时，增加注释是一个好习惯。
  • 使用DECLARE定义局部变量。其中taxable为布尔值，taxrate为整数型且定义了默认值。
  • IF语句检查taxable是否为真，若为真增加营业税。
  • COMMENT不是必须的，但如果给出将在SHOW PROCEDURE STATUS的结果中显示。

那么该代码如何运行呢？

1
2
3
4
5
CALL ordertotal(20005, 1, @total);
select @total;

call ordertotal(20005, 0, @total);
select @total;

taxable为BOOLEAN（布尔值），指定为0表示假，指定为1表示为真（实际上指定非零值都表示为真）

检查存储过程

1
SHOW CREATE procedure ordertotal;

使用该命令可以查看该存储过程何时、由谁创建等详细信息。

1
SHOW PROCEDURE STATUS;

该代码可以列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：

1
SHOW PROCEDURE STATUS LIKE 'ordertotal';

小结

这是自学习SQL语言后遇到的第一章很难理解的章节，并不像其他章节一样看一遍，然后就可以输入了。

尤其是最后一个例子需要再次好好研究研究。