【版權聲明】未經(jīng)博主同意，謝絕轉載！（請尊重原創(chuàng)，博主保留追究權）
https://blog.csdn.net/m0_69908381/article/details/130857854
出自【進步*于辰的博客】

【存儲過程】這個知識點，我在大二下期學習【mysql】時就接觸過。第一次接觸，覺得新奇，就上網(wǎng)查資料。只是，那時基礎一般，就有些晦澀難懂；然后，那時的課程實訓項目都比較簡單，業(yè)務比較淺顯、單一，數(shù)據(jù)表也比較少，也就未必需要存儲過程。因此，可以說那時我對存儲過程是一知半解。
第一次實習，盡管項目不是很大，但由于客戶量大，對性能要求較高，因此很多業(yè)務都使用了存儲過程，我才重新學習，并在工作中總結了一些經(jīng)驗。
存儲過程的難度一般，但畢竟細節(jié)很多，而在實際工作中又未必都能涉及這些細節(jié)，工作時間一長，就可能忘記，于是我特來寫這篇文章，既是為自己做個筆記，也是跟大家分享【存儲過程】的學習和使用方法，望對大家有幫助！！
參考筆記三，P34.1、P35、P37.1。

注：為了方便大家理解以及便于闡述，我會直接在示例中注釋（//），這是java的注釋格式，在SQL中不適用。因此，如果大家需要復制代碼進行測試，運行前先將注釋全部刪除。

1、概述

存儲過程是一種存儲于數(shù)據(jù)庫、封裝了sql語句和流程控制語句、進而通過類如調用方法的形式來調用（如：傳參、獲取返回值）、從而實現(xiàn)業(yè)務功能（即將一定程序業(yè)務遷移到數(shù)據(jù)庫內，將業(yè)務交由數(shù)據(jù)庫管理）的數(shù)據(jù)結構。

1、優(yōu)點

1. 存儲過程對復雜sql語句進行了封裝，而調用簡便，故簡化了一些復雜的操作；
2. 若數(shù)據(jù)表變動（如：表名修改、字段名修改）或業(yè)務變動，不需要變動代碼，故簡化了對變動的管理；
3. 提高了程序性能。因為存儲過程存于數(shù)據(jù)庫，減少了sql傳輸的流量。并且，數(shù)據(jù)庫會對存儲過程進行編譯（調用時），其中，mysql存儲過程是按需編譯。大多數(shù)數(shù)據(jù)庫（如：oracle、mysql），編譯后的存儲過程都存于數(shù)據(jù)庫緩存，即若存儲過程在單個連接中被多次調用，調用的就是緩存內的存儲過程；否則調用的是數(shù)據(jù)庫內的（未編譯），此時存儲過程的執(zhí)行效率相當于查詢；
4. 存儲過程提供了一個接口供開發(fā)人員調用，這使得開發(fā)人員不必考慮其內部功能。同時，只需向訪問存儲過程的應用程序授權，而不必向其提供基礎數(shù)據(jù)表權限，故提高了安全性，且可重用和透明。

2、缺點

1. 存儲過程會占用當前連接內存（因為存儲過程會經(jīng)過編譯存儲于緩存中，而緩存是內存的一部分）。其中，由于mysql設計的初衷是高效的查詢，非邏輯運算，故若存儲過程中使用了大量的邏輯操作則會占用大量的CPU；
2. 存儲過程的構造使得開發(fā)復雜的存儲過程變得困難；
3. 存儲過程難以調試（僅有很少的工具可以調試存儲過程），且開發(fā)和維護都不容易；
4. 對數(shù)據(jù)庫的依賴性高，難以移植（存儲過程的內部就是sql語句，自然對數(shù)據(jù)庫依賴性高）。

3、補充說明

大都是情況下，存儲過程內都會包含流程控制語句。為何？因為使用存儲過程的原因無非兩種：

1. 封裝一條復雜的sql語句；
2. 封裝一個包含多個原子操作（sql語句），而這些原子操作間會進行一些邏輯運算或數(shù)據(jù)處理的事務。

哪些是流程控制語句？
比較常用的如：條件語句、循環(huán)語句。
我曾為mysql流程控制語句單獨寫過一篇文章，因此本文中不再贅述，大家可以參考博文【關于mysql流程控制語句的簡述】，下文示例中就有用到。

2、關于存儲過程的使用

2.1 創(chuàng)建、修改、刪除

那篇博文中闡述了如何使用navicat創(chuàng)建、修改、刪除存儲過程的方法。因此，在此不再贅述。

2.2 細節(jié)說明

員工表：emp

字段名	類型	說明
emp_no	smallint	員工號
emp_name	varchar(20)	員工名
emp_salary	decimal(5,2)	員工工資

先看示例：

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_admin_EIByENo_Sel`(IN `empNo` int,OUT `empName` varchar(20))
BEGIN// 定義變量 doubleSal，表示“雙倍工資”，默認值為0，定義默認值也可以是 default(0)declare doubleSal int default 0;// 查詢員工號為empNo的員工的工資，并將值賦予變量 doubleSalselect emp_salary into doubleSal from emp where emp_no = empNo;set doubleSal = doubleSal * 2;// 賦值，必須使用 set。注意：此處不兼容：*=/+=// 查詢工資是此員工工資雙倍的員工名select emp_name into empName from emp where emp_salary = doubleSal;select empName;// 這是固定格式，相當于”result 變量“END

示例說明 + 注意事項：

1. 存儲過程格式：create procedure 存儲過程名(參數(shù)列表) begin...end；（示例definer那些是指明“用戶、連接、數(shù)據(jù)庫等”）
2. 存儲過程命名規(guī)范：P_[前/后臺標識]_[模塊/功能簡稱]By[條件名簡稱]_Sel（Sel表示查詢，Del表示刪除...）；
3. 存儲過程體必須用begin...end包括；
4. 在參數(shù)empNo、empName前的in/out是參數(shù)模式。in表示輸入?yún)?shù)，限制參數(shù)只能用于傳入，即形參；out表示輸出參數(shù)，限制只能用于傳出，即返回值。參數(shù)模式用于聲明此參數(shù)是否可用于傳入/傳出。
   第3種參數(shù)模式：inout，表示此參數(shù)既可傳入，也可傳出（關于如何使用，后續(xù)補充）。
   參數(shù)默認模式是in；
5. out傳出參數(shù)等同于變量。示例中empName的聲明等同于declare empName varchar(20)。不同的是，前者沒有“賦值類型限制”，如：

前者：set empName = 2023;// 不報錯
后者：set empName = 2023;// 報錯

6. 任何參數(shù)，若未初始化（設置默認值），則當將此參數(shù)作為返回值時（即：select 參數(shù)），無結果；
7. 所有已用參數(shù)，需初始化。否則，當返回值是select 參數(shù)時，此存儲過程無結果；（注：這1點不是虛言，因為select后可以是一個常量，即以常量作為返回值）
8. 參數(shù)名最好不要與字段名相同；
9. 存儲過程名不能包含“-”（連字符）；
10. 語句體（sql語句）不能嵌套流程控制語句，如：if、loop；
11. 所有的定義（declare）必須置于開頭，且變量或條件的定義要在游標（cursor，下文說明）的定義之前；

2.3 查詢

所有存儲過程存于數(shù)據(jù)表information_schema.routines中。

2.4 調用

call P_admin_EIByENo_Sel(1001, @);

這是固定格式，無論是在navicat命令行、cmd，還是在框架（如：mybatis）中。
1001對應傳入?yún)?shù)empNo；@對應傳出參數(shù)empName，也可以是@empName、@xx，就目前我所知，@后的標識任意（存儲過程的返回值由select 變量決定，與@后的標識無關，但傳出參數(shù)empName的位置必須至少有一個@（相當于占位符）。
注意一點： 存儲過程的實參與java方法實參有一定類似，即賦值類型限制。如示例，可以是1001，而不能是'1001'（字符型）。

3、關于cursor（游標）

3.1 概述

什么是游標？
游標是一種能夠對結果集中的每一行記錄進行定位、并對所指向記錄的數(shù)據(jù)進行操作的數(shù)據(jù)結構。
如：java迭代器（iterator）中的也是游標，也稱之為光標，其初始指向第一個元素的前面。
游標的用途是什么？
迭代器是何用途？遍歷。因此，存儲過程中的游標是用于控制遍歷的（直白而言，游標用于在循環(huán)語句中獲取記錄）。

3.2 示例

功能：根據(jù)用戶ID，刪除評論和評論回復記錄。

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_admin_RRTUByUid_Del`(in userId int)
BEGINdeclare rComId int default 0;// 評論IDdeclare rRepN int default 0;// 評論回復數(shù)// 定義游標declare rComId_cursor cursor for select comment_id from gd_resource_comment where user_id = userId;// 根據(jù)用戶ID查詢所有評論IDdeclare rComId_next int default 0;declare continue handler for not found set rComId_next = -1;// -- -Aopen rComId_cursor;// 打開游標getRComId:loop// 從結果集中獲取一行記錄。結合上下文，此結果集是當前用戶旗下的所有評論ID，// 因此每次獲取（fetch）的是其中一個評論IDfetch rComId_cursor into rComId;// 查詢當前評論ID（rComId）所對應的評論回復數(shù)select count(1) into rRepN from gd_resource_response where comment_id = rComId;if rRepN > 0 thendelete from gd_resource_response where comment_id = rComId;// 刪除評論回復end if;delete from gd_resource_comment where comment_id = rComId;// 刪除評論if rComId_next = -1 then// --------------------------------------Bleave getRComId;// 跳出循環(huán)，類似 breakend if;end loop getRComId;close rComId_cursor;// 關閉游標END

示例說明 + 注意事項：

1. 游標使用（fetch）前需要先打開（open 游標名），游標打開時如iterator的游標一般，初始指向第一行的前面；使用完后（循環(huán)結束）最好關閉游標（close 游標名）。其中，游標可多次打開（可用于多個循環(huán)）；
2. 游標定義格式：declare 游標名 cursor for select_statement；（其中的select_statement是查詢型sql語句）
3. 獲取游標值（一行記錄）：fetch 游標名 into 變量；
4. 示例中A的作用：
   大家肯定用過java迭代器，當調用next()時，在底層會先判斷是否存在下一個元素，若存在，則返回此元素；否則返回null，不會出現(xiàn)異常。而在mysql的游標中，當fetch時，同樣會先判斷是否存在下一行記錄，若存在則返回此記錄；否則報錯。那如何避免報錯？ 這就是A的作用。
   實現(xiàn)思路： 先判斷是否存在下一行記錄，若不存在則跳出循環(huán)，避免下一次fetch。
   具體實現(xiàn)： 定義A，格式：declare continue handler for not found set 變量 = 值。什么意思呢？就是當fetch時，A也會執(zhí)行，若滿足not found（即不存在下一行記錄）時，執(zhí)行變量 = 值。那么，就可以使用此變量來控制循環(huán)（示例中B，結束循環(huán)）。

4、最后

本文中的例子是為了方便大家理解、便于闡述mysql存儲過程而簡單舉出或是我曾用過的，不一定有實用性。
其實mysql存儲過程的細節(jié)很多，只是我沒有那么細致地進行闡述。我闡述的原則是“以吾之理解，著重之闡述”。因此，這篇文章可能并不適合初學者。
給大家推薦2篇博文，也是我較為系統(tǒng)學習mysql存儲過程時參考的文章。

1. MySQL中的存儲過程（詳細篇）；（轉發(fā)）
2. Mysql存儲過程大全。（轉發(fā)）

如果大家想要快速掌握這個知識點，我的建議是“多測試，學以致用”。

本文完結。