MySQL中存儲過程和存儲函數(shù)的示例分析

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

MySQL PROCEDURE

存儲過程（PROCEDURE）是存儲在服務(wù)器中的一組指定的SQL 語句，客戶機(jī)無需不斷重新發(fā)出單個(gè)語句，而可以引用存儲過程。存儲過程類型分為存儲過程(PROCEDURE)和存儲函數(shù)(FUNCTION)。

?  存儲過程：通過CALL 語句調(diào)用過程。這些過程可以使用輸出變量或結(jié)果集傳回值。

?  存儲函數(shù)：在語句中調(diào)用函數(shù)。這些函數(shù)可返回標(biāo)量值。

l  使用存儲過程的好處：

1)         客戶機(jī)功能

通過存儲過程，您可以在數(shù)據(jù)庫中集中創(chuàng)建一個(gè)語句或一系列語句，以供使用不同編程語言編寫或在不同平臺上運(yùn)行的多個(gè)客戶機(jī)應(yīng)用程序使用。

2)         安全性

存儲過程為需要最高安全級別的應(yīng)用程序提供了一個(gè)解決方法。例如，銀行針對所有常用操作均使用存儲過程和函數(shù)。這提供了一致、安全的環(huán)境。可對存儲過程進(jìn)行編碼，以確保正確記錄了每個(gè)操作。在此類設(shè)置中，應(yīng)用程序和用戶無法直接訪問數(shù)據(jù)庫表，只能執(zhí)行特定的存儲過程。

3)         性能

因?yàn)榉?wù)器和客戶機(jī)之間需要發(fā)送的信息變少了，所以存儲過程可提升性能?？蛻魴C(jī)按名稱調(diào)用存儲過程，而不是傳遞該存儲過程所包含的所有語句。

4)         函數(shù)庫

通過存儲過程，可以在數(shù)據(jù)庫服務(wù)器中使用函數(shù)庫。這些庫用作數(shù)據(jù)庫的API。

l  使用存儲過程的問題：

1)         增加了服務(wù)器負(fù)載

在數(shù)據(jù)庫自身中執(zhí)行存儲過程可增加服務(wù)器負(fù)載并降低應(yīng)用程序的性能?？梢赃\(yùn)行測試并運(yùn)用常識來確保在數(shù)據(jù)庫本身中包含邏輯所帶來的方便比可能引發(fā)的性能問題更為顯著。

2)         開發(fā)工具有限

MySQL 中支持存儲過程的開發(fā)工具不像在更通用的編程語言中那樣成熟和明確。此局限性會使存儲過程的編寫和調(diào)試過程更加困難，在決策過程中需要加以考慮。

3)         語言功能和速度有限

雖然在許多情況下在數(shù)據(jù)庫本身中包含邏輯具有很大的優(yōu)勢，但是與其他編程語言相比，在可實(shí)現(xiàn)的內(nèi)容方面仍有局限。存儲過程在數(shù)據(jù)庫上下文中執(zhí)行，與客戶機(jī)應(yīng)用程序中的存儲過程相比，在處理大量數(shù)據(jù)時(shí)性能較好，但是客戶機(jī)應(yīng)用程序語言可能具有更強(qiáng)大、更通用的處理、集成或其他庫功能。您必須考慮所需功能的范圍，以確保針對每個(gè)存儲過程使用最佳的可能解決方案。

4)         調(diào)試和概要分析功能有限

1.1.        執(zhí)行存儲過程

用于調(diào)用存儲過程的命令與MySQL 中的其他命令非常類似。使用CALL 語句來調(diào)用存儲過程（procedure）。存儲過程使用輸出變量或結(jié)果集傳回值。執(zhí)行FUNCTION，像其他任何函數(shù)一樣，從語句內(nèi)部調(diào)用函數(shù)（即，通過調(diào)用相應(yīng)函數(shù)的名稱），函數(shù)返回標(biāo)量值。

每個(gè)存儲過程均與特定數(shù)據(jù)庫相關(guān)聯(lián)。這有多重含義：

?  USE <database>：調(diào)用存儲過程時(shí)，MySQL 會在該存儲過程運(yùn)行期間執(zhí)行隱式USE <database>。不能在存儲過程內(nèi)發(fā)出USE 語句。

?  限定名稱：可使用存儲過程的數(shù)據(jù)庫名稱限定存儲過程名稱。執(zhí)行此操作可引用當(dāng)前數(shù)據(jù)庫以外的存儲過程。例如，要調(diào)用與test 數(shù)據(jù)庫相關(guān)聯(lián)的存儲過程p 或函數(shù)f，請使用CALL test.p() 或test.f()。

?  數(shù)據(jù)庫刪除：刪除數(shù)據(jù)庫時(shí)，也會刪除與其關(guān)聯(lián)的所有存儲過程。

MySQL 允許在存儲過程內(nèi)使用常規(guī)SELECT 語句。此類查詢的結(jié)果集將直接發(fā)送到客戶機(jī)。

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 ;

?  復(fù)合語句

通過在存儲過程中使用BEGIN…END 語法并使用觸發(fā)器，可以創(chuàng)建復(fù)合語句。BEGIN…END 塊可包含零個(gè)或多個(gè)語句?？諒?fù)合語句是合法的，而且復(fù)合語句中的語句數(shù)量沒有上限。

?  分隔符

在BEGIN…END 語法中，必須使用分號(;) 終止每個(gè)語句。由于mysql 客戶機(jī)使用分號作為SQL 語句的默認(rèn)終止字符，在以交互方式或針對批處理使用mysql 命令行客戶機(jī)時(shí)，必須使用DELIMITER 語句更改此設(shè)置。

示例中，第一個(gè)DELIMITER 語句用于將SQL 語句終止字符更改為兩個(gè)正斜杠(//)。此更改可確?？蛻魴C(jī)不會將復(fù)合語句中的分號解釋為語句分隔符，并確保客戶機(jī)不會過早地將CREATE PROCEDURE 語句發(fā)送到服務(wù)器。當(dāng)創(chuàng)建存儲過程的語句以 // 終止時(shí)，客戶機(jī)會先將該語句發(fā)送到服務(wù)器，然后再發(fā)出第二個(gè)DELIMITER 語句將語句分隔符重置為分號。

2)         存儲函數(shù)：示例

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 子句用于確定此函數(shù)要返回的值的類型。

?  特征

通過多個(gè)特征，可確定有關(guān)存儲函數(shù)所使用的數(shù)據(jù)的性質(zhì)。在MySQL 中，這些特征僅供參考。服務(wù)器不會使用這些特征來限制允許存儲函數(shù)執(zhí)行的語句種類。

l  CONTAINS SQL 表示存儲函數(shù)包含用于讀取或?qū)懭霐?shù)據(jù)的語句。如果未顯式提供以上任何特征，則此為默認(rèn)值。

l  NO SQL 表示存儲函數(shù)不包含任何SQL 語句。

l  READS SQL DATA 表示存儲函數(shù)包含用于讀取數(shù)據(jù)的語句（例如，SELECT）而不包含用于寫入數(shù)據(jù)的語句。

l  MODIFIES SQL DATA 表示存儲過程包含用于寫入數(shù)據(jù)的語句（例如，INSERT 或DELETE）。

注：在啟用了二進(jìn)制日志記錄后，如果創(chuàng)建函數(shù)時(shí)未指定以下項(xiàng)之一，則MySQL 會產(chǎn)生一個(gè)錯(cuò)誤：NO SQL、READS SQL DATA 或DETERMINISTIC。

?  DECLARE 語句

在存儲過程中使用DECLARE 語句來聲明本地變量并初始化用戶變量?？蓪EFAULT 子句添加到DECLARE 語句的結(jié)尾，以便為用戶變量指定初始值。如果省去DEFAULT 子句，則用戶變量的初始值為NULL。

?  SET 語句

通過SET 語句，您可以使用= 或:= 作為賦值運(yùn)算符來向定義的變量賦值。

?  RETURN 語句

RETURN 語句用于終止存儲函數(shù)的執(zhí)行，并將值表達(dá)式返回給函數(shù)調(diào)用方。

1.2.        檢查存儲過程

?  SHOW CREATE PROCEDURE 和SHOW CREATE FUNCTION

這些語句為MySQL 擴(kuò)展，類似于SHOW CREATE TABLE。這些語句返回可用于重新創(chuàng)建指定存儲過程的具體字符串。這些語句的主要限制之一是您必須知道過程或函數(shù)的名稱，并且必須確定其為過程或函數(shù)，然后才能嘗試查看相應(yīng)信息。

?  SHOW PROCEDURE STATUS 和SHOW FUNCTION STATUS

這些語句特定于MySQL。它們可返回存儲過程的特征，如數(shù)據(jù)庫、名稱、類型、創(chuàng)建者以及創(chuàng)建和修改日期。這些語句有一個(gè)優(yōu)點(diǎn)：可基于LIKE 模式顯示特定存儲過程。如果未指定任何模式，則會根據(jù)所使用的語句，列出所有存儲過程或所有存儲函數(shù)的信息。例如，以下語句顯示名稱以“film”開頭的過程的相關(guān)信息：

SHOW PROCEDURE STATUS LIKE 'film%'\G

?  INFORMATION_SCHEMA.ROUTINES

INFORMATION_SCHEMA.ROUTINES 表包含存儲過程（過程和函數(shù)）的相關(guān)信息，并返回可同時(shí)在SHOW CREATE … 和SHOW … STATUS 語句中找到的大部分詳細(xì)信息，以包含用于創(chuàng)建存儲過程的實(shí)際語法。在這三個(gè)選項(xiàng)中，此表可完整地呈現(xiàn)數(shù)據(jù)庫中的可用存儲過程。

示例：

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 系統(tǒng)數(shù)據(jù)庫中與編程組件關(guān)聯(lián)的表

mysql 系統(tǒng)數(shù)據(jù)庫中包含的一些表可提供與MySQL 存儲過程功能相關(guān)的信息。這些表包括：

l  mysql.event 表，包含MySQL 服務(wù)器中所存儲事件的相關(guān)信息；

l  mysql.proc 表，包含MySQL 服務(wù)器中的存儲過程和函數(shù)的相關(guān)信息；

l  mysql.procs_priv 表，為引用存儲過程的用戶提供訪問控制授予詳細(xì)信息；

1.3.        存儲過程和執(zhí)行安全性

存儲過程和函數(shù)的使用涉及多個(gè)權(quán)限。

默認(rèn)操作：創(chuàng)建存儲過程時(shí)，MySQL 會自動向您的帳戶授予對該存儲過程的EXECUTE 和ALTER ROUTINE 權(quán)限。擁有撤消權(quán)限以及GRANT OPTION 權(quán)限的用戶稍后可撤消或刪除這些權(quán)限。在創(chuàng)建存儲過程后，可以通過發(fā)出SHOW GRANTS 語句來驗(yàn)證這些權(quán)限。

授予權(quán)限：當(dāng)在全局級別或數(shù)據(jù)庫級別授予所有權(quán)限時(shí)，GRANT ALL 語句包括除GRANT OPTION 之外的所有存儲過程權(quán)限。要授予GRANT OPTION 權(quán)限，請?jiān)谠撜Z句結(jié)尾包含WITH GRANT OPTION 子句。您可以在單個(gè)存儲過程級別授予EXECUTE、ALTER ROUTINE 和GRANT OPTION 權(quán)限，但僅限于已經(jīng)存在的存儲過程。要授予對單個(gè)存儲過程的權(quán)限，可使用其數(shù)據(jù)庫名稱限定存儲過程，并提供關(guān)鍵字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';

權(quán)限對應(yīng)允許的操作

CREATE ROUTINE：創(chuàng)建存儲過程。

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

EXECUTE：執(zhí)行存儲過程。

GRANT OPTION：將權(quán)限授予其他帳戶。

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