Like Share Discussion Bookmark Smile

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

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