MySQL存儲(chǔ)過(guò)程詳解
本文轉(zhuǎn)載自微信公眾號(hào)「SQL數(shù)據(jù)庫(kù)開(kāi)發(fā)」，作者丶平凡世界。轉(zhuǎn)載本文請(qǐng)聯(lián)系SQL數(shù)據(jù)庫(kù)開(kāi)發(fā)公眾號(hào)。
經(jīng)常有小伙伴問(wèn)我這個(gè)存儲(chǔ)過(guò)程該如何寫?作為過(guò)來(lái)人我剛開(kāi)始也有這樣的苦惱，今天就給大家說(shuō)說(shuō)這個(gè)存儲(chǔ)過(guò)程該如何創(chuàng)建和使用。
什么是存儲(chǔ)過(guò)程
存儲(chǔ)過(guò)程是一組可編程的函數(shù)，是為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫(kù)中，用戶可通過(guò)指定存儲(chǔ)過(guò)程的名字并給定參數(shù)(需要時(shí))來(lái)調(diào)用執(zhí)行。
關(guān)鍵詞：可編程，特定功能，調(diào)用
創(chuàng)建存儲(chǔ)過(guò)程
我們以表customers為例，通過(guò)傳遞客戶ID的值來(lái)查詢客戶的具體信息：
表customers
示例：
- CREATE PROCEDURE sp_customers(IN cusid INT)
- BEGIN
- SELECT * FROM customers WHERE `客戶ID`=cusid;
- END;
上面這是一個(gè)比較簡(jiǎn)單的存儲(chǔ)過(guò)程，主要的功能就是用來(lái)查詢客戶信息。這里我們先簡(jiǎn)單解釋一下：CREATE PROCEDURE：這是創(chuàng)建存儲(chǔ)過(guò)程的關(guān)鍵字，屬固定語(yǔ)法。sp_customers：這是存儲(chǔ)過(guò)程名稱，當(dāng)我們執(zhí)行了該存儲(chǔ)過(guò)程后，系統(tǒng)就會(huì)出現(xiàn)一個(gè)該名稱的存儲(chǔ)過(guò)程，可以自定義。IN：這是輸入?yún)?shù)的意思，當(dāng)然也有輸出參數(shù)關(guān)鍵字OUT，同時(shí)也可以不定義參數(shù)，直接讓參數(shù)為空。cusid INT：這是定義參數(shù)名和類型，這里我們定義了一個(gè)名為cusid，類型為INT的參數(shù)名。BEGIN ... END ：這是存儲(chǔ)過(guò)程過(guò)程體的固定語(yǔ)法，你需要執(zhí)行的SQL功能就寫在這中間。
調(diào)用存儲(chǔ)過(guò)程
上面我們創(chuàng)建好了存儲(chǔ)過(guò)程以后，就可以調(diào)用了。調(diào)用存儲(chǔ)過(guò)程的語(yǔ)法很簡(jiǎn)單：
- CALL sp_name([參數(shù)])
下面我們來(lái)調(diào)用上面的存儲(chǔ)過(guò)程sp_customers
- CALL sp_customers(1);
解釋：
上面的代碼的意思就是將客戶ID為1的數(shù)據(jù)，傳遞給存儲(chǔ)過(guò)程sp_customers，通過(guò)CALL來(lái)調(diào)用該存儲(chǔ)過(guò)程來(lái)執(zhí)行。
結(jié)果為
細(xì)心的小伙伴可能已經(jīng)發(fā)現(xiàn)了，這不就是一個(gè)簡(jiǎn)單的WHERE查詢語(yǔ)句嗎?是的，剛開(kāi)始使用存儲(chǔ)過(guò)程時(shí)，其實(shí)不必把它神秘化，你越覺(jué)得它神秘越會(huì)覺(jué)得難以熟練使用。復(fù)雜的東西先簡(jiǎn)單化，方可更進(jìn)一步掌握。
過(guò)程體
- 過(guò)程體即我們?cè)谡{(diào)用時(shí)必須執(zhí)行的SQL語(yǔ)句，上面的SELECT查詢即為一個(gè)簡(jiǎn)單的過(guò)程體。
- 過(guò)程體包含DML、DDL語(yǔ)句，if-then-else和while-do語(yǔ)句、聲明變量的declare語(yǔ)句等
- 過(guò)程體的格式上面也已經(jīng)演示過(guò)，以BEGIN開(kāi)始，以END結(jié)尾(可以嵌套)。
例如：
- BEGIN
- BEGIN
- BEGIN
- -- SQL代碼;
- END
- END
- END
注意：每個(gè)嵌套塊及其中的每條SQL語(yǔ)句，必須以分號(hào)(;)結(jié)束。表示過(guò)程體結(jié)束的BEGIN-END塊(又叫做復(fù)合語(yǔ)句compound statement)，即END后面，則不需要分號(hào)。
標(biāo)簽
標(biāo)簽通常是與BEGIN-END一起使用，用來(lái)增強(qiáng)代碼的可讀性。語(yǔ)法為：
- [label_name:] BEGIN
- [statement_list]
- END [label_name]
例如：
- label1: BEGIN
- label2: BEGIN
- label3: BEGIN
- --SQL代碼;
- END label3 ;
- END label2;
- END label1
該功能不常用，了解即可。
存儲(chǔ)過(guò)程的參數(shù)
上面我們大致的說(shuō)了一下存儲(chǔ)過(guò)程參數(shù)定義，下面我們?cè)僭敿?xì)給大家講述參數(shù)該如何使用。參數(shù)類型
- IN輸入?yún)?shù)：表示調(diào)用者向過(guò)程傳入值(傳入值可以是字面量或變量)
- OUT輸出參數(shù)：表示過(guò)程向調(diào)用者傳出值(可以返回多個(gè)值)(傳出值只能是變量)
- INOUT輸入輸出參數(shù)：既表示調(diào)用者向過(guò)程傳入值，又表示過(guò)程向調(diào)用者傳出值(值只能是變量)
IN輸入?yún)?shù)
上面的示例就是一個(gè)輸入?yún)?shù)的示例，這里不贅述。
OUT輸出參數(shù)
- CREATE PROCEDURE sp_customers_out(OUT cusname VARCHAR(20))
- BEGIN
- SELECT cusname;
- SELECT `姓名` INTO cusname FROM customers WHERE `客戶ID`=1;
- SELECT cusname;
- END
調(diào)用上面的存儲(chǔ)過(guò)程：
- CALL sp_customers_out(@cusname);
結(jié)果為：
結(jié)果1
結(jié)果2
上面我們定義了一個(gè)輸出參數(shù)為cusname的參數(shù)(這里參數(shù)類型如果有長(zhǎng)度必須給定長(zhǎng)度)。
然后在過(guò)程體里面，我們輸出了兩次參數(shù)的結(jié)果，結(jié)果1為NULL，是因?yàn)槲覀兊妮敵鰠?shù)cusname還沒(méi)有接收任何值，所以為NULL;結(jié)果2里面有了客戶姓名，是因?yàn)槲覀儗⒖蛻鬒D為1的客戶姓名傳遞給了輸出參數(shù)cusname。
INOUT輸入輸出參數(shù)
這個(gè)不常見(jiàn)，但是也有使用，即同一個(gè)參數(shù)既為輸入?yún)?shù)，也為輸出參數(shù)，我們把上面的存儲(chǔ)過(guò)程稍微修改一下就可以看出區(qū)別了。
- CREATE PROCEDURE sp_customers_inout(INOUT cusname VARCHAR(20))
- BEGIN
- SELECT cusname;
- SELECT `姓名` INTO cusname FROM customers WHERE `客戶ID`=2;
- SELECT cusname;
- END
調(diào)用上述存儲(chǔ)過(guò)程之前我們先給定一個(gè)輸入?yún)?shù)：張三
- SET @cusname='張三';
- CALL sp_customers_inout(@cusname);
結(jié)果為：
結(jié)果1
結(jié)果2
上面我們定義了一個(gè)輸入輸出參數(shù)為cusname的參數(shù)。然后在過(guò)程體里面，我們輸出了兩次參數(shù)的結(jié)果：第一次我們將先定義好的“張三”(SET @cusname='張三')傳遞給參數(shù)cusname，此時(shí)它為輸入?yún)?shù)。進(jìn)入過(guò)程體后首先輸出結(jié)果1為“張三”，此時(shí)參數(shù)cusname為輸出參數(shù);然后通過(guò)查詢將客戶ID為2的客戶姓名再次傳遞給cusname，來(lái)改變它的值，此時(shí)它同樣為輸出參數(shù)，只是輸出結(jié)果發(fā)生了改變。
以上就是三個(gè)參數(shù)的用法，建議：
- 需要輸入值時(shí)使用IN參數(shù);
- 需要返回值時(shí)使用OUT參數(shù);
- INOUT參數(shù)盡量少用。
