MySQL - 第三十章 | 儲存程序(完)

建立智能儲存程序

迄今為止使用的所有儲存程序基本上都是封裝MySQL簡單的SELECT語句。雖然它們全都是有效的儲存程序例子，但它們所能完成的工作你直接用這些被封裝的語句就能完成(如果說它們還能帶來更多的東西，那就是使事情更複雜)。只有在儲存程序內包含業務規則和智能處理時，它們的威力才真正顯現出來。

考慮這個場景。你需要獲得與以前一樣的訂單合計，但需要對合計 增加營業稅，不過只針對某些顧客(或許是你所在州中那些顧客)。那麼，你需要做下面幾件事情:

• 獲得合計(與以前一樣);
• 把營業稅有條件地添加到合計;
• 返回合計(帶或不帶稅)。

儲存程序的完整工作如下:​‌​​‌‌​‌​‌‌‌‌​​‌​‌​‌​​‌‌​‌​‌​​​‌​‌​​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​​​​‌‌​​​​​​‌‌​‌‌‌​​‌‌​​‌​​​‌‌​​​‌​​‌​‌‌​‌​‌‌​‌‌​‌​‌‌‌‌​​‌​‌‌‌​​‌‌​‌‌‌​​​‌​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌‌​​‌‌​​​​

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

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             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
		-- Yes, so add taxrate to the total
		SELECT total + (total / 100 * taxrate) INTO total;
		END IF;
	-- And finally, save to out variable
	SELECT total INTO ototal;
END;

分析：此儲存程序有很大的變動。首先，增加了注釋(前面放置–)。在儲存程序複雜性增加時，這樣做特別重要。添加了另外一個參數taxable，它是一個布林值(如果要增加稅則為真，否則為假)。在儲存程序體中，用DECLARE語句定義了兩個局部變數。DECLARE要求指定變數名和資料類型，它也支持可選的默認值(這個例子中的taxrate的默認被設置為6%)。SELECT語句已經改變，因此其結果存儲到total(局部變數)而不是ototal。IF語句檢查taxable是否為真，如果為真，則用另一SELECT語句增加營業稅到局部變數total。最後，用另一SELECT語句將
total(它增加或許不增加營業稅)保存到ototal。

提示：COMMENT關鍵字 本例子中的儲存程序在CREATEPROCEDURE語句中包含了一個COMMENT值。它不是必需的，但如果給出，將在SHOW PROCEDURE STATUS的結果中顯示。

這顯然是一個更高級，功能更強的儲存程序。為試驗它，請用以下兩條語句:​‌​​‌‌​‌​‌‌‌‌​​‌​‌​‌​​‌‌​‌​‌​​​‌​‌​​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​​​​‌‌​​​​​​‌‌​‌‌‌​​‌‌​​‌​​​‌‌​​​‌​​‌​‌‌​‌​‌‌​‌‌​‌​‌‌‌‌​​‌​‌‌‌​​‌‌​‌‌‌​​​‌​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌‌​​‌‌​​​​

1
2
3
4
5
6
7
8
9
10

mysql> CALL ordertotal(20005, 0, @total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

mysql> CALL ordertotal(20005, 1, @total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)

分析：BOOLEAN值指定為1表示真，指定為0表示假(實際上，非零值都考慮為真，只有0被視為假)。通過給中間的參數指定0或1，可以有條件地將營業稅加到訂單合計上。

說明：IF語句 這個例子給出了MySQL的IF語句的基本用法。IF語句還支持ELSEIF和ELSE子句(前者還使用THEN子句，後者不使用)。在以後章節中我們將會看到IF的其他用法(以及其他流控制語句)。

檢查儲存程序

為顯示用來建立一個儲存程序的CREATE語句，使用SHOW CREATE PROCEDURE語句:​‌​​‌‌​‌​‌‌‌‌​​‌​‌​‌​​‌‌​‌​‌​​​‌​‌​​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​​​​‌‌​​​​​​‌‌​‌‌‌​​‌‌​​‌​​​‌‌​​​‌​​‌​‌‌​‌​‌‌​‌‌​‌​‌‌‌‌​​‌​‌‌‌​​‌‌​‌‌‌​​​‌​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌‌​​‌‌​​​​

1

SHOW CREATE PROCEDURE ordertotal;

為了獲得包括何時、由誰建立等詳細訊息的儲存程序列表，使用SHOW PROCEDURE STATUS。

說明：限制過程狀態結果 SHOWPROCEDURESTATUS列出所有儲存程序。為限制其輸出，可使用LIKE指定一個過濾模式，例如:

1

SHOW PROCEDURE STATUS LIKE 'ordertotal';

結語

發現文章將儲存程序寫成一篇來做介紹，我覺得篇幅太大了，吸收會有點不良，所以把它拆成四篇來做學習。​‌​​‌‌​‌​‌‌‌‌​​‌​‌​‌​​‌‌​‌​‌​​​‌​‌​​‌‌​​​​‌​‌‌​‌​​‌‌​​‌​​​‌‌​​​​​​‌‌​​‌​​​‌‌​​​​​​‌‌​​​​​​‌‌​‌‌‌​​‌‌​​‌​​​‌‌​​​‌​​‌​‌‌​‌​‌‌​‌‌​‌​‌‌‌‌​​‌​‌‌‌​​‌‌​‌‌‌​​​‌​‌‌​‌‌​​​​‌​‌‌​‌​​‌‌​​‌‌​​‌‌​​​​

學習到這邊基本上對儲存程序有一定的了解，而後面將會對儲存程序更深入的介紹並使用。

註：以上參考了
MySQL必知必会 MySQL Crash Course