https://www.flickr.com/photos/8058098@N07/2718571627/
認識了這麼多 SQL 指令，如果我們有經常要執行的工作，一直重複撰寫相同的指令，會相當不便。我們可以設計「預存程序」（Stored Procedure），將多組 SQL 操作封裝起來。如此一來，只要執行呼叫它的指令即可，得到簡化工作的好處。
此篇內容轉載自本人在 iThome 的文章。
一、測試資料
以下是本文使用的測試資料。
員工表，包含編號、名字與薪水，共三個欄位。
銷售表，包含員工編號、銷售日期與金額，共三個欄位。
當我們想知道員工總銷售金額的排行榜，則範例語法如下。
SELECT `e`.`emp_id`, `e`.`name`, SUM(`total_sales`) AS `sum_sales` FROM `sales` AS `s` JOIN `employee` AS `e` ON `s`.`emp_id` = `e`.`emp_id` GROUP BY `e`.`emp_id` ORDER BY `sum_sales` DESC;
查詢結果為：
| emp_id | name | sum_sales |
|---|---|---|
| 206 | 小黃 | 140000 |
| 207 | 小綠 | 111940 |
| 208 | 小黑 | 62800 |
二、建立預存程序
（一）建立與呼叫
基於上述查看銷售排行榜的情境，若要經常查詢，那麼撰寫指令會挺麻煩的。
下面的範例，是將這項操作封裝為預存程序。其實概念和使用其他程式語言時，會另外撰寫方法（method）或函數（function）是一樣的。
上面使用了 CREATE PROCEDURE 語法，建立出叫做「get_employee_sales_ranking」的預存程序。
而 () 括弧符號是撰寫參數的地方，只是這個例子沒有參數。至於 BEGIN 與 END 定義了撰寫 SQL 指令的範圍。
執行預存程序時，使用 CALL 指令即可。
CALL `get_employee_sales_ranking`();
最後要說明比較特別的 DELIMITER 指令。當在 command line 執行指令時，若 MySQL 讀取到分號，將視為指令已經告一段落，會直接執行。然而建立預存程序的指令，要到 END 那一行才算結束。為了避免被誤認為語法錯誤，故使用 DELIMITER 指令，更改結尾符號為「$$」，之後再改回分號「;」。
（二）其他指令
使用 SHOW CREATE PROCEDURE 指令，可查看當初建立預存程序的定義。
SHOW CREATE PROCEDURE `get_employee_sales_ranking`;
使用 DROP PROCEDURE 指令，可刪除預存程序。
DROP PROCEDURE IF EXISTS `get_employee_sales_ranking`;
三、自定義變數
寫 SQL 時，跟平常寫程式一樣，都會用「變數」這個東西來儲存資料。我們需要先知道如何操作變數，才能進一步探索預存程序的更多使用方式。
（一）在預存程序中
以下的預存程序，是查詢 2023 年第 3 季度的員工銷售排行。
上面分別宣告了年度、季度、開始月份與結束月份，共四個變數。
宣告變數的語法為 DECLARE 變數名稱 資料型態 DEFAULT 預設值;。其中資料型態和設計資料表欄位時是一樣的。
接著透過 SET 變數名稱 = 值; 語法，能將值賦予給變數，此處透過季度推算出月份期間。最後將這兩個月份參數用於查詢。
（二）在腳本中
所謂的腳本，是一到多組 SQL 指令的集合。比方說本文第一節建立測試資料的那些指令，可以合稱為一個腳本。
由於在腳本中呼叫預存程序時是可以傳入參數的，因此我們需要了解如何在腳本中使用變數。
在腳本中宣告的方式不太一樣，其語法為 SET @變數名稱 = 值;。變數名稱前會加上 @ 符號，且不必定義資料型態。
SET @name = "Vincent"; SET @weight = 63.9;
我們也能透過查詢語法，將結果中的欄位值賦予給變數。以下的範例是查詢員工資料表，將總人數和總薪資分別賦予給兩個變數。
SELECT COUNT(*), SUM(`salary`) INTO @headcount, @total_salary FROM `employee`;
當想查看變數的值，使用 SELECT @變數名稱 語法即可。
SELECT @headcount, @total_salary;
以上的自定義變數均屬於會話級別（session），只在自身當前對 MySQL 的連線有效。也就是說，當關閉 Workbench 工具，或離開 command line，這些變數會隨之消失。
四、預存程序的參數
使用預存程序時，可以傳入參數，也能回傳結果給呼叫它的地方。
（一）IN 與 OUT 參數
以下的預存程序，是計算某員工在特定年月的傭金（假設訂為銷售金額的 5%）。這裡定義了三個傳入參數，及一個回傳值。
在預存程序名稱後方的括弧中，透過 IN 語法分別定義了員工編號、年度與月份，共三個傳入參數。
接著透過 OUT 語法，定義傭金的回傳參數。事實上，預存程序可定義多個 OUT 參數，意即能有多個回傳值。
在腳本中呼叫預存程序時，除了在 IN 參數的位置傳入值，也會在 OUT 參數的位置寫上要以哪個變數來接收。以下賦予給叫做 result 的自定義變數。
CALL clac_commissions_from_year_month(206, 2023, 1, @result); SELECT @result; -- 3500
當然，該變數亦能在腳本中事先建立好，此時預存程序將會覆寫該變數的值，看起來就像「傳址呼叫」（called by reference）一樣。
SET @result = 0; SELECT @result; -- 0 CALL clac_commissions_from_year_month(206, 2023, 1, @result); SELECT @result; -- 3500
（二）INOUT 參數
除了使用 IN 與 OUT 語法來定義參數，也能選擇 INOUT，使參數同時具有輸入與輸出的性質。
以下的預存程序，是傳入薪水與總銷售額兩個參數，以 5% 銷售額為傭金，計算最終薪水。
其中「salary」參數被設為 INOUT。呼叫預存程序時，在腳本中應事先定義好變數，再傳入該位置。待其被覆寫後，該變數本身又成為回傳值。
SELECT `salary` INTO @total_salary FROM `employee` WHERE `emp_id` = 206; SELECT @total_salary; -- 50000 CALL clac_final_salary(@total_salary, 140000); SELECT @total_salary; -- 57000
從這個情境來看，薪資加上傭金才等於最終薪資。那麼，一開始傳入預存程序的薪資變數，就只是暫時的值而已，並非最後要的。因此筆者認為使用 INOUT 的好處，是能減少參數的宣告。
留言
張貼留言