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

創(chuàng)新互聯(lián)公司是一家專業(yè)提供裕華企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、H5場(chǎng)景定制、小程序制作等業(yè)務(wù)。10年已為裕華眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)的建站公司優(yōu)惠進(jìn)行中。

MySQL PROCEDURE

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

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

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

l  使用存儲(chǔ)過程的好處：

1)         客戶機(jī)功能

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

2)         安全性

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

3)         性能

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

4)         函數(shù)庫(kù)

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

l  使用存儲(chǔ)過程的問題：

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

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

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

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

3)         語(yǔ)言功能和速度有限

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

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

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

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

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

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

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

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

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

1)         存儲(chǔ)過程示例

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ù)合語(yǔ)句

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

?  分隔符

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

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

2)         存儲(chǔ)函數(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)存儲(chǔ)函數(shù)所使用的數(shù)據(jù)的性質(zhì)。在MySQL 中，這些特征僅供參考。服務(wù)器不會(huì)使用這些特征來限制允許存儲(chǔ)函數(shù)執(zhí)行的語(yǔ)句種類。

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

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

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

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

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

?  DECLARE 語(yǔ)句

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

?  SET 語(yǔ)句

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

?  RETURN 語(yǔ)句

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

1.2.        檢查存儲(chǔ)過程

?  SHOW CREATE PROCEDURE 和SHOW CREATE FUNCTION

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

?  SHOW PROCEDURE STATUS 和SHOW FUNCTION STATUS

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

SHOW PROCEDURE STATUS LIKE 'film%'\G

?  INFORMATION_SCHEMA.ROUTINES

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

示例：

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ù)庫(kù)中與編程組件關(guān)聯(lián)的表

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

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

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

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

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

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

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

授予權(quán)限：當(dāng)在全局級(jí)別或數(shù)據(jù)庫(kù)級(jí)別授予所有權(quán)限時(shí)，GRANT ALL 語(yǔ)句包括除GRANT OPTION 之外的所有存儲(chǔ)過程權(quán)限。要授予GRANT OPTION 權(quán)限，請(qǐng)?jiān)谠撜Z(yǔ)句結(jié)尾包含WITH GRANT OPTION 子句。您可以在單個(gè)存儲(chǔ)過程級(jí)別授予EXECUTE、ALTER ROUTINE 和GRANT OPTION 權(quán)限，但僅限于已經(jīng)存在的存儲(chǔ)過程。要授予對(duì)單個(gè)存儲(chǔ)過程的權(quán)限，可使用其數(shù)據(jù)庫(kù)名稱限定存儲(chǔ)過程，并提供關(guān)鍵字PROCEDURE 或FUNCTION 以指示存儲(chǔ)過程類型，如以下示例中所示：

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)限對(duì)應(yīng)允許的操作

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

ALTER ROUTINE：更改或刪除存儲(chǔ)過程。

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

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

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