有的時候我們的一個 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 裡的資料。