MySQL存儲過程

一嘲恍、存儲過程

1.1 什么是存儲過程

存儲過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中足画，一組為了完成特定功能的SQL 語句集，它存儲在數(shù)據(jù)庫中佃牛，一次編譯后永久有效淹辞，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個重要對象俘侠。在數(shù)據(jù)量特別龐大的情況下利用存儲過程能達(dá)到倍速的效率提升

1.2 數(shù)據(jù)庫存儲過程程序

當(dāng)我們了了解存儲過程是什么之后象缀，就需要了解數(shù)據(jù)庫中存在的這三種類型的數(shù)據(jù)庫存儲類型程序，如下：

存儲過程：?存儲過程是最常見的存儲程序兼贡，存儲過程是能夠接受輸入和輸出參數(shù)并且能夠在請求時被執(zhí)行的程序單元攻冷。

存儲函數(shù)：?存儲函數(shù)和存儲過程很相像娃胆，但是它的執(zhí)行結(jié)果會返回一個值遍希。最重要的是存儲函數(shù)可以被用來充當(dāng)標(biāo)準(zhǔn)的 SQL 語句，允許程序員有效的擴展 SQL 語言的能力里烦。

觸發(fā)器：?觸發(fā)器是用來響應(yīng)激活或者觸發(fā)數(shù)據(jù)庫行為事件的存儲程序凿蒜。通常，觸發(fā)器用來作為數(shù)據(jù)庫操作語言的響應(yīng)而被調(diào)用胁黑，觸發(fā)器可以被用來作為數(shù)據(jù)校驗和自動反向格式化废封。

注意： 其他的數(shù)據(jù)庫提供了別的數(shù)據(jù)存儲程序，包括包和類丧蘸。目前MySQL不提供這種結(jié)構(gòu)漂洋。

1.3 為什么要使用存儲程序

雖然目前的開發(fā)中存儲程序我們使用的并不是很多，但是不一定就否認(rèn)它力喷。其實存儲程序會為我們使用和管理數(shù)據(jù)庫帶來了很多優(yōu)勢：

使用存儲程序更加安全刽漂。

存儲程序提供了一種數(shù)據(jù)訪問的抽象機制，它能夠極大的改善你的代碼在底層數(shù)據(jù)結(jié)構(gòu)演化過程中的易維護性弟孟。

存儲程序可以降低網(wǎng)絡(luò)擁阻贝咙，因為屬于數(shù)據(jù)庫服務(wù)器的內(nèi)部數(shù)據(jù)，這相比在網(wǎng)上傳輸數(shù)據(jù)要快的多拂募。

存儲程序可以替多種使用不同構(gòu)架的外圍應(yīng)用實現(xiàn)共享的訪問例程庭猩，無論這些構(gòu)架是基于數(shù)據(jù)庫服務(wù)器外部還是內(nèi)部窟她。

以數(shù)據(jù)為中心的邏輯可以被獨立的放置于存儲程序中，這樣可以為程序員帶來更高蔼水、更為獨特的數(shù)據(jù)庫編程體驗震糖。

在某些情況下，使用存儲程序可以改善應(yīng)用程序的可移植性趴腋。（在另外某些情況下试伙，可移植性也會很差！）

這里我大致解釋一下上述幾種使用存儲程序的優(yōu)勢：

我們要知道在Java語言中于样，我們使用數(shù)據(jù)庫與Java代碼結(jié)合持久化存儲需要引入JDBC來完成疏叨。會想到JDBC，我們是否還能想起SQL注入問題呢穿剖？雖然使用PreparedStatement解決SQL注入問題蚤蔓，那就真的是絕對安全嗎？不糊余，它不是絕對安全的秀又。

這時候分析一下數(shù)據(jù)庫與Java代碼的連接操作流程。在BS結(jié)構(gòu)中贬芥，一般都是瀏覽器訪問服務(wù)器的吐辙，再由服務(wù)器發(fā)送SQL語句到數(shù)據(jù)庫，在數(shù)據(jù)庫中對SQL語句進(jìn)行編譯運行蘸劈，最后把結(jié)果通過服務(wù)器處理再返回瀏覽器昏苏。在此操作過程中，瀏覽器對服務(wù)器每發(fā)送一次對數(shù)據(jù)庫操作的請求就會調(diào)用對應(yīng)的SQL語句編譯和執(zhí)行威沫，這是一件十分浪費性能的事情贤惯，?性能下降了就說明對數(shù)據(jù)庫的操作?效率低?了。

還有一種可能是棒掠，在這個過程中進(jìn)行發(fā)送傳輸?shù)腟QL語句是對真實的庫表進(jìn)行操作的SQL語句孵构，如果在發(fā)送傳輸?shù)倪^程中被攔截了，一些不法分子會根據(jù)他所攔截的SQL語句推斷出我們數(shù)據(jù)庫中的庫表結(jié)構(gòu)烟很，這是一個很大的?安全隱患?颈墅。

關(guān)于可維護性的提高，這里模擬一個場景雾袱。通常數(shù)據(jù)庫在公司中是由DBA來管理的恤筛，如果管理數(shù)據(jù)庫多年的DBA辭職了，此時數(shù)據(jù)庫會被下一任DBA來管理谜酒。這里時候問題來了叹俏，數(shù)據(jù)庫中這么多的數(shù)據(jù)和SQL語句顯然對下一任管理者不太友好。就算管理多年的DBA長時間不操作查看數(shù)據(jù)庫也會忘記點什么東西僻族。所以粘驰，我們在需要引入存儲程序來進(jìn)行SQL語句的統(tǒng)一編寫和編譯屡谐，?為維護提供了便利?。（其實我覺得這個例子并不生動合理蝌数，但是為了大家能理解愕掏，請體諒！）

講了很多存儲程序的優(yōu)勢演變過程顶伞，其核心就是：需要將編譯好的一段或多段SQL語句放置在數(shù)據(jù)庫端的存儲程序中饵撑，以便解決以上問題并方便開發(fā)者直接調(diào)用。

二唆貌、存儲過程的使用步驟

2.1 存儲過程的開發(fā)思想

存儲過程時數(shù)據(jù)庫的一個重要的對象滑潘，可以封裝SQL語句集，可以用來完成一些較復(fù)雜的業(yè)務(wù)邏輯锨咙，并且可以入?yún)ⅲ▊鲄ⅲ┯锫薄⒊鰠ⅲǚ祷貐?shù)），這里與Java中封裝方式十分相似酪刀。

而且創(chuàng)建時會預(yù)先編譯后保存粹舵，開發(fā)者后續(xù)的調(diào)用都不需要再次編譯。

2.2 存儲過程的優(yōu)缺點

存儲過程使用的優(yōu)缺點其實在1.3中的優(yōu)勢中說到了骂倘。這里我簡單羅列一下存儲過程的優(yōu)點與缺點眼滤。

優(yōu)點：

在生產(chǎn)環(huán)境下，可以通過直接修改存儲過程的方式修改業(yè)務(wù)邏輯或bug历涝，而不用重啟服務(wù)器诅需。

執(zhí)行速度快，存儲過程經(jīng)過編譯之后會比單獨一條一條編譯執(zhí)行要快很多睬关。

減少網(wǎng)絡(luò)傳輸流量诱担。

便于開發(fā)者或DBA使用和維護。

在相同數(shù)據(jù)庫語法的情況下电爹，改善了可移植性。

缺點：

過程化編程料睛，復(fù)雜業(yè)務(wù)處理的維護成本高丐箩。

調(diào)試不便。

因為不同數(shù)據(jù)庫語法不一致恤煞，不同數(shù)據(jù)庫之間可移植性差屎勘。

2.3 MySQL存儲過程的官方文檔

英語好或者有能力的小伙伴可以去參考一下官方文檔。如果不參考官方文檔居扒，沒關(guān)系概漱，我在下面也會詳細(xì)講述MySQL存儲過程的各個知識點。

1https://dev.mysql.com/doc/refman/5.6/en/preface.html

2.3 存儲過程的使用語法

1createPROCEDURE 過程名( in|out|inout 參數(shù)名 數(shù)據(jù)類型 , ...)2begin3sql語句;4end;5call過程名(參數(shù)值);

in 是定義傳入?yún)?shù)的關(guān)鍵字喜喂。 out 是定義出參的關(guān)鍵字瓤摧。 inout 是定義一個出入?yún)?shù)都可以的參數(shù)竿裂。如果括號內(nèi)什么都不定義，就說明該存儲過程時一個無參的函數(shù)照弥。在后面會有詳細(xì)的案例分析腻异。

注意：SQL語句默認(rèn)的結(jié)束符為 ; ，所以在使用以上存儲過程時这揣，會報1064的語法錯誤悔常。我們可以使用 DELIMITER 關(guān)鍵字臨時聲明修改SQL語句的結(jié)束符為 // ，如下：

1-- 臨時定義結(jié)束符為"http://"2DELIMITER//3create PROCEDURE 過程名(in|out參數(shù)名 數(shù)據(jù)類型 , ...)4begin5sql語句;6end//7-- 將結(jié)束符重新定義回結(jié)束符為";"8DELIMITER ;

例如：使用存儲過程來查詢員工的工資（無參）

注意：如果在特殊的必要情況下给赞，我們還可以通過 delimiter 關(guān)鍵字將 ; 結(jié)束符聲明回來使用机打，在以下案例中我并沒有這樣將結(jié)束符聲明回原來的 ; ，在此請大家注意~

為什么我在這里提供了drop（刪除）呢片迅？

是因為我們在使用的時候如果需要修改存儲過程中的內(nèi)容姐帚，我們需要先刪除現(xiàn)有的存儲過程后，再creat重新創(chuàng)建障涯。

1# 聲明結(jié)束符為//2delimiter//34# 創(chuàng)建存儲過程(函數(shù))5createprocedurese()6begin7selectsalaryfromemployee;8end//910# 調(diào)用函數(shù)11callse()//1213# 刪除已存在存儲過程——se()函數(shù)14dropprocedureifexistsse//

三罐旗、存儲過程的變量和賦值

3.1 局部變量

聲明局部變量語法： declare var_name type [default var_value];

賦值語法：

注意：局部變量的定義，在begin/end塊中有效唯蝶。

使用set為參數(shù)賦值

1# set賦值23# 聲明結(jié)束符為//4delimiter//56# 創(chuàng)建存儲過程7createprocedureval_set()8begin9# 聲明一個默認(rèn)值為unknown的val_name局部變量10declareval_namevarchar(32)default'unknown';11# 為局部變量賦值12setval_name='Centi';13# 查詢局部變量14selectval_name;15end//1617# 調(diào)用函數(shù)18callval_set()//19

使用into接收參數(shù)

1delimiter // 2create procedure val_into() 3begin 4# 定義兩個變量存放name和age5declareval_namevarchar(32)default'unknown'; 6declareval_ageint; 7# 查詢表中id為1的name和age并放在定義的兩個變量中8selectname,ageintoval_name,val_agefromemployeewhereid=1; 9# 查詢兩個變量10selectval_name,val_age;11end //1213call val_into() //14

3.2 用戶變量

用戶自定義用戶變量九秀，當(dāng)前會話（連接）有效。與Java中的成員變量相似粘我。

語法：?@val_name

注意：?該用戶變量不需要提前聲明鼓蜒，使用即為聲明。

1delimiter//2createprocedureval_user()3begin4# 為用戶變量賦值5set@val_name='Lacy';6end//78# 調(diào)用函數(shù)9callval_user()//1011# 查詢該用戶變量12select@val_name//

3.3 會話變量

會話變量是由系統(tǒng)提供的征字，只在當(dāng)前會話（連接）中有效都弹。

語法： @@session.val_name

1# 查看所有會話變量2showsessionvariables;3# 查看指定的會話變量4select@@session.val_name;5# 修改指定的會話變量6set@@session.val_name=0;

這里我獲取了一下所有的會話變量，大概有500條會話變量的記錄匙姜。等我們深入學(xué)習(xí)MySQL后畅厢，了解了各個會話變量值的作用，可以根據(jù)需求和場景來修改會話變量值氮昧。

1delimiter//2createprocedure val_session()3begin4# 查看會話變量5show session variables;6end//78callval_session() //9

image-20200610112512964

3.4 全局變量

全局變量由系統(tǒng)提供框杜，整個MySQL服務(wù)器內(nèi)有效。

語法： @@global.val_name

1# 查看全局變量中變量名有char的記錄2showglobalvariables like'%char%'//3# 查看全局變量character_set_client的值4select@@global.character_set_client//

3.5 入?yún)⒊鰠?/p>

入?yún)⒊鰠⒌恼Z法我們在文章開頭已經(jīng)提過了袖肥，但是沒有演示咪辱，在這里我將演示一下入?yún)⒊鰠⒌氖褂谩?/p>

語法： in|out|inout 參數(shù)名 數(shù)據(jù)類型 , ...

in 定義出參； out 定義入?yún)ⅲ?inout 定義出參和入?yún)ⅰ?/p>

出參in

使用出參in時椎组，就是需要我們傳入?yún)?shù)油狂，在這里可以對參入的參數(shù)加以改變。簡單來說in只負(fù)責(zé)傳入?yún)?shù)到存儲過程中，類似Java中的形參专筷。

1delimiter//2createprocedureval_in(inval_namevarchar(32))3begin4# 使用用戶變量出參(為用戶變量賦參數(shù)值)5set@val_name1=val_name;6end//78# 調(diào)用函數(shù)9callval_in('DK')//1011# 查詢該用戶變量12select@val_name1//

入?yún)ut

在使用out時弱贼，需要傳入一個參數(shù)。而這個參數(shù)相當(dāng)于是返回值仁堪，可以通過調(diào)用哮洽、接收來獲取這個參數(shù)的內(nèi)容。簡單來說out只負(fù)責(zé)作返回值弦聂。

1delimiter//2# 創(chuàng)建一個入?yún)⒑统鰠⒌拇鎯^程3create procedureval_out(inval_idint,outval_name varchar(32)) 4begin 5? ? # 傳入?yún)?shù)val_id查詢員工返回name值(查詢出的name值用出參接收并返回) 6selectnameintoval_namefromemployeewhereid= val_id;7end//89# 調(diào)用函數(shù)傳入?yún)?shù)并聲明傳入一個用戶變量10callval_out(1, @n)//1112# 查詢用戶變量13select @n//

入?yún)⒊鰠nout

inout關(guān)鍵字鸟辅，就是把in和out合并成了一個關(guān)鍵字使用槐壳。被關(guān)鍵字修飾的參數(shù)既可以出參也可以入?yún)ⅰ?/p>

1delimiter // 2create procedure val_inout(in val_name varchar(32), inout val_age int) 3begin 4# 聲明一個a變量5declareaint; 6# 將傳入的參數(shù)賦值給a變量7seta = val_age; 8# 通過name查詢age并返回val_age9selectageintoval_agefromemployeewherename= val_name;10# 將傳入的a與-和查詢age結(jié)果字符串做拼接并查詢出來(concat——拼接字符串)11selectconcat(a,'-', val_age);12end //1314# 聲明一個用戶變量并賦予參數(shù)為4015set @ages = '40' //16# 調(diào)用函數(shù)并傳入?yún)?shù)值17call val_inout('Ziph', @ages) //18# 執(zhí)行結(jié)果19# 40-18

四宅此、存儲過程中的流程控制

4.1 if 條件判斷（推薦）

擴展： timestampdiff(unit, exp1, exp2) 為exp2 - exp1得到的差值，而單位是unit蹬叭。（常用于日期）

擴展例子： select timestampdiff(year,’2020-6-6‘,now()) from emp e where id = 1;

解釋擴展例子：查詢員工表中id為1員工的年齡捺檬，exp2就可以為該員工的出生年月日再层，并以年為單位計算。

語法：

1IF條件判斷 THEN 結(jié)果2[ELSEIF 條件判斷 THEN 結(jié)果] ...3[ELSE 結(jié)果]4ENDIF

舉例：傳入所查詢的id參數(shù)查詢工資標(biāo)準(zhǔn)（s<=6000為低工資標(biāo)準(zhǔn)堡纬；6000 <=10000為中工資標(biāo)準(zhǔn)聂受；10000 <=15000為中上工資標(biāo)準(zhǔn)；s style="font-size: inherit; color: inherit; line-height: inherit; margin: 0px; padding: 0px;">=15000為高工資標(biāo)準(zhǔn)）

1delimiter//2createprocedures_sql(inval_idint)3begin4# 聲明一個局部變量result存放工資標(biāo)準(zhǔn)結(jié)果5declareresultvarchar(32);6# 聲明一個局部變量存放查詢得到的工資7declaresdouble;8# 根據(jù)入?yún)d查詢工資9selectsalaryintosfromemployeewhereid=val_id;10# if判斷的使用11ifs<=6000then12setresult='低工資標(biāo)準(zhǔn)';13elseifs<=10000then14setresult='中工資標(biāo)準(zhǔn)';15elseifs<=15000then16setresult='中上工資標(biāo)準(zhǔn)';17else18setresult='高工資標(biāo)準(zhǔn)';19endif;20# 查詢工資標(biāo)準(zhǔn)結(jié)果21selectresult;22end//2324# 調(diào)用函數(shù)烤镐，傳入?yún)?shù)25calls_sql(1);

4.2 case條件判斷

關(guān)于case語句蛋济，不僅僅在存儲過程中可以使用，MySQL基礎(chǔ)查詢語句中也有用到過炮叶。相當(dāng)于是Java中的switch語句碗旅。

語法：

1# 語法一2CASEcase_value3WHENwhen_valueTHEN結(jié)果4[WHENwhen_valueTHEN結(jié)果]...5[ELSE結(jié)果]6ENDCASE78# 語法二(推薦語法)9CASE10WHEN條件判斷THEN結(jié)果11[WHEN條件判斷THEN結(jié)果]...12[ELSE結(jié)果]13ENDCASE

舉例：

1# 語法一2delimiter//3create procedures_case(inval_idint) 4begin 5? ? # 聲明一個局部變量result存放工資標(biāo)準(zhǔn)結(jié)果 6? ? declare resultvarchar(32);7# 聲明一個局部變量存放查詢得到的工資8declare sdouble;9# 根據(jù)入?yún)d查詢工資10selectsalaryintosfromemployeewhereid = val_id;11cases12when6000thensetresult ='低工資標(biāo)準(zhǔn)';13when10000thensetresult ='中工資標(biāo)準(zhǔn)';14when15000thensetresult ='中上工資標(biāo)準(zhǔn)';15elsesetresult ='高工資標(biāo)準(zhǔn)';16endcase;17selectresult;18end//1920calls_case(1);2122# 語法二(推薦)23delimiter//24create procedures_case(inval_idint)25begin26? ? # 聲明一個局部變量result存放工資標(biāo)準(zhǔn)結(jié)果27? ? declare resultvarchar(32);28# 聲明一個局部變量存放查詢得到的工資29declare sdouble;30# 根據(jù)入?yún)d查詢工資31selectsalaryintosfromemployeewhereid = val_id;32case33whens <=6000thensetresult ='低工資標(biāo)準(zhǔn)';34whens <=10000thensetresult ='中工資標(biāo)準(zhǔn)';35whens <=15000thensetresult ='中上工資標(biāo)準(zhǔn)';36elsesetresult ='高工資標(biāo)準(zhǔn)';37endcase;38selectresult;39end//4041calls_case(1);

4.3 loop循環(huán)

loop為死循環(huán)，需要手動退出循環(huán)镜悉，我們可以使用 leave 來退出循環(huán)

可以把leave看成Java中的break祟辟；與之對應(yīng)的，就有 iterate （繼續(xù)循環(huán)）也可以看成Java的continue

語法：

1[別名:]LOOP2? ? 循環(huán)語句3ENDLOOP[別名]

注意：別名和別名控制的是同一個標(biāo)簽侣肄。

示例1：循環(huán)打印1~10（leave控制循環(huán)的退出）

注意：該loop循環(huán)為死循環(huán)旧困，我們查的1~10數(shù)字是i，在死循環(huán)中設(shè)置了當(dāng)大于等于10時停止循環(huán)茫孔，也就是說先后執(zhí)行了10次該循環(huán)內(nèi)的內(nèi)容叮喳，結(jié)果查詢了10次，生成了10個結(jié)果（1~10）缰贝。

1delimiter//2createprocedures_loop()3begin4# 聲明計數(shù)器5declareiintdefault1;6# 開始循環(huán)7? ? num:8loop9# 查詢計數(shù)器記錄的值10selecti;11# 判斷大于等于停止計數(shù)12ifi>=10then13leavenum;14endif;15# 計數(shù)器自增116seti=i+1;17# 結(jié)束循環(huán)18endloopnum;19end//2021calls_loop();

打印結(jié)果：

image-20200610191639524

示例2：循環(huán)打印1~10（iterate和leave控制循環(huán)）

注意：這里我們使用字符串拼接計數(shù)器結(jié)果，而條件如果用iterate就必須時 i < 10 了畔濒！

1delimiter//2createprocedures_loop1()3begin4# 聲明變量i計數(shù)器5declareiintdefault1;6# 聲明字符串容器7declarestrvarchar(256)default'1';8# 開始循環(huán)9? ? num:10loop11# 計數(shù)器自增112seti=i+1;13# 字符串容器拼接計數(shù)器結(jié)果14setstr=concat(str,'-',i);15# 計數(shù)器i如果小于10就繼續(xù)執(zhí)行16ifi<10then17iteratenum;18endif;19# 計數(shù)器i如果大于10就停止循環(huán)20leavenum;21# 停止循環(huán)22endloopnum;23# 查詢字符串容器的拼接結(jié)果24selectstr;25end//2627calls_loop1();

image-20200610193153512

4.4 repeat循環(huán)

repeat循環(huán)類似Java中的do while循環(huán)剩晴，直到條件不滿足才會結(jié)束循環(huán)。

語法：

1[別名:]REPEAT2? ? 循環(huán)語句3UNTIL 條件4END REPEAT[別名]

示例：循環(huán)打印1~10

1delimiter//2createprocedures_repeat()3begin4declareiintdefault1;5declarestrvarchar(256)default'1';6# 開始repeat循環(huán)7? ? num:8repeat9seti=i+1;10setstr=concat(str,'-',i);11# until 結(jié)束條件12# end repeat 結(jié)束num 結(jié)束repeat循環(huán)13untili>=10endrepeatnum;14# 查詢字符串拼接結(jié)果15selectstr;16end//1718calls_repeat();

4.5 while循環(huán)

while循環(huán)就與Java中的while循環(huán)很相似了。

語法：

1[別名]WHILE條件DO2循環(huán)語句3ENDWHILE[別名]

示例：循環(huán)打印1~10

1delimiter//2createprocedures_while()3begin4declareiintdefault1;5declarestrvarchar(256)default'1';6# 開始while循環(huán)7? ? num:8# 指定while循環(huán)結(jié)束條件9whilei<10do10seti=i+1;11setstr=concat(str,'+',i);12# while循環(huán)結(jié)束13endwhilenum;14# 查詢while循環(huán)拼接字符串15selectstr;16end//1718calls_while();

4.6 流程控制語句（繼續(xù)赞弥、結(jié)束）

至于流程控制的繼續(xù)和結(jié)束毅整，我們在前面已經(jīng)使用過了。這里再列舉一下绽左。

leave：與Java中break;相似

1leave標(biāo)簽;

iterate：與Java中的continue;相似

1iterate 標(biāo)簽;

五悼嫉、游標(biāo)與handler

5.1 游標(biāo)

游標(biāo)是可以得到某一個結(jié)果集并逐行處理數(shù)據(jù)。游標(biāo)的逐行操作拼窥，導(dǎo)致了游標(biāo)很少被使用戏蔑！

語法：

1DECLARE游標(biāo)名 CURSOR FOR 查詢語句2--打開語法3OPEN游標(biāo)名4--取值語法5FETCH游標(biāo)名 INTO var_name [, var_name] ...6--關(guān)閉語法7CLOSE游標(biāo)名

了解了游標(biāo)的語法，我們開始使用游標(biāo)鲁纠。如下：

示例：使用游標(biāo)查詢id总棵、name和salary。

1delimiter//2createproceduref()3begin4declareval_idint;5declareval_namevarchar(32);6declareval_salarydouble;78# 聲明游標(biāo)9declareemp_flagcursorfor10selectid,name,salaryfromemployee;1112# 打開13openemp_flag;1415# 取值16fetchemp_flagintoval_id,val_name,val_salary;1718# 關(guān)閉19closeemp_flag;2021selectval_id,val_name,val_salary;22end//2324callf();

執(zhí)行結(jié)果：

image-20200610203622749

因為游標(biāo)逐行操作的特點改含，導(dǎo)致我們只能使用游標(biāo)來查詢一行記錄情龄。怎么改善代碼才可以實現(xiàn)查詢所有記錄呢？聰明的小伙伴想到了使用循環(huán)捍壤。對骤视，我們試試使用一下循環(huán)。

1delimiter//2createproceduref()3begin4declareval_idint;5declareval_namevarchar(32);6declareval_salarydouble;78# 聲明游標(biāo)9declareemp_flagcursorfor10selectid,name,salaryfromemployee;1112# 打開13openemp_flag;1415# 使用循環(huán)取值16c:loop17# 取值18fetchemp_flagintoval_id,val_name,val_salary;19endloop;2021# 關(guān)閉22closeemp_flag;2324selectval_id,val_name,val_salary;25end//2627callf();

image-20200610204034224

我們使用循環(huán)之后鹃觉，發(fā)現(xiàn)有一個問題专酗，因為循環(huán)是死循環(huán)，我們不加結(jié)束循環(huán)的條件帜慢，游標(biāo)會一直查詢記錄笼裳，當(dāng)查到?jīng)]有的記錄的時候，就會拋出異常 1329：未獲取到選擇處理的行數(shù) 粱玲。

如果我們想辦法指定結(jié)束循環(huán)的條件該怎么做呢躬柬？

這時候可以聲明一個boolean類型的標(biāo)記。如果為true時則查詢結(jié)果集抽减，為false時則結(jié)束循環(huán)允青。

1delimiter//2createproceduref()3begin4declareval_idint;5declareval_namevarchar(32);6declareval_salarydouble;78# 聲明flag標(biāo)記9declareflagbooleandefaulttrue;1011# 聲明游標(biāo)12declareemp_flagcursorfor13selectid,name,salaryfromemployee;1415# 打開16openemp_flag;1718# 使用循環(huán)取值19c:loop20fetchemp_flagintoval_id,val_name,val_salary;21# 如果標(biāo)記為true則查詢結(jié)果集22ifflagthen23selectval_id,val_name,val_salary;24# 如果標(biāo)記為false則證明結(jié)果集查詢完畢，停止死循環(huán)25else26leavec;27endif;28endloop;2930# 關(guān)閉31closeemp_flag;3233selectval_id,val_name,val_salary;34end//3536callf();

上述代碼你會發(fā)現(xiàn)并沒有寫完卵沉，它留下了一個很嚴(yán)肅的問題颠锉。當(dāng)flag = false時候可以結(jié)束循環(huán)。但是什么時候才讓flag為false笆泛埂琼掠？

于是，MySQL為我們提供了一個 handler 句柄停撞。它可以幫我們解決此疑惑瓷蛙。

handler句柄語法： declare continue handler for 異常 set flag = false;

handler句柄可以用來捕獲異常悼瓮，也就是說在這個場景中當(dāng)捕獲到 1329：未獲取到選擇處理的行數(shù) 時，就將flag標(biāo)記的值改為false艰猬。這樣使用handler句柄就解決了結(jié)束循環(huán)的難題横堡。讓我們來試試吧！

終極版示例：解決了多行查詢以及結(jié)束循環(huán)問題冠桃。

1delimiter//2createproceduref()3begin4declareval_idint;5declareval_namevarchar(32);6declareval_salarydouble;78# 聲明flag標(biāo)記9declareflagbooleandefaulttrue;1011# 聲明游標(biāo)12declareemp_flagcursorfor13selectid,name,salaryfromemployee;1415# 使用handler句柄來解決結(jié)束循環(huán)問題16declarecontinuehandlerfor1329setflag=false;1718# 打開19openemp_flag;2021# 使用循環(huán)取值22c:loop23fetchemp_flagintoval_id,val_name,val_salary;24# 如果標(biāo)記為true則查詢結(jié)果集25ifflagthen26selectval_id,val_name,val_salary;27# 如果標(biāo)記為false則證明結(jié)果集查詢完畢命贴，停止死循環(huán)28else29leavec;30endif;31endloop;3233# 關(guān)閉34closeemp_flag;3536selectval_id,val_name,val_salary;37end//3839callf();

執(zhí)行結(jié)果：

image-20200610210925964

在執(zhí)行結(jié)果中，可以看出查詢結(jié)果以多次查詢的形式食听，分布顯示到了每一個查詢結(jié)果窗口中胸蛛。

注意：在語法中，變量聲明碳蛋、游標(biāo)聲明胚泌、handler聲明是必須按照先后順序書寫的，否則創(chuàng)建存儲過程出錯肃弟。

5.2 handler句柄

語法：

1DECLAREhandler操作 HANDLER2FOR 情況列表...(比如：異常錯誤情況)3操作語句

注意：異常情況可以寫異常錯誤碼玷室、異常別名或SQLSTATE碼。

handler操作：

CONTINUE：?繼續(xù)

EXIT：?退出

UNDO：?撤銷

異常情況列表：

mysql_error_code

SQLSTATE [VALUE] sqlstate_value

condition_name

SQLWARNING

NOT FOUND

SQLEXCEPTION

注意：MySQL中各種異常情況代碼笤受、錯誤碼穷缤、別名和SQLSTATEM碼可參考官方文檔：

https://dev.mysql.com/doc/refman/5.6/en/server-error-reference.html

寫法示例：

1DECLAREexitHANDLERFORSQLSTATE'3D000'setflag =false;2DECLAREcontinueHANDLERFOR1050setflag =false;3DECLAREcontinueHANDLERFORnotfoundsetflag =false;

六、循環(huán)創(chuàng)建表

需求：創(chuàng)建下個月的每天對應(yīng)的表箩兽，創(chuàng)建的表格式為： comp_2020_06_01津肛、comp_2020_06_02、...

描述：我們需要用某個表記錄很多數(shù)據(jù)汗贫，比如記錄某某用戶的搜索身坐、購買行為（注意，此處是假設(shè)用數(shù)據(jù)庫保存）落包，當(dāng)每天記錄較多時部蛇，如果把所有數(shù)據(jù)都記錄到一張表中太龐大，需要分表咐蝇，我們的要求是涯鲁，每天一張表，存當(dāng)天的統(tǒng)計數(shù)據(jù)有序，就要求提前生產(chǎn)這些表——每月月底創(chuàng)建下一個月每天的表抹腿！

預(yù)編譯： PREPARE 數(shù)據(jù)庫對象名 FROM 參數(shù)名

執(zhí)行： EXECUTE 數(shù)據(jù)庫對象名 [USING @var_name [, @var_name] ...]

通過數(shù)據(jù)庫對象創(chuàng)建或刪除表： {DEALLOCATE | DROP} PREPARE 數(shù)據(jù)庫對象名

關(guān)于時間處理的語句：

1--EXTRACT(unit FROM date)? ? ? ? ? ? ? 截取時間的指定位置值2--DATE_ADD(date,INTERVAL expr unit)? ? 日期運算3--LAST_DAY(date)? ? ? ? ? ? ? ? ? ? ? ? ? 獲取日期的最后一天4--YEAR(date)? ? ? ? ? ? ? ? ? ? ? ? ? ? 返回日期中的年5--MONTH(date)? ? ? ? ? ? ? ? ? ? ? ? ? ? 返回日期的月6--DAYOFMONTH(date)? ? ? ? ? ? ? ? ? ? ? ? 返回日

代碼：

1--思路：循環(huán)構(gòu)建表名comp_2020_06_01到comp_2020_06_30；并執(zhí)行create語句旭寿。2delimiter//3createproceduresp_create_table()4begin5# 聲明需要拼接表名的下一個月的年警绩、月、日6declarenext_yearint;7declarenext_monthint;8declarenext_month_dayint;910# 聲明下一個月的月和日的字符串11declarenext_month_strchar(2);12declarenext_month_day_strchar(2);1314# 聲明需要處理每天的表名15declaretable_name_strchar(10);1617# 聲明需要拼接的118declaret_indexintdefault1;19# declare create_table_sql varchar(200);2021# 獲取下個月的年份22setnext_year=year(date_add(now(),INTERVAL1month));23# 獲取下個月是幾月 24setnext_month=month(date_add(now(),INTERVAL1month));25# 下個月最后一天是幾號26setnext_month_day=dayofmonth(LAST_DAY(date_add(now(),INTERVAL1month)));2728# 如果下一個月月份小于10盅称，就在月份的前面拼接一個029ifnext_month<1030thensetnext_month_str=concat('0',next_month);31else32# 如果月份大于10房蝉，不做任何操作33setnext_month_str=concat('',next_month);34endif;3536# 循環(huán)操作(下個月的日大于等于1循環(huán)開始循環(huán))37whilet_index<=next_month_daydo3839# 如果t_index小于10就在前面拼接040if(t_index<10)41thensetnext_month_day_str=concat('0',t_index);42else43# 如果t_index大于10不做任何操作44setnext_month_day_str=concat('',t_index);45endif;4647# 拼接標(biāo)命字符串48settable_name_str=concat(next_year,'_',next_month_str,'_',next_month_day_str);49# 拼接create sql語句50set@create_table_sql=concat(51'create table comp_',52table_name_str,53'(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');54# 預(yù)編譯55# 注意：FROM后面不能使用局部變量僚匆！56preparecreate_table_stmtFROM@create_table_sql;57# 執(zhí)行58executecreate_table_stmt;59# 創(chuàng)建表60DEALLOCATEpreparecreate_table_stmt;6162# t_index自增163sett_index=t_index+1;6465endwhile;66end//6768# 調(diào)用函數(shù)69callsp_create_table()

七微渠、其他

7.1 characteristic

在MySQL存儲過程中搭幻，如果沒有顯示的定義characteristic，它會隱式的定義一系列特性的默認(rèn)值來創(chuàng)建存儲過程逞盆。

LANGUAGE SQL

存儲過程語言檀蹋，默認(rèn)是sql，說明存儲過程中使用的是sql語言編寫的云芦，暫時只支持sql俯逾，后續(xù)可能會支持其他語言

NOT DETERMINISTIC

是否確定性的輸入就是確定性的輸出，默認(rèn)是NOT DETERMINISTIC舅逸，只對于同樣的輸入桌肴，輸出也是一樣的，當(dāng)前這個值還沒有使用

CONTAINS SQL

提供子程序使用數(shù)據(jù)的內(nèi)在信息琉历，這些特征值目前提供給服務(wù)器坠七，并沒有根據(jù)這些特征值來約束過程實際使用數(shù)據(jù)的情況。有以下選擇：CONTAINS SQL表示子程序不包含讀或者寫數(shù)據(jù)的語句NO SQL 表示子程序不包含sqlREADS SQL DATA 表示子程序包含讀數(shù)據(jù)的語句旗笔，但是不包含寫數(shù)據(jù)的語句MODIFIES SQL DATA 表示子程序包含寫數(shù)據(jù)的語句彪置。

SQL SECURITY DEFINER

MySQL存儲過程是通過指定SQL SECURITY子句指定執(zhí)行存儲過程的實際用戶。所以次值用來指定存儲過程是使用創(chuàng)建者的許可來執(zhí)行蝇恶，還是執(zhí)行者的許可來執(zhí)行拳魁，默認(rèn)值是DEFINERDEFINER 創(chuàng)建者的身份來調(diào)用，對于當(dāng)前用戶來說：如果執(zhí)行存儲過程的權(quán)限撮弧，且創(chuàng)建者有訪問表的權(quán)限潘懊，當(dāng)前用戶可以成功執(zhí)行過程的調(diào)用的INVOKER 調(diào)用者的身份來執(zhí)行，對于當(dāng)前用戶來說：如果執(zhí)行存儲過程的權(quán)限贿衍，以當(dāng)前身份去訪問表授舟，如果當(dāng)前身份沒有訪問表的權(quán)限，即便是有執(zhí)行過程的權(quán)限舌厨，仍然是無法成功執(zhí)行過程的調(diào)用的岂却。

COMMENT ''

存儲過程的注釋性信息寫在COMMENT里面，這里只能是單行文本裙椭，多行文本會被移除到回車換行等

7.2 死循環(huán)處理

如有死循環(huán)處理躏哩，可以通過下面的命令查看并殺死（結(jié)束）

1showprocesslist;2killid;

7.3 select語句中書寫case

1select2case3when 條件判斷 then 結(jié)果4when 條件判斷 then 結(jié)果5else 結(jié)果6end 別名,7*8from表名;

7.4 復(fù)制表和數(shù)據(jù)

1CREATE TABLE deptSELECT*FROMprocedure_demo.dept;2CREATE TABLE empSELECT*FROMprocedure_demo.emp;3CREATE TABLE salgradeSELECT*FROMprocedure_demo.salgrade;

7.5 臨時表

1create temporary table 表名( 2　　字段名 類型 [約束], 3　　name varchar(20)? 4)Engine=InnoDB default charset utf8; 5 6-- 需求：按照部門名稱查詢員工，通過select查看員工的編號揉燃、姓名扫尺、薪資。（注意炊汤，此處僅僅演示游標(biāo)用法）7delimiter $$ 8create procedure sp_create_table02(in dept_name varchar(32)) 9begin10declareemp_noint;11declareemp_namevarchar(32);12declareemp_saldecimal(7,2);13declareexit_flagintdefault0;1415declareemp_cursorcursorfor16selecte.empno,e.ename,e.sal17fromemp einnerjoindept done.deptno = d.deptnowhered.dname = dept_name;1819declarecontinuehandlerfornotfoundsetexit_flag =1;2021-- 創(chuàng)建臨時表收集數(shù)據(jù)22CREATEtemporaryTABLE`temp_table_emp`(23`empno`INT(11)NOTNULLCOMMENT'員工編號',24`ename`VARCHAR(32)NULLCOMMENT'員工姓名'COLLATE'utf8_general_ci',25`sal`DECIMAL(7,2)NOTNULLDEFAULT'0.00'COMMENT'薪資',26PRIMARYKEY(`empno`)USINGBTREE27)28COLLATE='utf8_general_ci'29ENGINE=InnoDB;? 3031? ? open emp_cursor;3233? ? c_loop:loop34? ? ? ? fetch emp_cursor into emp_no,emp_name,emp_sal;353637? ? ? ? if exit_flag != 1 then38insertintotemp_table_empvalues(emp_no,emp_name,emp_sal); 39? ? ? ? else40? ? ? ? ? ? leave c_loop;41endif;4243endloopc_loop;4445select*fromtemp_table_emp;4647select@sex_res;-- 僅僅是看一下會不會執(zhí)行到48? ? close emp_cursor;4950end$$5152call sp_create_table02('RESEARCH');

原文鏈接：https://www.tuicool.com/articles/INf2qe3