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

📑 目錄

使用參數

使用參數

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

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

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

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變數都必須以@開始。

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

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

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

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

mysql> SELECT @pricelow, @pricehigh, @priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+
1 row in set (0.00 sec)

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

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傳遞兩個參數;第一個參數為訂單號，第二個參數為包含計算出來的合計的變數名。‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌

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

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

為了得到另一個訂單的合計顯示，需要再次調用儲存程序，然後重新顯示變數:

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的參數來搭配使用。當然還是有例外的，可能僅僅是來執行例行的事務，例如月報表或是資料清除等等。‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌‌