MySQL中存儲過程和存儲函數的示例分析

這篇文章主要為大家展示了“MySQL中存儲過程和存儲函數的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“MySQL中存儲過程和存儲函數的示例分析”這篇文章吧。

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：將權限授予其他帳戶。

以上是“MySQL中存儲過程和存儲函數的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！