0%

MySQL存储过程

发表于 更新于 分类于
为什么要使用存储过程

存储过程

什么是存储过程

百科上的解释是

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集。存储在数据库中，一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数来执行它。

简单来说，存储过程就是一组SQL语句集合。

为什么要使用存储过程

考虑如下情景：
某个商品抢购系统的数据库中，分别有订单表，库存商品表，用户表。

  • 当一个用户要预定仓库中的某一个商品时，首先要检索库存商品表以保证库存中有相应的商品。
  • 如果存在货物，则在订单表中插入一条新数据，并对商品表相应商品进行减库存的操作。
  • 如果库存中已没有客户抢购的商品，则返回售空信息。
  • 每个用户对同一商品只能抢购一次，以保证公平性。

在实现上述要求时，可以单独编写每条语句，并根据结果条件地执行另外的语句。在每次需要这样的处理流程时，都需要重复这些工作。
在这种情景下，如果单独编写每条SQL语句，这样做是非常低效的。

而使用存储过程，通过把处理封装在容易使用的单元中，简化复杂的操作，并且使用存储过程比使用单独的SQL语句要快，这样可以很好的提高效率。
最重要的一点，在web应用中，网络延迟是引起SQL语句执行效率的重要因素，而使用存储过程可以很好地避开这一点。
这点我们在后面讨论。

存储过程的应用

执行存储过程

执行存储过程使用CALL关键字

1
CALL productpriceavg();

创建存储过程

1
2
3
4
5
6
7
-- 修改MySQL语句分隔符
DELIMITER $$
CREATE PROCEDURE productpriceavg()
BEGIN
    SELECT Avg(prod_price) as priceaverage FROM products;
END $$
DELIMITER ;

通过上面的语句就可以创建存储过程productpriceavg()，该存储过程没有参数，没有返回值，只是显示产品的平均价格。
(可以使用SHOW CREATE PROCEDURE productpriceavg\G)查看存储过程的创建命令。

1
CALL productpriceavg();

执行结果

1
2
3
4
5
|------------|
|priceaverage|
|------------|
|12.3456     |
|------------|

创建带有参数的存储过程

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- in表示输入参数，out表示输出参数
-- 输出参数只能被赋值
-- r_result:0，商品售空，1，抢购成功，-1，已抢购，-2，系统错误
DELIMITER $$
CREATE PROCEDURE execute_sell
    (IN v_goods_id BIGINT,IN v_customer_id BIGINT,IN v_create_time TIMESTAMP,OUT r_result INT)
    BEGIN
        -- 定义变量
        DECLARE insert_count INT DEFAULT 0;
        -- 开启事务，把减库存表与创建订单表放在事务内，如果中间的操作出现的任何问题，回滚，如果操作正确，则提交事务
        START TRANSACTION;
        -- goods_order 订单表
        -- goods_id 与 customer_id做联合主键，保证一个用户对一个商品只能抢购一次
        INSERT IGNORE INTO goods_order (goods_id,customer_id,create_time)
            VALUES(v_goods_id,v_customer_id,v_create_time);
        -- 读取row_count()函数值，存放在insert_count中
        -- row_count()返回上条语句影响的行数：0，未修改数据，>0，修改n条数据,<0，SQL错误
        SELECT row_count() INTO insert_count;
        -- 如果影响条数为0，说明用户已抢购
        IF (insert_count = 0) THEN
            ROLLBACK;
            SET r_result = -1;
        ELSEIF (insert_count < 0) THEN
            ROLLBACK;
            SET r_result = -2;
        ELSE
            -- 如果插入成功，说明用户之前未抢购过此商品，此时可以执行减库存
            UPDATE goods SET goods_number = goods_number - 1
                WHERE goods_id = v_goods_id AND goods_number > 0;
            SELECT row_count() INTO insert_count;

            IF (insert_count == 0) THEN
                -- 商品已买完
                ROLLBACK;
                SET r_result = 0;
            ELSEIF (insert_count < 0) THEN
                ROLLBACK;
                SET r_result = -2;
            ELSE
                -- 操作成功
                COMMIT;
                SET r_result = 1;
            END IF;
        END IF;
    END;
$$

DELIMITER ;
-- SET @result = -3;
-- CALL execute_sell(1003,10005,now(),@result);
-- 获取结果
-- SELECT @result;

删除存储过程

删除存储过程，使用DROP关键字

1
DROP PROCEDURE execute_sell;

如果存储过程不存在，上面语句将会产生一个错误。

1
DROP PROCEDURE IF EXISTS execute_sell;

存储过程真的有用吗

在文章开头，我们说到了存储过程可以提高SQL的执行效率，当然，这是在特定场景下的说法。
当web应用与数据库不在同一台服务器上时，web应用访问数据库时，不仅要考虑SQL语句的效率，还要考虑两台服务器之间的网络延迟。如果在程序中使用事务来实现上面的情景，当我们执行新增订单操作时，要先将sql语句传递到数据库服务器。此时，数据库将会对订单表中的数据加锁，同时将执行结果返回给web应用上，应用通过此结果判断是继续执行操作还是回滚。这一来一回的网络延迟会对应用的执行时间造成很大的压力，同时由于表中数据处于锁状态，其他访问这条数据的请求都会阻塞。

这种情况下，使用存储过程，将事务放在数据库服务器上执行，web应用只需一次性将参数传递给数据库，数据库将存储过程的执行结果返回给web应用，中间只有一次来回，减少了网络延迟的影响，也缩短了数据库行级锁的时间。

总结

存储过程是一系列SQL语句的集合，用于优化SQL语句的执行效率，虽然存储过程有着诸多的优点，但我们也不应该过于依赖它，还是要根据实际情况合理选择。