MySQL PROCEDURE

儲存過程（PROCEDURE）是儲存在伺服器中的一組指定的 SQL 語句，客戶機無需不斷重新發出單個語句，而可以引用儲存過程。儲存過程型別分為儲存過程 (PROCEDURE) 和儲存函式 (FUNCTION) 。

Ø   儲存過程：透過 CALL 語句呼叫過程。這些過程可以使用輸出變數或結果集傳回值。

Ø   儲存函式：在語句中呼叫函式。這些函式可返回標量值。

 

l   使用儲存過程的好處：

1)          客戶機功能

透過儲存過程，您可以在資料庫中集中建立一個語句或一系列語句，以供使用不同程式語言編寫或在不同平臺上執行的多個客戶機應用程式使用。

2)          安全性

儲存過程為需要最高安全級別的應用程式提供了一個解決方法。例如，銀行針對所有常用操作均使用儲存過程和函式。這提供了一致、安全的環境。可對儲存過程進行編碼，以確保正確記錄了每個操作。在此類設定中，應用程式和使用者無法直接訪問資料庫表，只能執行特定的儲存過程。

3)          效能

因為伺服器和客戶機之間需要傳送的資訊變少了，所以儲存過程可提升效能。客戶機按名稱呼叫儲存過程，而不是傳遞該儲存過程所包含的所有語句。

4)          函式庫

透過儲存過程，可以在資料庫伺服器中使用函式庫。這些庫用作資料庫的 API 。

 

l   使用儲存過程的問題：

1)          增加了伺服器負載

在資料庫自身中執行儲存過程可增加伺服器負載並降低應用程式的效能。可以執行測試並運用常識來確保在資料庫本身中包含邏輯所帶來的方便比可能引發的效能問題更為顯著。

2)          開發工具有限

MySQL 中支援儲存過程的開發工具不像在更通用的程式語言中那樣成熟和明確。此侷限性會使儲存過程的編寫和除錯過程更加困難，在決策過程中需要加以考慮。

3)          語言功能和速度有限

雖然在許多情況下在資料庫本身中包含邏輯具有很大的優勢，但是與其他程式語言相比，在可實現的內容方面仍有侷限。儲存過程在資料庫上下文中執行，與客戶機應用程式中的儲存過程相比，在處理大量資料時效能較好，但是客戶機應用程式語言可能具有更強大、更通用的處理、整合或其他庫功能。您必須考慮所需功能的範圍，以確保針對每個儲存過程使用最佳的可能解決方案。

4)          除錯和概要分析功能有限

 

1.1.         執行儲存過程

用於呼叫儲存過程的命令與 MySQL 中的其他命令非常類似。使用 CALL 語句來呼叫儲存過程（ procedure ）。儲存過程使用輸出變數或結果集傳回值。執行 FUNCTION ，像其他任何函式一樣，從語句內部呼叫函式（即，透過呼叫相應函式的名稱），函式返回標量值。

每個儲存過程均與特定資料庫相關聯。這有多重含義：

Ø   USE <database> ：呼叫儲存過程時， MySQL 會在該儲存過程執行期間執行隱式 USE <database> 。不能在儲存過程內發出 USE 語句。

Ø   限定名稱：可使用儲存過程的資料庫名稱限定儲存過程名稱。執行此操作可引用當前資料庫以外的儲存過程。例如，要呼叫與 test 資料庫相關聯的儲存過程 p 或函式 f ，請使用 CALL test.p() 或 test.f() 。

Ø   資料庫刪除：刪除資料庫時，也會刪除與其關聯的所有儲存過程。

MySQL 允許在儲存過程內使用常規 SELECT 語句。此類查詢的結果集將直接傳送到客戶機。

 

1)          儲存過程示例

mysql> DELIMITER //

mysql> CREATE PROCEDURE record_count ()

-> BEGIN

-> SELECT 'Country count ', COUNT(*) FROM Country;

-> SELECT 'City count ', COUNT(*) FROM City;

-> SELECT 'CountryLanguage count', COUNT(*) FROM CountryLanguage;

-> END//

mysql> DELIMITER ;

Ø   複合語句

透過在儲存過程中使用 BEGIN … END 語法並使用觸發器，可以建立複合語句。 BEGIN … END 塊可包含零個或多個語句。空複合語句是合法的，而且複合語句中的語句數量沒有上限。

Ø   分隔符

在 BEGIN … END 語法中，必須使用分號 (;) 終止每個語句。由於 mysql 客戶機使用分號作為 SQL 語句的預設終止字元，在以互動方式或針對批處理使用 mysql 命令列客戶機時，必須使用 DELIMITER 語句更改此設定。

示例中，第一個 DELIMITER 語句用於將 SQL 語句終止字元更改為兩個正斜槓 (//) 。此更改可確保客戶機不會將複合語句中的分號解釋為語句分隔符，並確保客戶機不會過早地將 CREATE PROCEDURE 語句傳送到伺服器。當建立儲存過程的語句以 // 終止時，客戶機會先將該語句傳送到伺服器，然後再發出第二個 DELIMITER 語句將語句分隔符重置為分號。

 

 

2)          儲存函式：示例

mysql> DELIMITER //

mysql> CREATE FUNCTION pay_check (gross_pay FLOAT(9,2), tax_rate FLOAT (3,2))

-> RETURNS FLOAT(9,2)

-> NO SQL

-> BEGIN

-> DECLARE net_pay FLOAT(9,2)

-> DEFAULT 0;

-> SET net_pay=gross_pay - gross_pay * tax_rate;

-> RETURN net_pay;

-> END//

mysql> DELIMITER ;

Ø   RETURNS 子句

RETURNS 子句用於確定此函式要返回的值的型別。

Ø   特徵

透過多個特徵，可確定有關儲存函式所使用的資料的性質。在 MySQL 中，這些特徵僅供參考。伺服器不會使用這些特徵來限制允許儲存函式執行的語句種類。

l   CONTAINS SQL 表示儲存函式包含用於讀取或寫入資料的語句。如果未顯式提供以上任何特徵，則此為預設值。

l   NO SQL 表示儲存函式不包含任何 SQL 語句。

l   READS SQL DATA 表示儲存函式包含用於讀取資料的語句（例如， SELECT ）而不包含用於寫入資料的語句。

l   MODIFIES SQL DATA 表示儲存過程包含用於寫入資料的語句（例如， INSERT 或 DELETE ）。

注：在啟用了二進位制日誌記錄後，如果建立函式時未指定以下項之一，則 MySQL 會產生一個錯誤： NO SQL 、 READS SQL DATA 或 DETERMINISTIC 。

Ø   DECLARE 語句

在儲存過程中使用 DECLARE 語句來宣告本地變數並初始化使用者變數。可將 DEFAULT 子句新增到 DECLARE 語句的結尾，以便為使用者變數指定初始值。如果省去 DEFAULT 子句，則使用者變數的初始值為 NULL 。

Ø   SET 語句

透過 SET 語句，您可以使用 = 或 := 作為賦值運算子來向定義的變數賦值。

Ø   RETURN 語句

RETURN 語句用於終止儲存函式的執行，並將值表示式返回給函式呼叫方。

 

1.2.         檢查儲存過程

Ø   SHOW CREATE PROCEDURE 和 SHOW CREATE FUNCTION

這些語句為 MySQL 擴充套件，類似於 SHOW CREATE TABLE 。這些語句返回可用於重新建立指定儲存過程的具體字串。這些語句的主要限制之一是您必須知道過程或函式的名稱，並且必須確定其為過程或函式，然後才能嘗試檢視相應資訊。

Ø   SHOW PROCEDURE STATUS 和 SHOW FUNCTION STATUS

這些語句特定於 MySQL 。它們可返回儲存過程的特徵，如資料庫、名稱、型別、建立者以及建立和修改日期。這些語句有一個優點：可基於 LIKE 模式顯示特定儲存過程。如果未指定任何模式，則會根據所使用的語句，列出所有儲存過程或所有儲存函式的資訊。例如，以下語句顯示名稱以“ film ”開頭的過程的相關資訊：

SHOW PROCEDURE STATUS LIKE 'film%'\G

Ø   INFORMATION_SCHEMA.ROUTINES

INFORMATION_SCHEMA.ROUTINES 表包含儲存過程（過程和函式）的相關資訊，並返回可同時在 SHOW CREATE … 和 SHOW … STATUS 語句中找到的大部分詳細資訊，以包含用於建立儲存過程的實際語法。在這三個選項中，此表可完整地呈現資料庫中的可用儲存過程。

示例：

mysql> SELECT routine_name, routine_schema, routine_type, definer

> FROM INFORMATION_SCHEMA.ROUTINES

> WHERE routine_name LIKE 'film%';

+-------------------+----------------+--------------+----------------+

| routine_name | routine_schema | routine_type | definer |

+-------------------+----------------+--------------+----------------+

| film_in_stock | sakila | PROCEDURE | root@localhost |

| film_not_in_stock | sakila | PROCEDURE | root@localhost |

+-------------------+----------------+--------------+----------------+

2 rows in set (0.00 sec)

Ø   mysql 系統資料庫中與程式設計元件關聯的表

mysql 系統資料庫中包含的一些表可提供與 MySQL 儲存過程功能相關的資訊。這些表包括：

l   mysql.event 表，包含 MySQL 伺服器中所儲存事件的相關資訊；

l   mysql.proc 表，包含 MySQL 伺服器中的儲存過程和函式的相關資訊；

l   mysql.procs_priv 表，為引用儲存過程的使用者提供訪問控制授予詳細資訊；

 

1.3.         儲存過程和執行安全性

儲存過程和函式的使用涉及多個許可權。

預設操作：建立儲存過程時， MySQL 會自動向您的帳戶授予對該儲存過程的 EXECUTE 和 ALTER ROUTINE 許可權。擁有撤消許可權以及 GRANT OPTION 許可權的使用者稍後可撤消或刪除這些許可權。在建立儲存過程後，可以透過發出 SHOW GRANTS 語句來驗證這些許可權。

授予許可權：當在全域性級別或資料庫級別授予所有許可權時， GRANT ALL 語句包括除 GRANT OPTION 之外的所有儲存過程許可權。要授予 GRANT OPTION 許可權，請在該語句結尾包含 WITH GRANT OPTION 子句。您可以在單個儲存過程級別授予 EXECUTE 、 ALTER ROUTINE 和 GRANT OPTION 許可權，但僅限於已經存在的儲存過程。要授予對單個儲存過程的許可權，可使用其資料庫名稱限定儲存過程，並提供關鍵字 PROCEDURE 或 FUNCTION 以指示儲存過程型別，如以下示例中所示：

mysql> GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO 'magellan'@'localhost' WITH GRANT OPTION;

mysql> GRANT ALL ON world_innodb.* TO 'magellan'@'localhost';

mysql> GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO 'magellan'@'localhost';

 

許可權對應允許的操作

CREATE ROUTINE ：建立儲存過程。

ALTER ROUTINE ：更改或刪除儲存過程。

EXECUTE ：執行儲存過程。

GRANT OPTION ：將許可權授予其他帳戶。