MySQL - 第二十八章 | 儲存程序(中)

使用儲存程序

使用儲存程序需要知道如何執行(運行)它們。儲存程序的執行遠 比其定義更經常遇到，因此，我們將從執行儲存程序開始介紹。然後再介紹建立和使用儲存程序。

執行儲存程序

MySQL稱儲存程序的執行為調用，因此MySQL執行儲存程序的語句為CALL。
CALL接受儲存程序的名字以及需要傳遞給它的任意參數。

1

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

分析：執行名為productpricing的儲存程序，它計算並返回產品的最低、最高和平均價格。

儲存程序可以顯示結果，也可以不顯示結果。

建立儲存程序

返回產品平均價格的儲存程序。以下是其程式碼:

1
2
3
4
5
6
7

CREATE PROCEDURE productpricing ()
BEGIN
	SELECT
		Avg(prod_price) AS priceaverage
		FROM
			products;
END;

分析：儲存程序名為productpricing，用CREATE PROCEDURE productpricing()語句定義。如果儲存程序接受參數，它們將在()中列舉出來。此儲存程序沒有參數，但後跟的()仍然需要。BEGIN和END語句用來限定儲存程序體，過
程體本身僅是一個簡單的SELECT語句(使用之前介紹的Avg()函數)。

在MySQL處理這段程式碼時，它建立一個新的儲存程序product-pricing。沒有返回資料，因為這段程式碼並未調用儲存程序，這裡只是為以後使用而建立它。

說明：mysql命令行客戶機的分隔符 如果你使用的是mysql命令行實用程序，應該仔細閱讀此說明。
默認的MySQL語句分隔符為;。mysql命令行實用程序也使 用;作為語句分隔符。如果命令行實用程序要解釋儲存程序自身內的;字符，則它們最終不會成為儲存程序的成分，這會 儲存程序中的SQL出現句法錯誤。
解決辦法是臨時更改命令行實用程序的語句分隔符，如下所示:

1
2
3
4
5
6
7
8
9
10
11
12

mysql> delimiter //

mysql> CREATE PROCEDURE productpricing ()
    -> BEGIN
    ->   SELECT
    ->   Avg(prod_price) AS priceaverage
    ->   FROM products;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

其中，DELIMITER //告訴命令行實用程序使用//作為新的語句結束分隔符，可以看到標誌儲存程序結束的END定義為END//而不是END;。這樣，儲存程序體內的;仍然保持不動，並且正確地傳遞給資料庫引擎。最後，為恢復為原來的語句分隔符可使用DELIMITER ;。 除\符號外，任何字符都可以用作語句分隔符。

如何使用這個儲存程序?

1
2
3
4
5
6
7

mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+
1 row in set (0.04 sec)

分析：CALL productpricing();執行剛建立的儲存程序並顯示返回的結果。因為儲存程序實際上是一種函數，所以儲存程序名後需要有()符號(即使不傳遞參數也需要)。

刪除儲存程序

儲存程序在建立之後，被保存在服務器上以供使用，直至被刪除。
刪除命令從服務器中刪除儲存程序。

1

DROP PROCEDURE productpricing;

分析：這條語句刪除剛建立的儲存程序。請注意沒有使用後面的()，只給出儲存程序名。

提示：僅當存在時刪除 如果指定的過程不存在，則DROPPROCEDURE將產生一個錯誤。當過程存在想刪除它時(如果過程不存在也不產生錯誤)可使用DROP PROCEDURE IF EXISTS。

結語

簡單的了解了創建、使用和刪除，其實沒什麼特別好說的，要寫到非常複雜的邏輯，才能真真體現SP的好處。

例如：本來需要在程式碼裡面For好幾圈才能產生的資料，在使用SQL語法，創建SP去執行，可能只需要簡單的使用JOIN就可以解決，而且效能之快。

註：以上參考了
MySQL必知必会 MySQL Crash Course
Mysql 24.1 Defining Stored Programs