通常，在使用關(guān)系數(shù)據(jù)庫時，您可以直接從應(yīng)用程序代碼中發(fā)出單獨的結(jié)構(gòu)化查詢語言 (sql) 查詢來檢索或操作數(shù)據(jù)，例如 select、INSERT、UPDATE 或 delete。這些語句直接作用于并操作底層數(shù)據(jù)庫表。如果在訪問同一數(shù)據(jù)庫的多個應(yīng)用程序中使用相同的語句或語句組，則它們通常會在各個應(yīng)用程序中重復(fù)。
mysql，與許多其他關(guān)系數(shù)據(jù)庫管理系統(tǒng)類似， 支持使用存儲過程。存儲過程幫助第一組 或多個 SQL 語句以通用名稱重用，封裝 數(shù)據(jù)庫本身內(nèi)的通用業(yè)務(wù)邏輯。這樣的程序可以 從訪問數(shù)據(jù)庫的應(yīng)用程序調(diào)用以檢索或 以一致的方式操作數(shù)據(jù)。
使用存儲過程，您可以為常見的情況創(chuàng)建可重用的例程 跨多個應(yīng)用程序使用的任務(wù)，提供數(shù)據(jù)驗證， 或者通過限制來提供額外的數(shù)據(jù)訪問安全層 數(shù)據(jù)庫用戶直接訪問底層表并發(fā)出 任意查詢。
在本教程中，您將了解什么是存儲過程以及如何 創(chuàng)建返回數(shù)據(jù)并使用輸入和數(shù)據(jù)的基本存儲過程 輸出參數(shù)。
連接到 MySQL 并設(shè)置示例數(shù)據(jù)庫
在本節(jié)中，您將連接到 MySQL 服務(wù)器并創(chuàng)建一個 示例數(shù)據(jù)庫，以便您可以按照本指南中的示例進行操作。
在本指南中，您將使用一個虛構(gòu)的汽車集合。您將存儲 有關(guān)當(dāng)前擁有的汽車的詳細信息，包括其品牌、型號、制造年份，
如果您的 SQL 數(shù)據(jù)庫系統(tǒng)在遠程服務(wù)器上運行，請從本地計算機 ssh 到您的服務(wù)器：
ssh sammy@your_server_ip
然后打開 MySQL 服務(wù)器提示，將sammy替換為您的 MySQL 用戶名account:
mysql -u sammy-p
創(chuàng)建一個名為procedures的數(shù)據(jù)庫:
CREATE DATABASEprocedures;
如果數(shù)據(jù)庫創(chuàng)建成功，您將收到如下輸出：
OutputQuery OK, 1 row affected (0.01 sec)
選擇程序數(shù)據(jù)庫，運行以下 USE 語句：
USEprocedures;
您將收到以下輸出：
OutputDatabase changed
選擇數(shù)據(jù)庫后，您可以創(chuàng)建示例其中的表格。表 cars 將包含有關(guān)數(shù)據(jù)庫中汽車的簡化數(shù)據(jù)。它將包含以下列：
- 品牌：此列包含每輛擁有的汽車的品牌，使用最多 100 個字符的 varchar 數(shù)據(jù)類型表示。
- 型號：此列保存汽車型號名稱，使用 varchar 數(shù)據(jù)類型表示，最多 100 個字符。
- 年份：此列存儲汽車的構(gòu)建年，采用 int 數(shù)據(jù)類型來保存數(shù)值。
- value：該列使用十進制數(shù)據(jù)類型存儲汽車的值，最多 10 位數(shù)字，小數(shù)點后 2 位數(shù)字。
使用以下命令創(chuàng)建示例表：
CREATE TABLE cars ( make varchar(100), model varchar(100), year int, value decimal(10, 2));
如果輸出如下打印后，表已創(chuàng)建：
OutputQuery OK, 0 rows affected (0.00 sec)
接下來，通過運行以下 INSERT INTO 操作向 cars 表加載一些示例數(shù)據(jù)：
INSERT INTO carsVALUES('Porsche', '911 GT3', 2020, 169700),('Porsche', 'Cayman GT4', 2018, 118000),('Porsche', 'Panamera', 2022, 113200),('Porsche', 'Macan', 2019, 27400),('Porsche', '718 Boxster', 2017, 48880),('Ferrari', '488 GTB', 2015, 254750),('Ferrari', 'F8 Tributo', 2019, 375000),('Ferrari', 'SF90 Stradale', 2020, 627000),('Ferrari', '812 Superfast', 2017, 335300),('Ferrari', 'GTC4Lusso', 2016, 268000);
插入 該行動將在表中添加十輛樣品跑車，其中五輛 保時捷和五款法拉利車型。以下輸出表明所有 添加了五行：
OutputQuery OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0
至此，您就可以按照指南的其余部分進行操作并開始在 SQL 中使用存儲過程。
存儲過程簡介
MySQL 和許多其他關(guān)系數(shù)據(jù)庫中的存儲過程 系統(tǒng)是包含一個或多個布局指令的命名對象 然后在調(diào)用時由數(shù)據(jù)庫按順序執(zhí)行。在 最基本的例子，存儲過程可以保存一個通用的語句 可重用的例程，例如從數(shù)據(jù)庫中檢索數(shù)據(jù) 經(jīng)常使用的過濾器。例如，您可以創(chuàng)建一個存儲過程來 檢索在最后給定時間內(nèi)下訂單的在線商店客戶 月數(shù)。在最復(fù)雜的場景中，存儲過程可以 代表描述復(fù)雜業(yè)務(wù)邏輯的廣泛程序 健壯的應(yīng)用程序。
存儲過程中的指令集可以包括返回或操作數(shù)據(jù)的常見 SQL 語句，例如 SELECT 或 INSERT 查詢。此外，存儲過程可以利用：
- 傳遞給存儲過程或通過存儲過程返回的參數(shù)。
- 聲明的變量以直接在過程代碼中處理檢索到的數(shù)據(jù)。
- 條件語句，允許執(zhí)行部分內(nèi)容 存儲過程代碼取決于某些條件，例如 if 或 CASE 指令。
- 循環(huán)，例如 while、LOOP 和 REPEAT，允許多次執(zhí)行部分代碼，例如針對檢索到的數(shù)據(jù)中的每一行執(zhí)行
- 錯誤處理指令，例如向訪問該過程的數(shù)據(jù)庫用戶返回錯誤消息。
- 調(diào)用其他存儲過程數(shù)據(jù)庫。
當(dāng)通過名稱調(diào)用過程時，數(shù)據(jù)庫引擎按照定義逐條指令執(zhí)行它。
數(shù)據(jù)庫用戶必須具有適當(dāng)?shù)膱?zhí)行權(quán)限 給定的程序。此權(quán)限要求提供了一層 安全性，禁止直接數(shù)據(jù)庫訪問，同時授予用戶訪問權(quán)限 保證安全執(zhí)行的各個過程。
存儲過程直接在數(shù)據(jù)庫服務(wù)器上執(zhí)行， 在本地執(zhí)行所有計算并將結(jié)果返回給調(diào)用者 僅當(dāng)完成時才使用。
如果您想更改過程行為，您可以更新 數(shù)據(jù)庫中的過程，以及正在使用它的應(yīng)用程序 自動選擇新版本。所有用戶將立即開始 使用新的程序代碼而不需要調(diào)整它們
以下是用于創(chuàng)建存儲過程的 SQL 代碼的一般結(jié)構(gòu)：
DELIMITER //CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)BEGIN instruction_1; instruction_2; . . . instruction_n;END //DELIMITER ;
此代碼片段中的第一個和最后一個指令是 DELIMITER // 和分隔符 ;.通常，MySQL使用分號符號（;） 分隔語句并指示它們何時開始和結(jié)束。如果你 在 MySQL 控制臺中執(zhí)行多個語句，用 分隔 分號，它們將被視為單獨的命令并執(zhí)行 各自獨立，一個接一個。然而，存儲過程可以 包含多個命令，這些命令將在執(zhí)行時按順序執(zhí)行 被叫。當(dāng)試圖告訴 MySQL 創(chuàng)建 一個新的程序。數(shù)據(jù)庫引擎會遇到分號 在存儲過程主體中并認(rèn)為它應(yīng)該停止執(zhí)行 陳述。在這種情況下，預(yù)期的陳述是整個 過程創(chuàng)建代碼，而不是過程中的單個指令
要解決此限制，您可以使用 DELIMITER 命令暫時將分隔符從 ; 更改為 ; 本身，因此 MySQL 會誤解您的意圖。 // 在 CREATE PROCEDURE 期間 稱呼。然后，存儲過程主體內(nèi)的所有分號都將是 按原樣傳遞到服務(wù)器。整個程序完成后， 分隔符改回 ;最后一個 DELIMITER ;.
創(chuàng)建新過程的代碼的核心是 CREATE PROCEDURE 調(diào)用，后跟過程名稱： procedure_name 在示例中。過程名稱后面跟著一個可選的列表 過程將接受的參數(shù)。最后一部分是程序 正文，包含在 BEGIN 和 END 語句中。里面是過程代碼，其中可以包含單個 SQL 語句，例如 SELECT 查詢或更復(fù)雜的代碼。
END 命令以臨時分隔符 // 結(jié)尾，而不是典型的分號。
在下一節(jié)中，您將創(chuàng)建一個不包含單個查詢的參數(shù)的基本存儲過程。
創(chuàng)建一個不帶參數(shù)的存儲過程參數(shù)
在本節(jié)中，您將創(chuàng)建第一個存儲過程，該存儲過程封裝單個 SQL SELECT 語句，以返回按品牌和價值降序排列的自有汽車列表。
開始于執(zhí)行您要使用的 SELECT 語句：
SELECT * FROM cars ORDER BY make, value DESC;
數(shù)據(jù)庫將返回來自以下位置的汽車列表汽車表，首先按品牌排序，然后在單個品牌內(nèi)按價值降序排列：
Output --------- --------------- ------ ----------- | make | model | year | value | --------- --------------- ------ ----------- | Ferrari | SF90 Stradale | 2020 | 627000.00 | | Ferrari | F8 Tributo | 2019 | 375000.00 | | Ferrari | 812 Superfast | 2017 | 335300.00 | | Ferrari | GTC4Lusso | 2016 | 268000.00 | | Ferrari | 488 GTB | 2015 | 254750.00 | | Porsche | 911 GT3 | 2020 | 169700.00 | | Porsche | Cayman GT4 | 2018 | 118000.00 | | Porsche | Panamera | 2022 | 113200.00 | | Porsche | 718 Boxster | 2017 | 48880.00 | | Porsche | Macan | 2019 | 27400.00 | --------- --------------- ------ ----------- 10 rows in set (0.00 sec)
最有價值的法拉利位于列表頂部，最不值錢的保時捷出現(xiàn)在
假設(shè)此查詢將在多個應(yīng)用程序中頻繁使用或 由多個用戶使用，并假設(shè)您希望確保每個人都會使用 對結(jié)果進行排序的方式完全相同。為此，您想要創(chuàng)建一個 存儲過程將該語句保存在可重用的名為 procedure.
要創(chuàng)建此存儲過程，請執(zhí)行以下代碼片段：
DELIMITER //CREATE PROCEDUREget_all_cars()BEGIN SELECT * FROM cars ORDER BY make, value DESC;END //DELIMITER ;
如上一節(jié)所述，第一個和最后一個命令 (DELIMITER / / 和 DELIMITER ?? 告訴 MySQL 在過程期間停止將分號字符視為語句分隔符創(chuàng)建。
CREATE PROCEDURE SQL 命令后跟過程名稱get_all_cars，您可以定義它來最好地描述過程的作用。過程名稱后面有一對括號() 您可以在其中添加參數(shù)。在此示例中，該過程不 使用參數(shù)，因此括號為空。然后，在定義過程代碼塊的開始和結(jié)束的 BEGIN 和 END 命令之間，逐字寫入之前使用的 SELECT 語句。
數(shù)據(jù)庫將返回一條成功消息：
OutputQuery OK, 0 rows affected (0.02 sec)
get_all_cars過程現(xiàn)在保存在數(shù)據(jù)庫中，當(dāng)調(diào)用時，它將執(zhí)行保存的語句： is.
要執(zhí)行保存的存儲過程，可以使用 CALL SQL 命令，后跟過程名稱。嘗試像這樣運行新創(chuàng)建的過程：
CALLget_all_cars;
過程名稱get_all_cars就是使用該過程所需的全部內(nèi)容。您不再需要手動鍵入以前使用的 SELECT 語句的任何部分。數(shù)據(jù)庫將顯示結(jié)果，就像之前運行的 SELECT 語句的輸出一樣：
Output --------- --------------- ------ ----------- | make | model | year | value | --------- --------------- ------ ----------- | Ferrari | SF90 Stradale | 2020 | 627000.00 | | Ferrari | F8 Tributo | 2019 | 375000.00 | | Ferrari | 812 Superfast | 2017 | 335300.00 | | Ferrari | GTC4Lusso | 2016 | 268000.00 | | Ferrari | 488 GTB | 2015 | 254750.00 | | Porsche | 911 GT3 | 2020 | 169700.00 | | Porsche | Cayman GT4 | 2018 | 118000.00 | | Porsche | Panamera | 2022 | 113200.00 | | Porsche | 718 Boxster | 2017 | 48880.00 | | Porsche | Macan | 2019 | 27400.00 | --------- --------------- ------ ----------- 10 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
您現(xiàn)在已成功創(chuàng)建一個不帶任何參數(shù)的存儲過程，該存儲過程返回 cars 表中以特定方式訂購的所有汽車。您可以在多個應(yīng)用程序中使用該過程。
在下一節(jié)中，您將創(chuàng)建一個接受 根據(jù)用戶輸入更改過程行為的參數(shù)。
