Like Share Discussion Bookmark Smile

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

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