歡迎！您似乎正在使用廣告攔截器。請考慮通過禁用您的廣告攔截器來支持我。

J.J.'s Blogs

Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-20   MySQL   瀏覽次數：次   DMCA.com Protection Status {{moment(1595208600000).fromNow()}}

MySQL - 第二十九章 | 儲存程序(下)

使用參數

productpricing只是一個簡單的儲存程序，它簡單地顯示SELECT語句的結果。一般，儲存程序並不顯示結果，而是把結果返回給你指定的

術語：變數(variable)內存中一個特定的位置，用來臨時存儲資料。

以下是productpricing的修改版本(如果不先刪除此儲存程序，則不能再次創建它):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE PROCEDURE productpricing(OUT pl DECIMAL (8, 2), OUT ph DECIMAL (8, 2), OUT pa DECIMAL (8, 2))
BEGIN
	SELECT
		Min(prod_price) INTO pl
		FROM
			products;
	SELECT
		Max(prod_price) INTO ph
	FROM
		products;
	SELECT
		Avg(prod_price) INTO pa
	FROM
		products;
END

分析：此儲存程序接受3個參數:pl存儲產品最低價格，ph存儲產品
最高價格，pa存儲產品平均價格。每個參數必須具有指定的類型，這裡使用十進制值。關鍵字OUT指出相應的參數用來從儲存程序傳出一個值(返回給調用者)。MySQL支持IN(傳遞給儲存程序)、OUT(從儲存程序傳出，如這裡所用)和INOUT(對儲存程序傳入和傳出)類型的參數。儲存程序的代碼位於BEGIN和END語句內，如前所見，它們是一系列SELECT語句，用來檢索值，然後保存到相應的變數(通過指定INTO關鍵字)。

說明：參數的資料類型 儲存程序的參數允許的資料類型與表中使用的資料類型相同。附錄D列出了這些類型。注意，記錄集不是允許的類型，因此，不能通過一個參數返回多個行和列。這就是前面的例子為什麼要使用3個參數(和3條SELECT語句)的原因。

調用此修改過的儲存程序，必須指定3個變數名，如下所示:

1
CALL productpricing(@pricelow, @pricehigh, @priceaverage);

分析：由於此儲存程序要求3個參數，因此必須正好傳遞3個參數，不多也不少。所以，這條CALL語句給出3個參數。它們是儲存程序將保存結果的3個變數的名字。

說明：變數名 所有MySQL變數都必須以@開始。

在調用時，這條語句並不顯示任何資料。它返回以後可以顯示(或在其他處理中使用)的變數。

為了顯示檢索出的產品平均價格，可如下進行:

1
2
3
4
5
6
7
mysql> SELECT @priceaverage;
+---------------+
| @priceaverage |
+---------------+
|         16.13 |
+---------------+
1 row in set (0.00 sec)

為了獲得3個值，可使用以下語:

1
2
3
4
5
6
7
mysql> SELECT @pricelow, @pricehigh, @priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+
1 row in set (0.00 sec)

下面是另外一個例子，這次使用IN和OUT參數。ordertotal接受訂單號並返回該訂單的合計:

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE ordertotal (IN onnumber INT, OUT ototal DECIMAL (8, 2))
BEGIN
	SELECT
		Sum(item_price * quantity)
		FROM
			orderitems
		WHERE
			order_num = onumber INTO ototal;
END;

分析：onumber定義為IN，因為訂單號被傳入儲存程序。ototal定義為OUT，因為要從儲存程序返回合計。SELECT語句使用這兩個參數，WHERE子句使用onumber選擇正確的行，INTO使用ototal存儲計算出來的合計。

為調用這個新儲存程序，可使用以下語句:

1
CALL ordertotal(20005, @total);

分析：必須給ordertotal傳遞兩個參數;第一個參數為訂單號，第二個參數為包含計算出來的合計的變數名。

顯示此合計，可如下進行:

1
2
3
4
5
6
7
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(20009, @total);
Query OK, 1 row affected (0.00 sec)

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

結語

儲存程序(Stored Procedure)，大多數的情況下都是需要IN和OUT的參數來搭配使用。當然還是有例外的，可能僅僅是來執行例行的事務，例如月報表或是資料清除等等。

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

本文由J.J. Huang 創作，採用CC BY 3.0 TW協議 進行許可。可自由轉載、引用，但需署名作者且註明文章出處。