[Day20] MySQL 的 Stored Procedure

有的时候我们的一个 request 可能会需要执行好几个工作来完成，例如之前举例的，购买一个商品必须先验证使用者、接着确认商品数量、建立订单资讯、写入 log，还可能有其他必须的步骤。如果资料库跟 API 程序放在同一台机器还好，假如因为系统架构需求，资料库与 API 程序在不同的机器、必须用网路沟通，这些步骤来来回回就会造成很成的等待时间，像这种情况，我们如果使用 Stored Procedure 就能大幅减少 API 需要等待的时间。

Stored Procedure （SP, 预存程序）是一组预先写好的 SQL 脚本，可以一次执行多个指令，还可以在里面做流程控制，而且这些指令可以重新被使用。使用 SP 有几个主要的优点：

1. 减少网路传输的时间
2. 将相关的工作集中，方便管理、修改
3. SP 会被编译（compile），效能会更好一些

以前第一份工作的时候，有一个很强的主管常常会唠叨的跟我说，叫我用 SP，现在回想起他的碎念还是觉得颇有趣 XD。

建立 Stored Procedure

建立 SP 也需要执行 MySQL 的指令，可以从 MySQL 的命令列输入，但是有连线的权限的话，当然笔者更推荐用 MySQL Workbench 编写，因为 SP 通常会有很多行指令，用 MySQL 命令列个人觉得会有点卡。

建立 SP 的语法结构如下

DELIMITER //

CREATE PROCEDURE 预存程序名称 (
	IN或OUT  参数1 资料型态1,
	IN或OUT  参数2 资料型态2
)
BEGIN
    各种 SQL 指令
END//

DELIMITER ;

第一个 DELIMITER 把预设的分隔符号从分号（;）改成两个斜线（//），这个指令在使用 mysql 命令列建立 SP 时非常重要，因为没有改变分隔符号，mysql 命令列看到分号就会停止我们的输入并开始执行，如此一来就无法完整的建立 SP。而最後一个 DELIMITER 则是把分隔符号把两个斜线再次改回分号。这里的分隔符号其实可以自订，最常见的是两个斜线或两个钱号（$$）

接着就是帮我们的 SP 设定参数，参数可以是 IN 或 OUT，我们必须在呼叫 SP 的时候把参数一并带入。OUT 参数在执行完 SP 後会被储存再变数，我们可以透过 SLECT 指令将结果取出。

SP 范例

首先，我们来模拟上面的需求，额外新增商品与订单的资料表

CREATE TABLE `product` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `product_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `price` decimal(10,0) NOT NULL,
  `stock` int NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `user_order` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `product_id` int NOT NULL,
  `order_status` varchar(45) NOT NULL,
  `create_datetime` datetime NOT NULL,
  `update_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

塞入一些测试资料之後，建立一个 SP，接收使用者 ID、商品 ID 与 商品数量当作 IN 参数，外加一个输出结果的 OUT 参数。如果使用者已经通过认证、而且商品数量足够，就帮使用者建立一笔订单资料、并写入一笔 LOG，最後输出 "OK" 的讯息。否则的话，救回传错误代码

CREATE DEFINER=`root`@`localhost` PROCEDURE `BuyProduct`(
	IN  in_user_id INT,
	IN  in_product_id INT,
	IN  in_buy_count INT,
	OUT out_result INT
)
BEGIN
    -- 取得使用者验证状态
	SET @user_verified := -1;
    
    SELECT verified 
    INTO @user_verified
    FROM `user` 
    WHERE user_id = in_user_id;
    
    IF @user_verified = -1 THEN
		SET out_result = -1; -- 无效的使用者 ID
	ELSEIF @user_verified = 0 THEN 
		SET out_result = -2; -- 使用者未通过验证
	ELSEIF @user_verified = 1 THEN 
		-- 检查库存数量
        SET @stock := -1;
        
		SELECT stock 
        INTO @stock
        FROM product 
        WHERE product_id = in_product_id;
        
        IF @stock = -1 THEN
			SET out_result = -3; -- 无效的商品 ID
		ELSEIF @stock < in_buy_count THEN
			SET out_result = -4; -- 商品库存不足
		ELSE
			-- 建立订单
			INSERT INTO `user_order`
			(
				`user_id`,
				`product_id`,
				`order_status`,
				`create_datetime`
            )
			VALUES
			(
				in_user_id,
				in_product_id,
				'NEW ORDER',
				NOW()
            );

			-- 写入 LOG
            INSERT INTO `user_log`
			(
				`user_id`,
				`action`,
				`action_datetime`
            )
			VALUES
			(
				in_user_id,
				'CREATE ORDER',
				NOW()
            );
            SET out_result = 1; -- OK
		END IF;
    END IF;
END

上面的 SP 里有一些之前没提过的用法：

• 变数
  可以在 SP 中宣告并使用变数（本项目的延伸阅读是内容农场翻译简体文章而来，但笔者觉得内容还不错）
• SELECT INTO
  用 SELECT 指令把值存到暂存变数中，然後让後续的指令使用
• IF ELSE
  在 SP 里做流程控制

呼叫 SP

建立完 SP 後我们就能使用 CALL 预存程序名称() 来呼叫这个 SP，比较要注意的是这边带给 SP 的参数的顺序跟资料形态要对，还有最後一个参数是 "OUT" 参数，必须先宣告一个变数，然後让 SP 把结果存到这个变数

SET @test := 0; -- 宣告储存结果的变数
CALL BuyProduct('1', 1, 10, @test); -- 呼叫 SP
SELECT @test; -- 把结果取出来

执行上面的脚本就能看到我们的 SP 帮我们建立订单并写入 log 了！

回传一个 table

因为上面的需求是执行一串指令然後回传一个结果，所以笔者选择使用 OUT 参数。如果要让 SP 回传多笔资料（就像一个资料表），则可以在 SP 结束之前，执行一个 SELECT ... FROM ... WHERE ... 的指令，这样 SP 就会在执行结束後，把我们所需的资料都捞出来并回传。

明天，我们将回到 .NET API，使用 Dapper 来操作 MySQL 里的资料。