1. 存儲過程(Stored Procedure)

1.1 概述

1.1.1 定義：

存儲過程是一組預(yù)編譯的 SQL 語句和控制流語句（如條件判斷、循環(huán)）的集合，?無返回值?（但可通過 OUT/INOUT 參數(shù)或結(jié)果集返回?cái)?shù)據(jù)）。它支持參數(shù)傳遞、事務(wù)控制、異常處理等高級特性，適合封裝復(fù)雜的業(yè)務(wù)邏輯（如批量數(shù)據(jù)操作、多表關(guān)聯(lián)查詢）。（打包sql）

1.1.2 作用-（打包sql）

• 1、簡化操作，提高了sql語句的重用性，減少了開發(fā)程序員的壓力
• 2、減少操作過程中的失誤，提高效率
• 3、減少網(wǎng)絡(luò)傳輸量（客戶端不需要把所有的 SQL 語句通過網(wǎng)絡(luò)發(fā)給服務(wù)器）
• 4、減少了 SQL 語句暴露在網(wǎng)上的風(fēng)險(xiǎn)，也提高了數(shù)據(jù)查詢的安全性

1.1.3 適合場景 --（其實(shí)現(xiàn)在也不常用了）

1. ?復(fù)雜業(yè)務(wù)邏輯?：如訂單處理（涉及庫存扣減、支付記錄、物流狀態(tài)更新）。
2. ?批量數(shù)據(jù)操作?：如定時(shí)任務(wù)（每日統(tǒng)計(jì)銷售額并生成報(bào)表）。
   ?3. 事務(wù)控制?：確保多個(gè) SQL 操作要么全部成功，要么全部回滾（START TRANSACTION + COMMIT/ROLLBACK）。
   ?4. 性能優(yōu)化?：減少網(wǎng)絡(luò)傳輸（一次調(diào)用執(zhí)行多個(gè)語句），利用預(yù)編譯提升執(zhí)行效率。

1.2 創(chuàng)建及使用存儲過程

1.2.1 創(chuàng)建語法：

DELIMITER 新的結(jié)束標(biāo)記 如 $ ---此句非必須 
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[特性列表...]
BEGIN
存儲過程體
END $ --如上方的DELIMITER是$ 這里也要是￥

1. 特性列表（characteristics ）?：可選，常見如 DETERMINISTIC（結(jié)果確定）、NO SQL（無 SQL 查詢）、READS SQL DATA（讀取 SQL 數(shù)據(jù)）等（用于優(yōu)化器或安全策略）。-此處暫不展開
2. BEGIN 和 END: 存儲過程體中可以有多條 SQL 語句，如果僅僅一條SQL 語句，則可以省略 BEGIN 和 END。
3. DELIMITER:因?yàn)镸ySQL默認(rèn)的語句結(jié)束符號為分號‘;’。為了避免與存儲過程中SQL語句結(jié)束符相沖突，需要使用DELIMITER改變存儲過程的結(jié)束符。當(dāng)使用DELIMITER命令時(shí)，應(yīng)該避免使用反斜杠（‘\’）字符，因?yàn)榉葱本€是MySQL的轉(zhuǎn)義字符。 “DELIMITER //”語句的作用是將MySQL的結(jié)束符設(shè)置為//，并以“END //”結(jié)束存儲過程。存儲過程定義完畢之后再使用“DELIMITER ;”恢復(fù)默認(rèn)結(jié)束符。

1.2.2 參數(shù)-IN\OUT：

• 1、沒有參數(shù)（無參數(shù)無返回）
• 2、僅僅帶 IN 類型（有參數(shù)無返回）
• 3、僅僅帶 OUT 類型（無參數(shù)有返
  回）
• 4、既帶 IN 又帶 OUT（有參數(shù)有返回）
• 5、帶 INOUT（有參數(shù)有返回）

• IN ：當(dāng)前參數(shù)為輸入?yún)?shù)，也就是表示入?yún)ⅲ?/li>
• 存儲過程只是讀取這個(gè)參數(shù)的值。如果沒有定義參數(shù)種類， 默認(rèn)就是 IN ，表示輸入?yún)?shù)。
• OUT ：當(dāng)前參數(shù)為輸出參數(shù)，也就是表示出參；(執(zhí)行完成之后，調(diào)用這個(gè)存儲過程的客戶端或者應(yīng)用程序就可以讀取這個(gè)參數(shù)返回的值了。)
• INOUT ：當(dāng)前參數(shù)既可以為輸入?yún)?shù)，也可以為輸出參數(shù)。
  注意：IN、OUT、INOUT 都可以在一個(gè)存儲過程中帶多個(gè)。

1.2.3 調(diào)用存儲過程

一、語法格式

CALL 存儲過程名(實(shí)參列表)

二、調(diào)用不參數(shù)的存儲過程

1、調(diào)用in模式的參數(shù)：
CALL sp1('值');
2、調(diào)用out模式的參數(shù)：
SET @name;
CALL sp1(@name);
SELECT @name;
3、調(diào)用inout模式的參數(shù)：
SET @name=值;
CALL sp1(@name);
SELECT @name;

1.2.4 示例

舉例1（標(biāo)識符使用）：創(chuàng)建存儲過程select_all_data()，查看 emps 表的所有數(shù)據(jù)

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
DELIMITER ;

舉例2（參數(shù)使用）:創(chuàng)建存儲過程show_someone_salary2()，查看“emps”表的某個(gè)員工的薪資，并用IN參數(shù)empname輸入員工姓名，用OUT參數(shù)empsalary輸出員工薪資。

DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
SELECT salary INTO empsalary FROM emps WHERE ename = empname;
END //
DELIMITER ;

2. 存儲函數(shù)（Stored Function）

存儲函數(shù)是一段返回單個(gè)值的特殊存儲過程，?必須有且僅有一個(gè)返回值?（通過 RETURNS 子句聲明類型）。它的參數(shù)只能是輸入?yún)?shù)（IN 類型），且返回值通常用于 SQL 表達(dá)式（如 SELECT、WHERE 子句）。

2.1 概述

2.1.1 定義

存儲函數(shù)是一段返回單個(gè)值的特殊存儲過程，?必須有且僅有一個(gè)返回值?（通過 RETURNS 子句聲明類型）。它的參數(shù)只能是輸入?yún)?shù)（IN 類型），且返回值通常用于 SQL 表達(dá)式（如 SELECT、WHERE 子句）。
舉例：常見的函數(shù)：LENGTH、SUBSTR、CONCAT等

2.1.2 作用

1. 封裝計(jì)算邏輯，返回單個(gè)值
2. 簡化 SQL 查詢，避免重復(fù)代碼
3. 替代視圖或觸發(fā)器中的復(fù)雜邏輯
4. 提高執(zhí)行效率：存儲函數(shù)在數(shù)據(jù)庫中預(yù)編譯，調(diào)用時(shí)直接執(zhí)行編譯后的代碼，減少了 SQL 解析和傳輸?shù)拈_銷，尤其適用于高頻調(diào)用的輕量級計(jì)算

2.1.3 適合場景–（這個(gè)現(xiàn)在也不常用）

?1. 計(jì)算密集型操作?：如日期格式化（FORMAT_DATE()）、數(shù)值計(jì)算（CALC_TAX()）。
?2. 數(shù)據(jù)校驗(yàn)?：如驗(yàn)證手機(jī)號格式（返回 VALID 或 INVALID）。
?3. 簡化 SQL 查詢?：在 SELECT 語句中調(diào)用函數(shù)，避免重復(fù)編寫復(fù)雜表達(dá)式（如 SELECT GET_USERNAME(user_id)）。

2.2 創(chuàng)建及使用存儲過程

2.2.1 創(chuàng)建語法：

CREATE FUNCTION 函數(shù)名(參數(shù)名 參數(shù)類型,...)
RETURNS 返回值類型
[characteristics ...]
BEGIN
函數(shù)體 #函數(shù)體中肯定有 RETURN 語句
END

1、參數(shù)列表：FUNCTION中總是默認(rèn)為IN參數(shù)。（指定參數(shù)為IN、OUT或INOUT只對存儲過程是合法的，）

2、RETURNS type 語句表示函數(shù)返回?cái)?shù)據(jù)的類型；（必須有）

3、characteristic 同存儲過程相同。

4、函數(shù)體也可以用BEGIN…END來表示SQL代碼的開始和結(jié)束。如果函數(shù)體只有一條語句，也可以省略。同存儲過程相似。

2.2.2 調(diào)用存儲過程

在MySQL中，存儲函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法是一樣的。換言之，用戶自己定義的存儲函數(shù)與MySQL內(nèi)部函數(shù)是一個(gè)性質(zhì)的。區(qū)別在于，存儲函數(shù)是 用戶自己定義 的，而內(nèi)部函數(shù)是MySQL.的 開發(fā)者定義 的

SELECT 函數(shù)名(實(shí)參列表)

2.2.3 示例

存儲函數(shù)創(chuàng)建示例

#示例1
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL

BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;

##示例2
DELIMITER $$
CREATE FUNCTION CalculateTax(orderAmount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC  -- 聲明結(jié)果確定（相同輸入返回相同結(jié)果）
READS SQL DATA  -- 聲明僅讀取數(shù)據(jù)（無寫操作）
BEGIN
  RETURN orderAmount * 0.1;  -- 返回計(jì)算結(jié)果
END
$$
DELIMITER ;

存儲函數(shù)調(diào)用示例

SELECT count_by_id(@dept_id);--調(diào)用無參數(shù)
SELECT email_by_name();--調(diào)用有參數(shù)

3. 存儲過程 與 存儲函數(shù) 管理

存儲過程 與 存儲函數(shù) 的查看、修改，刪除

3.1 查看

1. 查看創(chuàng)建信息–SHOW CREATE

SHOW CREATE {PROCEDURE | FUNCTION} 存儲過程名或函數(shù)名
--示例
SHOW CREATE FUNCTION test_db.CountProc；

2. 查看狀態(tài)信息-SHOW STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
SHOW PROCEDURE STATUS LIKE 'SELECT%' \G

3. 查看屬性信息-information_schema.Routines表

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存儲過程或函數(shù)的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
-示例
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id' AND ROUTINE_TYPE = 'FUNCTION' \G

3.2 修改

修改已創(chuàng)建的存儲過程或存儲函數(shù)主要有兩種方式：?使用 ALTER 語句修改對象屬性? 或 ?使用 CREATE OR REPLACE 語句覆蓋原定義?（推薦用于邏輯修改）

一、使用 ALTER PROCEDURE（FUNCTION） 修改屬性（有限修改）

ALTER PROCEDURE 用于修改存儲過程的元數(shù)據(jù)屬性?（如注釋、參數(shù)模式、安全模式等），但無法修改存儲過程的邏輯代碼塊?（即 BEGIN…END 內(nèi)的 SQL 語句）。

-- 原存儲過程定義（假設(shè)已存在）
DELIMITER $$
CREATE PROCEDURE GetUserCount()
BEGIN
  SELECT COUNT(*) FROM users;
END
$$
DELIMITER ;

-- 修改注釋和安全模式
ALTER PROCEDURE GetUserCount
COMMENT '獲取系統(tǒng)總用戶數(shù)（僅讀取數(shù)據(jù)）'
READS SQL DATA;

二、 使用 CREATE OR REPLACE PROCEDURE 覆蓋原定義（推薦邏輯修改）

若需修改存儲過程的邏輯代碼塊?（如調(diào)整 SQL 語句、參數(shù)列表或流程控制），必須通過 CREATE OR REPLACE PROCEDURE 重新定義。該語句會刪除原存儲過程并創(chuàng)建新的同名過程，因此需確保新定義與原過程類型一致（均為 PROCEDURE）。

CREATE [OR REPLACE] PROCEDURE procedure_name([參數(shù)列表])
[特性列表]
BEGIN
  -- 新的 SQL 邏輯代碼塊
END;

三、 完全刪除后重新創(chuàng)建（備用方案）

若 CREATE OR REPLACE 無法滿足需求（如參數(shù)類型變更導(dǎo)致無法覆蓋），可先刪除原存儲過程，再重新創(chuàng)建。

3.3 刪除

刪除存儲過程和函數(shù)，可以使用DROP語句，其語法結(jié)構(gòu)如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存儲過程或函數(shù)的名
--示例
DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

4. 存儲過程 與 存儲函數(shù) 比較

阿里開發(fā)規(guī)范
【強(qiáng)制】禁止使用存儲過程，存儲過程難以調(diào)試和擴(kuò)展，更沒有移植性。
存儲過程主要缺點(diǎn)如下：
1、可移植性差。存儲過程不能跨數(shù)據(jù)庫移植，比如在 MySQL、Oracle 和 SQL Server 里編寫的存儲過程，在換成其他數(shù)據(jù)庫時(shí)都需要重新編寫。
2、調(diào)試?yán)щy。只有少數(shù) DBMS 支持存儲過程的調(diào)試。對于復(fù)雜的存儲過程來說，開發(fā)和維護(hù)都不容易。雖然也有一些第三方工具可以對存儲過程進(jìn)行調(diào)試，但要收費(fèi)。
3、存儲過程的版本管理很困難。比如數(shù)據(jù)表索引發(fā)生變化了，可能會導(dǎo)致存儲過程失效。我們在開發(fā)軟件的時(shí)候往往需要進(jìn)行版本管理，但是存儲過程本身沒有版本控制，版本迭代更新的時(shí)候很麻煩。
4、它不適合高并發(fā)的場景。高并發(fā)的場景需要減少數(shù)據(jù)庫的壓力，有時(shí)數(shù)據(jù)庫會采用分庫分表的方式，而且對可擴(kuò)展性要求很高，在這種情況下，存儲過程會變得難以維護(hù)， 增加數(shù)據(jù)庫的壓力 ，顯然就
不適用了。

總結(jié) 

到此這篇關(guān)于Mysql學(xué)習(xí)筆記之存儲過程與存儲函數(shù)的文章就介紹到這了,更多相關(guān)Mysql存儲過程與存儲函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論