文章目錄
- 31.1 存盤程序簡介
- 31.2 創建存盤程序
- 31.3 查看存盤程序
- 31.4 修改存盤程序
- 31.5 洗掉存盤程序
- 31.6 管理存盤函式
接下來的兩篇文章我們將會介紹 MySQL 存盤程序和函式，本篇主要介紹它們的概念和管理，下一篇將會討論如何撰寫存盤程序和函式來實作業務需求，
31.1 存盤程序簡介
MySQL 存盤程序（Stored procedure）是一種存盤在資料庫中的程式，它可以包含多個 SQL 陳述句，提供許多程序語言的功能，例如變數定義、條件陳述句、回圈陳述句、游標以及例外處理等，存盤程序沒有回傳值，但是它可以通過輸出引數實作資料的回傳，同時還可以產生一個查詢結果回傳到客戶端，
在資料庫中創建存盤程序之后，應用程式或其他存盤程序可以通過名稱對其進行重復呼叫，
在介紹存盤程序的管理之前，我們還需要了解一下它的優缺點，存盤程序的主要優點包括：
- 實作代碼的重用和集中管理，存盤程序存盤在資料庫中，可以在被重復呼叫，不同的應用可以共享存盤程序；
- 實作業務的封裝和隔離，應用程式通過介面訪問存盤程序；當業務發生變化時，只需要修改存盤程序的邏輯，對應用程式毫無影響；
- 減少了應用與資料庫之間的網路流量，呼叫存盤程序時，只需要傳遞引數，在一定程度上可以減輕網路負擔；
- 存盤程序可以提高安全性，應用程式通過存盤程序進行資料訪問，而不需要直接訪問資料表，保證資料的安全，
不過，存盤程序也存在一些缺點：
- MySQL 存盤程序的語法和其他資料庫之間不兼容，無法直接移植；
- 存盤程序需要占用資料庫服務器的資源，包括 CPU、記憶體等，MySQL 對于大量邏輯處理的支持不夠完善；
- 存盤程序的開發和維護需要專業的技能，MySQL 存盤程序不支持除錯功能，增加了應用程式的開發和維護難度，
一般來說，對于業務快速變化的互聯網應用，傾向于將業務邏輯放在應用層，便于擴展；對于傳統行業，或者復雜的報表分析，合理使用存盤程序可以提高效率，
31.2 創建存盤程序
MySQL 使用CREATE PROCEDURE陳述句創建存盤程序：
CREATE PROCEDURE sp_name (
[ IN | OUT | INOUT ] param_name data_type,
...
)
routine_body;
其中，sp_name 是存盤程序名稱；param_name 是引數名稱，IN 表示輸入引數（默認模式），OUT 表示輸出引數，INOUT 表示輸入輸出引數；data_type 是引數的資料型別；routine_body 是存盤程序的具體實作，
下面是一個創建存盤程序的示例：
DELIMITER $$
CREATE PROCEDURE GetDevelopers()
BEGIN
SELECT emp_name, sex, hire_date, salary, bonus, email
FROM employee
WHERE dept_id = 4;
END$$
DELIMITER ;
其中，DELIMITER不屬于存盤程序的內容，由于很多 MySQL 客戶端將分號（;）作為 SQL 陳述句的終止符，而存盤程序中包含多個陳述句；為了將存盤程序的定義整體發送到服務器，需要將終止符臨時修改為其他符號（例如 $$），最后再將其改回分號，BEGIN 和 END 表示程式主體的開始和結束，
存盤程序 GetDevelopers 沒有引數，程式體中包含了一個查詢陳述句，用于回傳開發部門的員工資訊，MySQL 使用 CALL 陳述句呼叫存盤程序，例如：
CALL GetDevelopers();
emp_name|sex|hire_date |salary |bonus|email |
--------|---|----------|--------|-----|-------------------|
趙云 |男 |2005-12-19|15000.00| 0.00|zhaoyun@shuguo.com |
廖化 |男 |2009-02-17| 6500.00| 0.00|liaohua@shuguo.com |
關平 |男 |2011-07-24| 6800.00| 0.00|guanping@shuguo.com|
趙氏 |女 |2011-11-10| 6600.00| 0.00|zhaoshi@shuguo.net |
關興 |男 |2011-07-30| 7000.00| 0.00|guanxing@shuguo.com|
張苞 |男 |2012-05-31| 6500.00| 0.00|zhangbao@shuguo.com|
趙統 |男 |2012-05-03| 6000.00| 0.00|zhaotong@shuguo.com|
周倉 |男 |2010-02-20| 8000.00| 0.00|zhoucang@shuguo.com|
馬岱 |男 |2014-09-16| 5800.00| 0.00|madai@shuguo.com |
📝MySQL 在第一次呼叫存盤程序時查找并編譯它的原始碼，并且將編譯結果放入快取，然后執行該存盤程序，如果在同一個會話中再次呼叫該存盤程序，MySQL 直接從快取中執行，而不需要重新編譯，
我們再創建一個存盤程序 GetEmpNumBySex，并且為其指定兩個引數，按照性別查詢員工的數量：
DELIMITER $$
CREATE PROCEDURE GetEmpNumBySex (
IN psex VARCHAR(10),
OUT pnum INT
)
BEGIN
SELECT COUNT(1)
INTO pnum
FROM employee
WHERE sex = psex;
END$$
DELIMITER ;
其中，psex 是輸入引數，型別為 VARCHAR(10)；pnum 是輸出引數，型別為 INTEGER；在存盤程序內部，通過一個查詢回傳指定性別的員工數量并賦值給 pnum，呼叫該存盤程序的示例如下：
CALL GetEmpNumBySex('女', @num);
SELECT @num;
@num|
----|
6|
會話變數 @num 用于接收存盤程序回傳的引數值，
📝MySQL 存盤程序的輸入引數使用 IN 模式表示，在存盤程序內部使用輸入引數的副本進行操作，不會改變外部傳入的引數值；輸出引數使用 OUT 模式表達，在存盤程序內部對其進行賦值并回傳給呼叫程式，存盤程序無法使用輸出引數的初始值；輸入輸出引數使用 INOUT 模式組成，它是 IN 和 OUT 引數的組合，
另外，在創建存盤程序時還可以指定一些可選的屬性：
CREATE PROCEDURE sp_name (
[ IN | OUT | INOUT ] param_name data_type,
...
)
[
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
]
routine_body;
其中，COMMENT 可以為存盤程序添加注釋；LANGUAGE表示撰寫存盤程序的語言，目前 SQL 是唯一可用值；DETERMINISTIC 屬性表示這是一個確定性的存盤函式，對于相同的輸入引數一定會回傳相同的結果；MySQL 默認創建的是非確定性函式（NOT DETERMINISTIC），
CONTAINS SQL 表示程式中不包含讀取或者寫入資料表的陳述句，這是默認設定，舉例來說，SET @x = 1 或者DO RELEASE_LOCK('abc')不會讀寫任何資料，NO SQL 表示程式不包含任何 SQL 陳述句，READS SQL DATA 表示程式包含讀取操作（例如 SELECT），但不會修改資料表，MODIFIES SQL DATA 表示程式包含寫入操作（例如 INSERT 或者DELETE），這些屬性僅供 MySQL 服務器參考使用，不會用于限制程式中實際使用的陳述句，
SQL SECURITY 屬性表示存盤程序以定義者權限（DEFINER ）還是呼叫者權限（INVOKER ）執行，具體參考下一篇文章，
31.3 查看存盤程序
使用SHOW PROCEDURE STATUS陳述句查看存盤程序的串列和屬性：
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE expr]
LIKE 用于匹配存盤程序的名稱，WHERE 可以指定更多的過濾條件，例如，以下陳述句回傳了存盤程序 GetDevelopers 的相關資訊：
SHOW PROCEDURE STATUS WHERE name = 'GetDevelopers';
Name |Value |
--------------------|-------------------|
Db |hrdb |
Name |GetDevelopers |
Type |PROCEDURE |
Definer |root@% |
Modified |2020-09-27 05:32:44|
Created |2020-09-27 05:32:44|
Security_type |DEFINER |
Comment | |
character_set_client|utf8mb4 |
collation_connection|utf8mb4_0900_ai_ci |
Database Collation |utf8mb4_0900_ai_ci |
回傳的資訊包括資料庫、存盤程序名稱、型別（PROCEDURE）、創建者、創建時間和修改時間、呼叫權限以及字符集資訊，
使用SHOW CREATE PROCEDURE陳述句查看存盤程序的定義，例如：
SHOW CREATE PROCEDURE GetDevelopers;
Name |Value |
--------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------|
Procedure |GetDevelopers |
sql_mode |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
Create Procedure |CREATE DEFINER=`root`@`%` PROCEDURE `GetDevelopers`()
BEGIN
SELECT emp_name, sex, hire_date, salary, bonus, email
FROM employee
WHERE dept_id = 4;
END|
character_set_client|utf8mb4 |
collation_connection|utf8mb4_0900_ai_ci |
Database Collation |utf8mb4_0900_ai_ci |
另外，我們也可以通過系統表 information_schema.routines 查看存盤程序和函式的資訊，
31.4 修改存盤程序
如果想要為存盤程序增加或洗掉某個引數，或者修改存盤程序的定義，只能通過下文中的DROP PROCEDURE陳述句洗掉該存盤程序，然后再次使用CREATE PROCEDURE陳述句創建存盤程序，
MySQL 中的ALTER PROCEDURE陳述句只能用于修改存盤程序的某些屬性：
ALTER PROCEDURE proc_name
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
這些屬性和 CREATE PROCEDURE 陳述句中的屬性一樣，
31.5 洗掉存盤程序
MySQL 使用DROP PROCEDURE陳述句洗掉存盤程序：
DROP PROCEDURE [IF EXISTS] sp_name;
如果洗掉的存盤程序不存在，將會回傳一個錯誤資訊；使用 IF EXISTS 可以避免該錯誤，例如：
DROP PROCEDURE IF EXISTS GetDevelopers;
31.6 管理存盤函式
MySQL 存盤函式（Stored function）和存盤程序類似，也是存盤在資料庫中的程式，但是它會回傳一個計算結果，存盤函式可以和內置函式或者運算式一樣用于 SQL 陳述句，可以提高代碼的可讀性以及可維護性，
📝MySQL 存盤程序和存盤函式統稱為存盤例程（Stored routine），MySQL 還支持一種創建函式的方式，就是通過撰寫 C++ 程式實作擴展的自定義函式（User-Defined Function），這一功能不在我們的專欄范圍之內，
存盤函式的管理和存盤程序類似，創建存盤函式使用CREATE FUNCTION陳述句：
CREATE FUNCTION sp_name (
param_name data_type,
...
)
RETURNS data_type
routine_body;
其中，sp_name 是存盤函式名稱；param_name 是引數名稱，所有的引數都是輸入引數；data_type 是引數或者回傳值的資料型別；RETURNS 定義了回傳值的型別；routine_body 是存盤程序的具體實作，
例如，以下陳述句創建了一個函式 add2：
DELIMITER $$
CREATE FUNCTION add2(
p1 int,
p2 int
)
RETURNS int
DETERMINISTIC
BEGIN
RETURN p1 + p2;
END $$
DELIMITER ;
函式 add2 用于計算兩個整數的和；DETERMINISTIC 屬性表示這是一個確定性函式，對于相同的輸入引數一定會回傳相同的結果；MySQL 默認創建的是非確定性函式（NOT DETERMINISTIC），
使用以下陳述句測驗一下 add2 函式：
SELECT add2(2, 3);
add2(2, 3)|
----------|
5|
查看存盤函式的方式和存盤程序類似，只需要將 PROCEDURE 替換成 FUNCTION 即可：
SHOW FUNCTION STATUS WHERE name = 'add2';
Name |Value |
--------------------|-------------------|
Db |hrdb |
Name |add2 |
Type |FUNCTION |
Definer |root@% |
Modified |2020-09-27 07:01:38|
Created |2020-09-27 07:01:38|
Security_type |DEFINER |
Comment | |
character_set_client|utf8mb4 |
collation_connection|utf8mb4_0900_ai_ci |
Database Collation |utf8mb4_0900_ai_ci |
其他查看方式可以參考上文中的 31.3 小節，
修改存盤函式的定義也只能通過DROP FUNCTION陳述句先洗掉，然后再次使用CREATE FUNCTION陳述句創建存盤函式，MySQL 中的ALTER FUNCTION陳述句只能用于修改存盤函式的某些屬性：
ALTER FUNCTION proc_name
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
這些屬性也可以在 CREATE FUNCTION 陳述句中指定，具體介紹可以參考上面的存盤程序，
MySQL 使用DROP FUNCTION陳述句洗掉存盤函式：
DROP FUNCTION [IF EXISTS] sp_name;
如果洗掉的存盤函式不存在，將會回傳一個錯誤資訊；使用 IF EXISTS 可以避免該錯誤，例如：
DROP FUNCTION IF EXISTS add2;
CSDN認證博客專家
資料庫架構師
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/138674.html
標籤：其他