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

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

存儲過程可以沒有返回值，也可以有任意個輸出參數(shù)，必須單獨調用。

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

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

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

當對數(shù)據(jù)庫進行復雜操作時（如對多個表進行Update、Insert、Query、Delete時），可將此復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務處理結合一起使用。

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

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

[characteristic...]routine_body

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

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

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

/*存儲過程名*/(INinparmsINT,OUToutparamsvarchar(32))

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

INSERTINTOt1VALUES(var);

/*SQL語句*/SELECTnameFROMt1LIMIT1INTOoutparams;END//delimiter;

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

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

變量賦值，SET語句：SETvar_name=expr[,var_name=expr]...也可以用語句代替SET來為用戶變量分配一個值。在這種情況下，分配符必須為:=而不能用=，因為在非SET語句中=被視為一個比較操作符，如下所示：

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

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

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

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

存儲子程序可以使用BEGIN...END復合語句來包含多個語句。statement_list代表一個或多個語句的列表。statement_list之內每個語句都必須用分號（；）來結尾。

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

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

IF語句IFsearch_conditionTHENstatement_list

[ELSEIFsearch_conditionTHENstatement_list]...

[ELSEstatement_list]ENDIF

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

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

CASE語句CASEcase_value

WHENwhen_valueTHENstatement_list

[WHENwhen_valueTHENstatement_list]...

[ELSEstatement_list]ENDCASE

Or:CASE

WHENsearch_conditionTHENstatement_list

[WHENsearch_conditionTHENstatement_list]...

[ELSEstatement_list]ENDCASE

存儲程序的CASE語句實現(xiàn)一個復雜的條件構造。如果search_condition

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

LEAVE語句LEAVElabel

這個語句被用來退出任何被標注的流程控制構造。它和BEGIN...END或循環(huán)一起被使用。ITERATE語句ITERATElabel

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

CREATEPROCEDUREp4()BEGINDECLAREvINT;SETv=0;WHILEv<5DOINSERTINTOtVALUES(v);SETv=v+1;ENDWHILE;END;//12MYSQL存儲過程技術5/9/2024流程控制結構語句：循環(huán)語句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允許某特定語句或語句群的重復執(zhí)行，實現(xiàn)一個簡單的循環(huán)構造。在循環(huán)內的語句一直重復直到循環(huán)被退出，退出通常伴隨著一個LEAVE語句。13MYSQL存儲過程技術5/9/2024流程控制結構語句：循環(huán)語句REPEAT…ENDREPEAT舉例：CREATEPROCEDUREp6()BEGINDECLAREvINT;SETv=0;REPEATINSERTINTOtVALUES(v);SETv=v+1;UNTILv>=5ENDREPEAT;END;//功能與WHILE差不多，差別是在執(zhí)行一次后檢查，而WHILE是在開始時檢查，累死DO…WHILE功能。14MYSQL存儲過程技術5/9/2024流程控制結構語句：補充：迭代（ITERATE）語句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存儲過程技術5/9/2024注釋語法：mysql存儲過程可使用兩種風格的注釋雙模杠：--，該風格一般用于單行注釋c風格：/*注釋內容*/，一般用于多行注釋使用權限：關于存儲過程的權限管理，一種是SQLSECURITYINVOKER，一種是SQLSECURITYDEFINER,也就是一種是調用者權限，一種是定義者權限，如果使用第一種，那么執(zhí)行的時候是以執(zhí)行者本身的權限來操作存儲過程中包含的表。如果是第二種，那么執(zhí)行的時候，是以該存儲過程的定義者權限來操作。16MYSQL存儲過程技術5/9/2024條件和異常處理程序：DECLAREhandler_typeHANDLERFORcondition_value[,...]sp_statement

handler_type:

CONTINUE

|EXIT

condition_value:

SQLSTATE[VALUE]sqlstate_value

|condition_name

|SQLWARNING

|NOTFOUND

|SQLEXCEPTION這個語句指定每個可以處理一個或多個條件的處理程序。如果產(chǎn)生一個或多個條件，指定的語句被執(zhí)行。對一個CONTINUE處理程序，當前子程序的執(zhí)行在執(zhí)行處理程序語句之后繼續(xù)。對于EXIT處理程序，當前BEGIN...END復合語句的執(zhí)行被終止。UNDO處理程序類型語句還不被支持。SQLWARNING是對所有以01開頭的SQLSTATE代碼的速記。NOTFOUND是對所有以02開頭的SQLSTATE代碼的速記。SQLEXCEPTION是對所有沒有被SQLWARNING或NOTFOUND捕獲的SQLSTATE代碼的速記。聲明自定義條件：DECLAREcondition_nameCONDITIONFORcondition_valuecondition_value:SQLSTATE[VALUE]sqlstate_value17MYSQL存儲過程技術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存儲過程技術5/9/2024游標：聲明游標DECLAREcursor_nameCURSORFORselect_statement這個語句聲明一個光標。也可以在子程序中定義多個光標，但是一個塊中的每一個光標必須有唯一的名字。打開游標OPENcursor_name這個語句打開先前聲明的光標。游標FETCHFETCHcursor_nameINTOvar_name[,var_name]...這個語句用指定的打開光標讀取下一行（如果有下一行的話），并且前進光標指針。

關閉游標CLOSECLOSEcursor_name這個語句關閉先前打開的光標。