MySQL存儲過程實例教程

1、(1)MySQL存儲過程是在“命令提示符”下創(chuàng)建的，所以首先應(yīng)該打開“命令提示符”窗MySQL存儲過程實例教程MySQL5.0以后的版本開始支持存儲過程，存儲過程具有一致性、高效性、安全性和體系結(jié)構(gòu)等特點，本節(jié)將通過具體的實例講解PHP是如何操縱MySQL存儲過程的。實例261：存儲過程的創(chuàng)建這是一個創(chuàng)建存儲過程的實例錄像位置：光盤mingrisoft9?lt;/p實例說明為了保證數(shù)據(jù)的完整性、一致性，提高應(yīng)用的性能，常采用存儲過程技術(shù)。MySQL5.0之前的版本并不支持存儲過程，隨著MySQL技術(shù)的日趨完善，存儲過程將在以后的項目中得到廣泛的應(yīng)用。本實例將介紹在MySQL5.0以后的版本中創(chuàng)

2、建存儲過程。技術(shù)要點一個存儲過程包括名字、參數(shù)列表，以及可以包括很多SQL語句的SQL語句集。下面為一個存儲過程的定義過程：createprocedureproc_name(inparameterinteger)begindeclarevariablevarchar(20);ifparameter=1thensetvariable=MySQL;elsesetvariable=PHP;endif;insertintotb(name)values(variable);end;MySQL中存儲過程的建立以關(guān)鍵字createprocedure開始，后面緊跟存儲過程的名稱和參數(shù)。MySQL的存儲過程名稱

3、不區(qū)分大小寫，例如PROCE1O和procel()代表同一個存儲過程名。存儲過程名不能與MySQL數(shù)據(jù)庫中的內(nèi)建函數(shù)重名。存儲過程的參數(shù)一般由3部分組成。第一部分可以是in、out或inout。in表示向存儲過程中傳入?yún)?shù)；out表示向外傳出參數(shù)；inout表示定義的參數(shù)可傳入存儲過程，并可以被存儲過程修改后傳出存儲過程，存儲過程默認(rèn)為傳入?yún)?shù)，所以參數(shù)in可以省略。第二部分為參數(shù)名。第三部分為參數(shù)的類型，該類型為MySQL數(shù)據(jù)庫中所有可用的字段類型，如果有多個參數(shù)，參數(shù)之間可以用逗號進(jìn)行分割。MySQL存儲過程的語句塊以begin開始，以end結(jié)束。語句體中可以包含變量的聲明、控制語句、SQ

4、L查詢語句等。由于存儲過程內(nèi)部語句要以分號結(jié)束，所以在定義存儲過程前應(yīng)將語句結(jié)束標(biāo)志“;”更改為其他字符，并且該字符在存儲過程中出現(xiàn)的幾率也應(yīng)該較低，可以用關(guān)鍵字delimiter更改。例如：mysqldelimiter/存儲過程創(chuàng)建之后，可用如下語句進(jìn)行刪除，參數(shù)proc_name指存儲過程名。dropprocedureproc_name實現(xiàn)過程(1)MySQL存儲過程是在“命令提示符”下創(chuàng)建的，所以首先應(yīng)該打開“命令提示符”窗口。(2)進(jìn)入“命令提示符”窗口后，首先應(yīng)該登錄MySQL數(shù)據(jù)庫服務(wù)器，在“命令提示符”下輸入如下命令：mysql-u用戶名-p用戶密碼(3)更改語句結(jié)束符號，本實例

5、將語句結(jié)束符更改為“/”。代碼如下：delimiter/創(chuàng)建存儲過程前應(yīng)首先選擇某個數(shù)據(jù)庫。代碼如下：use數(shù)據(jù)庫名創(chuàng)建存儲過程。通過call語句調(diào)用存儲過程。實例的關(guān)鍵技術(shù)是如何創(chuàng)建傳入?yún)?shù)的存儲過程，具體實現(xiàn)代碼如下：delimiter/createprocedurepro_reg(inncvarchar(50),inpwdvarchar(50),inemailvarchar(50),inaddressvarchar(50)begininsertintotb_reg(name,pwd,email,address)values(nc,pwd,email,address);end;/“deli

6、miter/”的作用是將語句結(jié)束符更改為“/”。“inncvarchar(50)inaddressvarchar(50)”表示要向存儲過程中傳入的參數(shù)。實現(xiàn)過程通過PHP預(yù)定義類mysqli，實現(xiàn)與MySQL數(shù)據(jù)庫的連接。代碼如下：$conn=newmysqli(localhost,root,root,db_database09);$conn-query(setnamesgb2312);調(diào)用存儲過程pro_reg，實現(xiàn)將用戶錄入的注冊信息保存到數(shù)據(jù)庫。代碼如下：if($sql=$conn-query(callpro_reg(.$nc.,.$pwd.,.$email.,.$address.)ec

7、hoSCRIPTalert(用戶注冊成功！)；/SCRIPT；elseechoSCRIPTalert(用戶注冊失敗!);MySQL5.0參考手冊中關(guān)于創(chuàng)建存儲過程的語法說明：(1)MySQL存儲過程是在“命令提示符”下創(chuàng)建的，所以首先應(yīng)該打開“命令提示符”窗CREATEDEFINER=user|CURRENT_USERPROCEDUREsp_name(proc_parameter,.)characteristic.routine_bodyproc_parameter:IN|OUT|INOUTparam_nametypetype:AnyvalidMySQLdatatypecharacterist

8、ic:LANGUAGESQL|NOTDETERMINISTIC|CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA|SQLSECURITYDEFINER|INVOKER|COMMENTstringroutine_body:ValidSQLprocedurestatement如果你對MySQL還不太熟悉的話，單單看這個語法結(jié)構(gòu)當(dāng)然不足以進(jìn)行MySQL存儲過程編程。我之前基本都是使用MSSQLSERVER,所以以下記錄我熟悉MySQL存儲過程的過程，也是重點介紹MSSQLSERVER與MySQL區(qū)別較大的地方。第一步，當(dāng)然是寫個HelloWord的存儲過程

9、，如下：CREATEPROCEDUREphelloword()BEGINSELECTHelloWord!ASF;END;將上面創(chuàng)建phelloword存儲過程的語句拷到phpMyAdmin中執(zhí)行，報如下錯誤：將上面創(chuàng)建phelloword存儲過程的語句拷到phpMyAdmin中執(zhí)行，報如下錯誤：#1064-YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenearatline3在這個問題上我糾纏了很久，在MySQL的命令行工具中執(zhí)行同樣

10、不成功，但是根據(jù)提示信息可以知道執(zhí)行在SELECTHelloWord!ASF;處結(jié)束，后面的END;沒有執(zhí)行，這顯然會導(dǎo)致錯誤。這里需要選擇以個分隔符，語法如下：DELIMITER/分隔符是通知MySQL客戶端已經(jīng)輸入完成的符號。一直都是用“；”，但是在存儲過程中不行，因為存儲過程中很多語句都需要用到分號。因此上面的存儲過程改為：CREATEPROCEDUREptest()BEGINSELECTHelloWord!ASF;END/，這次存儲過程即可創(chuàng)建成另外在phpMyAdmin中執(zhí)行時，在Delimiter文本框中填寫功。第二步，寫一個包括參數(shù)，變量，變量賦值，條件判斷，集的完整的一個存儲過

11、程，如下：CREATEPROCEDUREplogin(UPDATE語句，SELECT返回結(jié)果將上面創(chuàng)建phelloword存儲過程的語句拷到phpMyAdmin中執(zhí)行，報如下錯誤：將上面創(chuàng)建phelloword存儲過程的語句拷到phpMyAdmin中執(zhí)行，報如下錯誤：將上面創(chuàng)建phelloword存儲過程的語句拷到phpMyAdmin中執(zhí)行，報如下錯誤：將上面創(chuàng)建phelloword存儲過程的語句拷到phpMyAdmin中執(zhí)行，報如下錯誤：p_usernamechar(15),p_passwordchar(32),p_ipchar(18),p_logintimedatetime)LABEL_P

12、ROC:BEGINDECLAREv_uidmediumint(8);DECLAREv_realpasswordchar(32);DECLAREv_nicknamevarchar(30);DECLAREv_oltimesmallint(6);v_realpassword,u.uidWHERESELECTu.uid,u.password,f.nickname,u.oltimeINTOv_uid,v_nickname,v_oltimeFROMcdb_membersuINNERJOINcdb_memberfieldsfONf.uidu.username=p_username;IF(v_uidISNUL

13、L)THENSELECT2ASErrorCode;LEAVELABEL_PROC;ENDIF;IF(p_passwordv_realpassword)THENSELECT3ASErrorCode;LEAVELABEL_PROC;ENDIF;UPDATEipsp_userexpandsSETlastloginip=p_ip,lastlogintime=p_logintimeWHEREuid=v_uid;SELECT0ASErrorCode,v_uidASuid,v_nicknameASnickname,v_oltimeASoltime;ENDLABEL_PROC/首先要說的是給變量賦值的語法，M

14、ySQL中使用SELECTu.uid,u.password,f.nickname,u.oltimeINTOv_uid,v_realpassword,v_nickname,v_oltimeFROMcdb_membersuINNERJOINcdb_memberfieldsfONf.uid=u.uidWHEREu.username=p_username;這種方式給變量賦值。其次是條件判斷的語法結(jié)構(gòu)，如下所示：IF.THENELSEIF.THENELSEIFELSEENDIF;ENDIF;SQL時，在MSSQLSERVERLEAVEDOPROC:”然后再需要用到最后說說LEAVE語法的使用。當(dāng)滿足某種

15、條件，不繼續(xù)執(zhí)行下面的中使用RETURN語法，在MySQL中我沒有找到對應(yīng)的關(guān)鍵字，但是這里可以利用來滿足要求，在存儲過程的BEGIN前定義一個標(biāo)簽，如：“LABELRETURN中斷執(zhí)行的地方執(zhí)行“LEAVELABEL_PROC;”即可。第三步，創(chuàng)建一個執(zhí)行動態(tài)SQL的存儲過程。CREATEPROCEDUREipsp_getresourcedir(p_hashcodechar(40)LABEL_PROC:BEGINDECLAREv_sqlvarchar(200);SETv_sql=CONCAT(SELECTfiledirFROMipsp_resourcesWHEREhashcode=,p_ha

16、shcode,LIMIT0,1);SETsql=v_sql;PREPAREslFROMsql;EXECUTEsl;DEALLOCATEPREPAREsl;ENDLABEL_PROC/mysql存儲過程的創(chuàng)建，刪除，調(diào)用及其他常用命令mysql5.0存儲過程學(xué)習(xí)總結(jié)創(chuàng)建存儲過程基本語法：createproceduresp_name()beginend參數(shù)傳遞調(diào)用存儲過程1.基本語法：callsp_name()注意：存儲過程名稱后面必須加括號，哪怕該存儲過程沒有參數(shù)傳遞刪除存儲過程1.基本語法：dropproceduresp_name/2.注意事項(1)不能在一個存儲過程中刪除另一個存儲過程，只能

17、調(diào)用另一個存儲過程區(qū)塊，條件，循環(huán)1.區(qū)塊定義，常用beginend;也可以給區(qū)塊起別名，如：lable:begin.endlable;可以用leavelable;跳出區(qū)塊，執(zhí)行區(qū)塊以后的代碼2.條件語句if條件thenstatementelsestatementendif;循環(huán)語句.while循環(huán)label:WHILEexpressionDOstatementsENDWHILElabel;.loop循環(huán)label:LOOPstatementsENDLOOPlabel;.repeatuntil循環(huán)label:REPEATstatementsUNTILexpressionENDREPEATlab

18、el;五.其他常用命令1.showprocedurestatus創(chuàng)建時間等顯示數(shù)據(jù)庫中所有存儲的存儲過程基本信息，包括所屬數(shù)據(jù)庫，存儲過程名稱，2.showcreateproceduresp_name顯示某一個存儲過程的詳細(xì)信息mysql存儲過程中要用到的運(yùn)算符mysql存儲過程學(xué)習(xí)總結(jié)U操作符算術(shù)運(yùn)算符+加SETvar1=2+2;4-減SETvar2=3-2;1*乘SETvar3=3*2;6/除SETvar4=10/3;3.3333DIV整除SETvar5=10DIV3;3%取模SETvar6=10%3;1比較運(yùn)算符大于12False小于21False=小于等于2=大于等于3=2TrueBE

19、TWEEN在兩值之間5BETWEEN1AND10TrueNOTBETWEEN不在兩值之間5NOTBETWEEN1AND10FalseIN在集合中5IN(1,2,3,4)FalseNOTIN不在集合中5NOTIN(1,2,3,4)True=等于2=3False,!=不等于23False嚴(yán)格比較兩個NULL值是否相等NULL二NULLTrueLIKE簡單模式匹配GuyHarrisonLIKEGuy%TrueREGEXP正則式匹配GuyHarrisonREGEXPGgregFalseISNULL為空0ISNULLFalseISNOTNULL不為空0ISNOTNULLTrue邏輯運(yùn)算符與(AND)AN

20、DTRUEFALSENULLTRUETRUEFALSENULLFALSEFALSEFALSENULLNULLNULLNULLNULL或(OR)ORTRUEFALSENULLTRUETRUETRUETRUEFALSETRUEFALSENULLNULLTRUENULLNULL異或(XOR)XORTRUEFALSENULLTRUEFALSETRUENULLFALSETRUEFALSENULLNULLNULLNULLNULL位運(yùn)算符|位或&位與右移位位非（單目運(yùn)算，按位取反）mysq存儲過程中常用的函數(shù)，字符串類型操作，數(shù)學(xué)類，日期時間類。mysql存儲過程基本函數(shù)字符串類CHARSET(str)/返

21、回字串字符集CONCAT(string2,.)/連接字串INSTR(string,substring)/返回substring首次在string中出現(xiàn)的位置，不存在返回0LCASE(string2)/轉(zhuǎn)換成小寫LEFT(string2,length)/從string2中的左邊起取length個字符LENGTH(string)/string長度LOAD_FILE(file_name)/從文件讀取內(nèi)容LOCATE(substring,string,start_position)同INSTR,但可指定開始位置LPAD(string2,length,pad)/ODOpad加在string開頭，直到字串

22、長度為lengthLTRIM(string2)/去除前端空格REPEAT(string2,count)/ODcount次REPLACE(str,search_str,replace_str)/在str中用replace_str替換search_strRPAD(string2,length,pad)/在str后用padDO，直到長度為lengthRTRIM(string2)/去除后端空格TOC o 1-5 h zSTRCMP(string1,string2)/逐字符比較兩字串大小,SUBSTRING(str,position,length)/從str的position開始，取length個字符,

23、注：mysql中處理字符串時，默認(rèn)第一個字符下標(biāo)為1,即參數(shù)position必須大于等于1mysqlselectsubstring(abcd,0,2);+-+|substring(abcd,0,2)|+-+|+-+1rowinset(0.00sec)mysqlselectsubstring(abcd,1,2);+-+|substring(abcd,1,2)|+-+|ab|+-1rowinset(0.02sec)TRIM(BOTH|LEADING|TRAILINGpaddingFROMstring2)/去除指定位置的指定字符UCASE(string2)/轉(zhuǎn)換成大寫RIGHT(string2,le

24、ngth)/取string2最后length個字符SPACE(count)/生成count個空格數(shù)學(xué)類ABS(number2)/絕對值BIN(decimal_number)/十進(jìn)制轉(zhuǎn)二進(jìn)制CEILING(number2)/向上取整CONV(number2,from_base,to_base)/進(jìn)制轉(zhuǎn)換FLOOR(number2)/向下取整FORMAT(number,decimal_places)/保留小數(shù)位數(shù)HEX(DecimalNumber)/轉(zhuǎn)十六進(jìn)制注：HEX()中可傳入字符串，則返回其ASC-11碼，如HEX(DEF)返回4142143也可以傳入十進(jìn)制整數(shù)，返回其十六進(jìn)制編碼，如HEX

25、(25)返回19LEAST(number,number2,.)/求最小值MOD(numerator,denominator)/求余POWER(number,power)/求指數(shù)RAND(seed)/隨機(jī)數(shù)ROUND(numberdecimals)/四舍五入decimals為小數(shù)位數(shù)注：返回類型并非均為整數(shù)，如：(1)默認(rèn)變?yōu)檎沃祄ysqlselectround(1.23);TOC o 1-5 h z+-+|round(1.23)|+-+|1|+-+1rowinset(0.00sec)mysqlselectround(1.56);+-+|round(1.56)|+-+|2|+-+1rowinset(0.00sec)(2)可以設(shè)定小數(shù)位數(shù)，返回浮點型數(shù)據(jù)mysqlselectround(1.567,2);+-+|round(1.567,2)|+-+|1.57|+-+1rowinset(0.00sec)SIGN(number2)/返回符號,正負(fù)或0SQRT(number2)/開平方日期時間類ADDTIME(date2,time_interval)/將time_interval加到date2CONVERT_TZ(datetime2,fromTZ,toTZ)/轉(zhuǎn)換時區(qū)CURRENT_DATE()/當(dāng)前日期CURRENT_TIME()/當(dāng)前時間