MYSQL存儲(chǔ)過(guò)程技術(shù)

1MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024存儲(chǔ)過(guò)程的概念為什么使用存儲(chǔ)過(guò)程基本創(chuàng)建、刪除語(yǔ)法存儲(chǔ)過(guò)程中的變量BEGIN...END復(fù)合語(yǔ)句流程控制結(jié)構(gòu)語(yǔ)句注釋語(yǔ)法使用權(quán)限條件和異常處理程序游標(biāo)2MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024存儲(chǔ)過(guò)程的概念：所謂的存儲(chǔ)過(guò)程就是存儲(chǔ)在數(shù)據(jù)庫(kù)當(dāng)中的可以執(zhí)行特定工作(查詢和更新)的一組SQL代碼的程序段。

與自定義函數(shù)的區(qū)別：自定義函數(shù)有且只有一個(gè)返回值，就像普通的函數(shù)一樣，可以直接在表達(dá)式中嵌入調(diào)用。

存儲(chǔ)過(guò)程可以沒(méi)有返回值，也可以有任意個(gè)輸出參數(shù)，必須單獨(dú)調(diào)用。

執(zhí)行的本質(zhì)都一樣。只是函數(shù)有如只能返回一個(gè)變量的限制。而存儲(chǔ)過(guò)程可以返回多個(gè)。而函數(shù)是可以嵌入在sql中使用的，可以在select中調(diào)用，而存儲(chǔ)過(guò)程不行。函數(shù)限制比較多，比如不能用臨時(shí)表，只能用表變量。還有一些函數(shù)都不可用等等。而存儲(chǔ)過(guò)程的限制相對(duì)就比較少。

一般來(lái)說(shuō)，存儲(chǔ)過(guò)程實(shí)現(xiàn)的功能要復(fù)雜一點(diǎn)，而函數(shù)的實(shí)現(xiàn)的功能針對(duì)性比較強(qiáng)。對(duì)于存儲(chǔ)過(guò)程來(lái)說(shuō)可以返回參數(shù)，而函數(shù)只能返回值或者表對(duì)象。存儲(chǔ)過(guò)程一般是作為一個(gè)獨(dú)立的部分來(lái)執(zhí)行，而函數(shù)可以作為查詢語(yǔ)句的一個(gè)部分來(lái)調(diào)用，由于函數(shù)可以返回一個(gè)表對(duì)象，因此它可以在查詢語(yǔ)句中位于FROM關(guān)鍵字的后面。3MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024為什么使用存儲(chǔ)過(guò)程：存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：

存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，而一般SQL語(yǔ)句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行速度。

當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)（如對(duì)多個(gè)表進(jìn)行Update、Insert、Query、Delete時(shí)），可將此復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用。

存儲(chǔ)過(guò)程可以重復(fù)使用，可減少數(shù)據(jù)庫(kù)開發(fā)人員的工作量。

安全性高，可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)。4MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024基本的創(chuàng)建、刪除語(yǔ)法：CREATEPROCEDUREsp_name([proc_parameter[,...]])

[characteristic...]routine_body

默認(rèn)地，子程序與當(dāng)前數(shù)據(jù)庫(kù)關(guān)聯(lián)。要明確地把子程序與一個(gè)給定數(shù)據(jù)庫(kù)關(guān)聯(lián)起來(lái)，可以在創(chuàng)建子程序的時(shí)候指定其名字為db_name.sp_name。

sp_name存儲(chǔ)過(guò)程的名字proc_parameter指定參數(shù)為IN，

OUT，或INOUTcharacteristic特征routine_body包含合法的SQL過(guò)程語(yǔ)句。DROP{PROCEDURE|FUNCTION}[IFEXISTS]sp_name這個(gè)語(yǔ)句被用來(lái)移除一個(gè)存儲(chǔ)程序或函數(shù)。即，從服務(wù)器移除一個(gè)制定的子程序。在MySQL5.1中，你必須有ALTERROUTINE權(quán)限才可用此子程序。這個(gè)權(quán)限被自動(dòng)授予子程序的創(chuàng)建者。IFEXISTS子句是一個(gè)MySQL的擴(kuò)展。如果程序或函數(shù)不存在，它防止發(fā)生錯(cuò)誤。5MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024基本的創(chuàng)建、調(diào)用、刪除語(yǔ)法：delimiter//DROPPROCEDUREIFEXISTStest//CREATEPROCEDUREtest

/*存儲(chǔ)過(guò)程名*/(INinparmsINT,OUToutparamsvarchar(32))

/*輸入?yún)?shù)*/BEGIN/*語(yǔ)句塊頭*/DECLAREvarCHAR(10);/*變量聲明*/IFinparms=1THEN/*IF條件開始*/SETvar='hello';/*賦值*/ELSESETvar='world';ENDIF;/*IF結(jié)束*/

INSERTINTOt1VALUES(var);

/*SQL語(yǔ)句*/SELECTnameFROMt1LIMIT1INTOoutparams;END//delimiter;

calltest(1,@out);6MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024存儲(chǔ)過(guò)程的變量：聲明變量：DECLAREvar_name[,...]type[DEFAULTvalue]這個(gè)語(yǔ)句被用來(lái)聲明局部變量。要給變量提供一個(gè)默認(rèn)值，需要包含一個(gè)DEFAULT子句。值可以被指定為一個(gè)表達(dá)式，不需要為一個(gè)常數(shù)。如果沒(méi)有DEFAULT子句，初始值為NULL。

局部變量的作用范圍在它被聲明的BEGIN...END塊內(nèi)。它可以被用在嵌套的塊中，除了那些用相同名字聲明變量的塊。

變量賦值，SET語(yǔ)句：SETvar_name=expr[,var_name=expr]...也可以用語(yǔ)句代替SET來(lái)為用戶變量分配一個(gè)值。在這種情況下，分配符必須為:=而不能用=，因?yàn)樵诜荢ET語(yǔ)句中=被視為一個(gè)比較操作符，如下所示：

mysql>SET@t1=0,@t2=0,@t3=0;

mysql>SELECT@t1:=0,@t2:=0,@t3:=0;

對(duì)于使用select語(yǔ)句為變量賦值的情況，若返回結(jié)果為空，即沒(méi)有記錄，此時(shí)變量的值為上一次變量賦值時(shí)的值，如果沒(méi)有對(duì)變量賦過(guò)值，則為NULL。變量賦值，SELECT...INTO語(yǔ)句SELECTcol_name[,...]INTOvar_name[,...]table_expr這個(gè)SELECT語(yǔ)法把選定的列直接存儲(chǔ)到變量。因此，只有單一的行可以被取回。SELECTid,dataINTOx,yFROMtest.t1LIMIT1;7MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024BEGIN...END復(fù)合語(yǔ)句：

[begin_label:]BEGIN[statement_list]END[end_label]

存儲(chǔ)子程序可以使用BEGIN...END復(fù)合語(yǔ)句來(lái)包含多個(gè)語(yǔ)句。statement_list代表一個(gè)或多個(gè)語(yǔ)句的列表。statement_list之內(nèi)每個(gè)語(yǔ)句都必須用分號(hào)（；）來(lái)結(jié)尾。

復(fù)合語(yǔ)句可以被標(biāo)記。除非begin_label存在，否則end_label不能被給出，并且如果二者都存在，他們必須是同樣的。

使用多重語(yǔ)句需要客戶端能發(fā)送包含語(yǔ)句定界符;的查詢字符串。這個(gè)符號(hào)在命令行客戶端被用delimiter命令來(lái)處理。改變查詢結(jié)尾定界符;（比如改變?yōu)?/）使得;可被用在子程序體中。8MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024流程控制結(jié)構(gòu)語(yǔ)句：

IF語(yǔ)句IFsearch_conditionTHENstatement_list

[ELSEIFsearch_conditionTHENstatement_list]...

[ELSEstatement_list]ENDIF

IF實(shí)現(xiàn)了一個(gè)基本的條件構(gòu)造。如果search_condition求值為真，相應(yīng)的SQL語(yǔ)句列表被執(zhí)行。如果沒(méi)有search_condition匹配，在ELSE子句里的語(yǔ)句列表被執(zhí)行。statement_list可以包括一個(gè)或多個(gè)語(yǔ)句。

舉例：DELIMITER//CREATEPROCEDUREp1(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;IFvariable1=0THENINSERTINTOtVALUES(17);ENDIF;IFparameter1=0THENUPDATEtSETs1=s1+1;ELSEUPDATEtSETs1=s1+2;ENDIF;END;//DELIMITER;9MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024流程控制結(jié)構(gòu)語(yǔ)句：

CASE語(yǔ)句CASEcase_value

WHENwhen_valueTHENstatement_list

[WHENwhen_valueTHENstatement_list]...

[ELSEstatement_list]ENDCASE

Or:CASE

WHENsearch_conditionTHENstatement_list

[WHENsearch_conditionTHENstatement_list]...

[ELSEstatement_list]ENDCASE

存儲(chǔ)程序的CASE語(yǔ)句實(shí)現(xiàn)一個(gè)復(fù)雜的條件構(gòu)造。如果search_condition

求值為真，相應(yīng)的SQL被執(zhí)行。如果沒(méi)有搜索條件匹配，在ELSE子句里的語(yǔ)句被執(zhí)行。舉例：CREATEPROCEDUREp2(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;CASEvariable1WHEN0THENINSERTINTOtVALUES(17);WHEN1THENINSERTINTOtVALUES(18);ELSEINSERTINTOtVALUES(19);ENDCASE;END;//10MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024流程控制結(jié)構(gòu)語(yǔ)句：循環(huán)語(yǔ)句WHILE…ENDWHILELOOP…ENDLOOPREPEAT…ENDREPEATGOTO前三種是標(biāo)準(zhǔn)的循環(huán)方式，至于GOTO就如C語(yǔ)言里的GOTO一樣，盡量少用！在循環(huán)中還穿插一些循環(huán)控制語(yǔ)句，如LEAVE（類似C語(yǔ)言的break）、ITERATE（類似C語(yǔ)言的continue）等。

LEAVE語(yǔ)句LEAVElabel

這個(gè)語(yǔ)句被用來(lái)退出任何被標(biāo)注的流程控制構(gòu)造。它和BEGIN...END或循環(huán)一起被使用。ITERATE語(yǔ)句ITERATElabel

ITERATE只可以出現(xiàn)在LOOP,REPEAT,和WHILE語(yǔ)句內(nèi)。ITERATE意思為：再次循環(huán)。11MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024流程控制結(jié)構(gòu)語(yǔ)句：循環(huán)語(yǔ)句WHILE…ENDWHILE舉例：

CREATEPROCEDUREp4()BEGINDECLAREvINT;SETv=0;WHILEv<5DOINSERTINTOtVALUES(v);SETv=v+1;ENDWHILE;END;//12MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024流程控制結(jié)構(gòu)語(yǔ)句：循環(huán)語(yǔ)句LOOP…ENDLOOP舉例：CREATEPROCEDUREp5()BEGINDECLAREvINT;SETv=0;loop_label:LOOPINSERTINTOtVALUES(v);SETv=v+1;IFv>=5THENLEAVEloop_label;ENDIF;ENDLOOP;END;//[begin_label:]LOOP

statement_listENDLOOP[end_label]LOOP允許某特定語(yǔ)句或語(yǔ)句群的重復(fù)執(zhí)行，實(shí)現(xiàn)一個(gè)簡(jiǎn)單的循環(huán)構(gòu)造。在循環(huán)內(nèi)的語(yǔ)句一直重復(fù)直到循環(huán)被退出，退出通常伴隨著一個(gè)LEAVE語(yǔ)句。13MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024流程控制結(jié)構(gòu)語(yǔ)句：循環(huán)語(yǔ)句REPEAT…ENDREPEAT舉例：CREATEPROCEDUREp6()BEGINDECLAREvINT;SETv=0;REPEATINSERTINTOtVALUES(v);SETv=v+1;UNTILv>=5ENDREPEAT;END;//功能與WHILE差不多，差別是在執(zhí)行一次后檢查，而WHILE是在開始時(shí)檢查，累死DO…WHILE功能。14MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024流程控制結(jié)構(gòu)語(yǔ)句：補(bǔ)充：迭代（ITERATE）語(yǔ)句CREATEPROCEDUREp7()BEGINDECLAREvINT;SETv=0;loop_label:LOOPIFv=3THENSETv=v+1;ITERATEloop_label;ENDIF;INSERTINTOtVALUES(v);SETv=v+1;IFv>=5THENLEAVEloop_label;ENDIF;ENDLOOP;END;//15MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024注釋語(yǔ)法：mysql存儲(chǔ)過(guò)程可使用兩種風(fēng)格的注釋雙模杠：--，該風(fēng)格一般用于單行注釋c風(fēng)格：/*注釋內(nèi)容*/，一般用于多行注釋使用權(quán)限：關(guān)于存儲(chǔ)過(guò)程的權(quán)限管理，一種是SQLSECURITYINVOKER，一種是SQLSECURITYDEFINER,也就是一種是調(diào)用者權(quán)限，一種是定義者權(quán)限，如果使用第一種，那么執(zhí)行的時(shí)候是以執(zhí)行者本身的權(quán)限來(lái)操作存儲(chǔ)過(guò)程中包含的表。如果是第二種，那么執(zhí)行的時(shí)候，是以該存儲(chǔ)過(guò)程的定義者權(quán)限來(lái)操作。16MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024條件和異常處理程序：DECLAREhandler_typeHANDLERFORcondition_value[,...]sp_statement

handler_type:

CONTINUE

|EXIT

condition_value:

SQLSTATE[VALUE]sqlstate_value

|condition_name

|SQLWARNING

|NOTFOUND

|SQLEXCEPTION這個(gè)語(yǔ)句指定每個(gè)可以處理一個(gè)或多個(gè)條件的處理程序。如果產(chǎn)生一個(gè)或多個(gè)條件，指定的語(yǔ)句被執(zhí)行。對(duì)一個(gè)CONTINUE處理程序，當(dāng)前子程序的執(zhí)行在執(zhí)行處理程序語(yǔ)句之后繼續(xù)。對(duì)于EXIT處理程序，當(dāng)前BEGIN...END復(fù)合語(yǔ)句的執(zhí)行被終止。UNDO處理程序類型語(yǔ)句還不被支持。SQLWARNING是對(duì)所有以01開頭的SQLSTATE代碼的速記。NOTFOUND是對(duì)所有以02開頭的SQLSTATE代碼的速記。SQLEXCEPTION是對(duì)所有沒(méi)有被SQLWARNING或NOTFOUND捕獲的SQLSTATE代碼的速記。聲明自定義條件：DECLAREcondition_nameCONDITIONFORcondition_valuecondition_value:SQLSTATE[VALUE]sqlstate_value17MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024條件和異常處理程序：舉例：CREATETABLEtest.t(s1int,primarykey(s1));delimiter//CREATEPROCEDUREhandlerdemo()BEGINDECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@x2=1;

SET@x=1;INSERTINTOtest.tVALUES(1);SET@x=2;INSERTINTOtest.tVALUES(1);SET@x=3;END;//delimiter;18MYSQL存儲(chǔ)過(guò)程技術(shù)5/9/2024游標(biāo)：聲明游標(biāo)DECLAREcursor_nameCURSORFORselect_statement這個(gè)語(yǔ)句聲明一個(gè)光標(biāo)。也可以在子程序中定義多個(gè)光標(biāo)，但是一個(gè)塊中的每一個(gè)光標(biāo)必須有唯一的名字。打開游標(biāo)OPENcursor_name這個(gè)語(yǔ)句打開先前聲明的光標(biāo)。游標(biāo)FETCHFETCHcursor_nameINTOvar_name[,var_name]...這個(gè)語(yǔ)句用指定的打開光標(biāo)讀取下一行（如果有下一行的話），并且前進(jìn)光標(biāo)指針。

關(guān)閉游標(biāo)CLOSECLOSEcursor_name這個(gè)語(yǔ)句關(guān)閉先前打開的光標(biāo)。