1也殖、MySQL存儲過程簡介
在對數(shù)據(jù)庫進行增土思、刪、改忆嗜、查等操作時己儒，實際上輸入的SQL語句會經(jīng)由MySQL引擎分析語法是否正確，之后再編譯成MySQL可識別的命令捆毫，最后執(zhí)行該語句并將結(jié)果返回至客戶端闪湾。
假如能夠?qū)υ摿鞒踢M行簡化，省略語法分析和編譯的環(huán)節(jié)绩卤，那么MySQL的執(zhí)行效率將會大大提高途样。因此首先需要了解什么是存儲過程：
存儲過程是SQL語句和控制語句的預(yù)編譯集合，以一個名稱存儲并作為一個單元處理濒憋。
存儲過程會被儲存在數(shù)據(jù)庫內(nèi)何暇，可以由應(yīng)用程序調(diào)用執(zhí)行，而且允許用戶聲明變量以及進行流程控制凛驮；存儲過程可以接受參數(shù)裆站，包括輸入類型的參數(shù)，或輸出類型的參數(shù)，并且可以存在多個返回值宏胯。
因此使用存儲過程的效率要比使用單一的SQL語句執(zhí)行的效率要高羽嫡，例如：編寫了兩條SQL的語句，MySQL會對這兩條語句逐一的進行分析肩袍、編譯厂僧，再逐一執(zhí)行；而采用存儲過程以后了牛，只有在第一次執(zhí)行時才進行語法分析和編譯，以后當客戶端再去調(diào)用辰妙，會直接調(diào)用編譯完成的結(jié)果鹰祸，通過省略再次進行語法分析和編譯而提高了執(zhí)行效率。
存儲過程的優(yōu)點：
-
增強SQL語句的功能和靈活性
在存儲過程內(nèi)可以寫控制語句密浑，從而可以完成復(fù)雜的判斷以及較復(fù)雜的運算蛙婴，因此具有很強的靈活性； -
實現(xiàn)較快的執(zhí)行速度
例如執(zhí)行某個復(fù)雜的操作尔破，當包含大量的sql語句時街图，雖然首次執(zhí)行與不使用存儲過程的效率無差，但以后客戶端再次調(diào)用時便直接從內(nèi)存中來執(zhí)行編譯好的結(jié)果懒构，從而實現(xiàn)了更高的執(zhí)行速度餐济。 -
減少了網(wǎng)絡(luò)流量
當需要通過客戶端發(fā)送SQL語句來使服務(wù)器執(zhí)行某些操作時，如果每次都是發(fā)送單獨的SQL語句胆剧，那么通過http協(xié)議所提交的數(shù)據(jù)量相對而言會比較大絮姆；而使用存儲過程則可以減少數(shù)據(jù)量，從而減少了網(wǎng)絡(luò)流量秩霍。
2篙悯、存儲過程語法結(jié)構(gòu)解析
創(chuàng)建存儲過程
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,……]])
[characteristic ……] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
COMMENT ' STRING ':
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
可以理解為：
創(chuàng)建存儲過程
創(chuàng)建者，省略即為當前登錄到MySQL的用戶
過程名 前置選項 參數(shù)
特性 過程體
前置選項：
- IN铃绒，表示該參數(shù)的值必須在調(diào)用存儲過程時指定鸽照，在存儲過程中不能被返回；
- OUT颠悬，表示該參數(shù)的值可以被存儲過程改變矮燎，并且可以返回；
- INOUT赔癌，表示該參數(shù)在在調(diào)用存儲過程時指定漏峰，并且可以被存儲過程改變和返回。
關(guān)于特性：
- CONTAINS SQL：包含SQL語句届榄，但不包含讀或?qū)憯?shù)據(jù)的語句浅乔；
- NO SQL：不包含SQL語句；
- READS SQL DATA：包含讀數(shù)據(jù)的語句；
- MODIFIES SQL DATA：包含寫數(shù)據(jù)的語句靖苇；
- SQL SECURITY { DEFINER | INVOKER }：指明誰有權(quán)限來執(zhí)行席噩。
關(guān)于過程體：
- 過程體由合法的SQL語句構(gòu)成；
- 過程體可以是近乎任意的SQL語句贤壁；
- 過程體如果為復(fù)合結(jié)構(gòu)悼枢，則使用BEGIN……END語句包含；
- 復(fù)合結(jié)構(gòu)可以包含聲明脾拆、循環(huán)馒索、控制結(jié)構(gòu)。
3名船、創(chuàng)建不帶參數(shù)的存儲過程
以函數(shù)VERSION()為例绰上，創(chuàng)建可以返回版本信息的存儲過程：
之后就可以調(diào)用該存儲過程了，調(diào)用的方式有兩種：
CALL sp_name([parameter[,……]])
CALL sp_name[()]
區(qū)別：如果存儲過程在封裝時沒有參數(shù)渠驼，“()”有或沒有都可以蜈块；但是當存儲過程帶有參數(shù)，就必須有“()”迷扇。
因此對于不帶有參數(shù)的存儲過程sp1而言百揭，兩種調(diào)用方式都可以：
4、創(chuàng)建帶有IN類型參數(shù)的存儲過程
創(chuàng)建數(shù)據(jù)表users：
此次共填入了18條記錄蜓席，手動輸入過于繁瑣器一，因此提供源碼，點擊下載使用厨内。：
之后需要使用帶有IN類型的存儲過程來刪除用戶指定的“id”號的用戶數(shù)據(jù)盹舞，按照慣例，首先需要修改分隔符隘庄，之后創(chuàng)建存儲過程：
單行過程體也可以使用BEGIN……END語句包含踢步，而過程體中第一個“id”是指數(shù)據(jù)表中的字段，第二個“id”是傳入存儲過程的參數(shù)丑掺。
將分隔符改回默認的“获印；”，因為存儲過程有參數(shù)街州，因此使用帶有“()”的調(diào)用方式兼丰，希望刪除“id”為3的用戶記錄，此時“神奇”的事情發(fā)生了唆缴，數(shù)據(jù)表被清空了鳍征，18條記錄全部被刪除：
其實出現(xiàn)這種情況的原因很簡單，雖然我們知道過程體中兩個“id”的區(qū)別面徽，但是對于系統(tǒng)而言無法區(qū)分艳丛，認為兩個都是字段匣掸，因此就全部刪除了，這也說明要注意過程體中的參數(shù)名不能與數(shù)據(jù)表中的字段名相同氮双，而對于存儲過程而言碰酝，只能修改如下幾個簡單的選項：
ALTER PROCEDURE sp_name [characteristic ……]
COMMENT ' STRING ':
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
由于不能修改過程體，因此只能刪除存儲過程再重寫戴差，刪除的語法結(jié)構(gòu)如下：
DROP PROCEDURE [IF EXISTS] sp_name
此時就可以執(zhí)行刪除存儲過程操作送爸，并使用提供下載的原碼將數(shù)據(jù)庫記錄導(dǎo)入了：
重新創(chuàng)建存儲過程：
修改分隔符并查看數(shù)據(jù)表數(shù)據(jù)：
因為數(shù)據(jù)被清空的緣故，“id”編號會繼續(xù)排序暖释，此時選擇刪除“id”為23的用戶記錄：
再次搜索“id”為23的用戶顯示數(shù)據(jù)為空袭厂，表明已成功刪除。
5球匕、創(chuàng)建帶有IN和OUT類型參數(shù)的存儲過程
對之前的“removeUserById”這個存儲過程進行升級纹磺，刪除指定的“id”且返回剩余的“id”數(shù)量：
恢復(fù)分隔符并查看數(shù)據(jù)表中現(xiàn)有的“id”數(shù)量：
此時調(diào)用該存儲過程并刪除“id”為27的用戶記錄，另一個參數(shù)“@num”為用戶變量谐丢，用來記錄剩余的“id”數(shù)量，通過查詢可見剩余“id”數(shù)量為16：
關(guān)于變量的相關(guān)內(nèi)容蚓让，可參考（十）MySQL中的變量乾忱。
6、創(chuàng)建帶有多個OUT類型參數(shù)的存儲過程
除了可以通過指定“id”來刪除用戶記錄之外历极，還可以通過例如年齡窄瘟、性別等等其他字段來刪除：
通過查看記錄發(fā)現(xiàn)會有很多年齡相同的用戶，因此創(chuàng)建一個可以根據(jù)年齡來刪除用戶記錄的存儲過程趟卸，且該過程不僅能返回剩余的用戶數(shù)量蹄葱，還能返回刪除的記錄數(shù)量。不過在此之前锄列，先簡單介紹一個能實現(xiàn)該存儲過程的系統(tǒng)函數(shù)ROW_COUNT()：
該函數(shù)實際上是返回最近增加图云、刪除、更新等操作影響的行數(shù)邻邮，例如以數(shù)據(jù)表“test”為例：
現(xiàn)向表中再插入兩條記錄后調(diào)用函數(shù)ROW_COUNT()：
此時顯示被影響的行數(shù)為2竣况，了解其功能后，再來創(chuàng)建存儲過程：
修改分隔符筒严，查詢原數(shù)據(jù)表的總記錄數(shù)為13丹泉，以及將要刪除的年齡為23的用戶記錄數(shù)為4：
理論上執(zhí)行完存儲過程后，剩余的記錄數(shù)應(yīng)為9：
其中變量@var1為刪除記錄數(shù)鸭蛙，變量@var2為剩余記錄數(shù)摹恨，可見該存儲過程已成功執(zhí)行。
7娶视、存儲過程與自定義函數(shù)的區(qū)別
- 存儲過程實現(xiàn)的功能要復(fù)雜一些晒哄，而函數(shù)的針對性更強；
- 存儲過程可以返回多個值，而函數(shù)只能有一個返回值揩晴；
- 存儲過程一般獨立的執(zhí)行勋陪，而函數(shù)可以作為其他SQL語句的組成部分出現(xiàn)。
8硫兰、MySQL存儲過程的SQL語句匯總：
-
創(chuàng)建存儲過程
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,……]])
[characteristic ……] routine_body
調(diào)用存儲過程
CALL sp_name([parameter[,……]])
CALL sp_name[()]
區(qū)別：如果存儲過程在封裝時沒有參數(shù)诅愚，“()”有或沒有都可以；但是當存儲過程帶有參數(shù)劫映，就必須有“()”违孝。簡單修改存儲過程
ALTER PROCEDURE sp_name [characteristic ……]
COMMENT ' STRING ':
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }刪除存儲過程
DROP PROCEDURE [IF EXISTS] sp_name
版權(quán)聲明：歡迎轉(zhuǎn)載，歡迎擴散泳赋，但轉(zhuǎn)載時請標明作者以及原文出處雌桑，謝謝合作！ ↓↓↓
