Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-21   MySQL   瀏覽次數:

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