mysql存儲過程和函數(shù)

第9講存儲過程與存儲函數(shù)存儲過程和函數(shù)是在數(shù)據(jù)庫中定義一些SQL語句的集合，然后直接調(diào)用這些存儲過程和函數(shù)來執(zhí)行已經(jīng)定義好的SQL語句。存儲過程和函數(shù)可以避免開發(fā)人員重復(fù)的編寫相同的SQL語句。而且，存儲過程和函數(shù)是在MySQL服務(wù)器中存儲和執(zhí)行的，可以減少客戶端和服務(wù)器端的數(shù)據(jù)傳輸。9.1創(chuàng)建存儲過程和函數(shù)創(chuàng)建存儲過程和函數(shù)是指將經(jīng)常使用的一組SQL語句的組合在一起，并將這些SQL語句當(dāng)作一個整體存儲在MySQL服務(wù)器中。例如，銀行經(jīng)常需要計算用戶的利息。不同類別的用戶的利率是不一樣的。這就可以將計算利率的SQL代碼寫成一個存儲過程或者存儲函數(shù)。只要調(diào)用這個存儲過程或者存儲函數(shù)，就可以將不同類別用戶的利息計算出來。9.1.1

創(chuàng)建存儲過程MySQL中，創(chuàng)建存儲過程的基本形式如下：CREATEPROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_bodySp_name:存儲過程的名稱，默認(rèn)在當(dāng)前數(shù)據(jù)庫中創(chuàng)建。這個名稱應(yīng)當(dāng)盡量避免與MySQL的內(nèi)置函數(shù)相同的名稱Proc_parameter:存儲過程的參數(shù)列表格式[IN|OUT|INOUT]param_nametypeParam_name為參數(shù)名，type為參數(shù)的數(shù)據(jù)類型。多個參數(shù)彼此間用逗號分隔。輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)，分別用in/out/inout標(biāo)識。參數(shù)的取名不要與數(shù)據(jù)表的列名相同。Characteristic:存儲過程的某些特征設(shè)定，分別介紹1COMMENT’string’:用于對存儲過程的描述，其中string為描述內(nèi)容,comment為關(guān)鍵字。2LANGUAGESQL:指明編寫這個存儲過程的語言為SQL語言。這個選項可以不指定。3DETERMINISTIC:表示存儲過程對同樣的輸入?yún)?shù)產(chǎn)生相同的結(jié)果;NOTDETERMINISTIC，則表示會產(chǎn)生不確定的結(jié)果（默認(rèn)）。4containssql|nosql|readssqldata|modifiessqldataContainssql表示存儲過程包含讀或?qū)憯?shù)據(jù)的語句（默認(rèn)）Nosql表示不包含sql語句Readssqldata表示存儲過程只包含讀數(shù)據(jù)的語句Modifiessqldata表示存儲過程只包含寫數(shù)據(jù)的語句5sqlsecurity:這個特征用來指定存儲過程使用創(chuàng)建該存儲過程的用戶(definer)的許可來執(zhí)行，還是使用調(diào)用者(invoker)的許可來執(zhí)行。默認(rèn)是definerRoutine_body:存儲過程的主體部分，包含了在過程調(diào)用的時候必須執(zhí)行的sql語句。以begin開始，以end結(jié)束。如果存儲過程體中只有一條sql語句,可以省略begin-end標(biāo)志。例題在數(shù)據(jù)庫example中創(chuàng)建一個存儲過程，用于實現(xiàn)給定表customers中一個id號即可修改表customers中該客戶的性別為一個指定的性別。調(diào)用存儲過程sp_update_sex，將id為2的客戶性別修改為男性”M”Callsp_update_sex(2,’M’);課堂練習(xí)在數(shù)據(jù)庫example中創(chuàng)建一個存儲過程，用于實現(xiàn)給定employee表中給定一個部門號d_id,即可統(tǒng)計出該部門的總?cè)藬?shù)。9.1.2存儲過程體存儲過程體中可以使用各種sql語句和過程式語句的組合，來封裝數(shù)據(jù)庫應(yīng)用中復(fù)雜的業(yè)務(wù)邏輯和處理規(guī)則，以實現(xiàn)數(shù)據(jù)庫應(yīng)用的靈活編程。下面主要介紹幾個用于構(gòu)造存儲過程體的常用語法元素。1局部變量在存儲過程體中可以聲明局部變量，用來存儲存儲過程體中臨時結(jié)果。DECLAREvar_name[,…]type[DEFAULTvalue]Var_name:指定局部變量的名稱Type:用于聲明局部變量的數(shù)據(jù)類型default子句:用于為局部變量指定一個默認(rèn)值。若沒有指定，默認(rèn)為null.Declarecidint(10);使用說明：局部變量只能在存儲過程體的begin…end語句塊中聲明。局部變量必須在存儲過程體的開頭處聲明。局部變量的作用范圍僅限于聲明它的begin..end語句塊，其他語句塊中的語句不可以使用它。局部變量不同于用戶變量，兩者區(qū)別：局部變量聲明時，在其前面沒有使用@符號，并且它只能在begin..end語句塊中使用；而用戶變量在聲明時，會在其名稱前面使用@符號，同時已聲明的用戶變量存在于整個會話之中。2set語句使用set語句為局部變量賦值Setvar_name=exprSetcid=910;3select…into語句把選定列的值直接存儲到局部變量中，語法格式Selectcol_name[,…]intovar_name[,…]table_exprCol_name:用于指定列名Var_name:用于指定要賦值的變量名Table_expr:表示select語句中的from字句及后面的語法部分說明:存儲過程體中的select…into語句返回的結(jié)果集只能有一行數(shù)據(jù)。4定義處理程序是事先定義程序執(zhí)行過程中可能遇到的問題。并且可以在處理程序中定義解決這些問題的辦法。這種方式可以提前預(yù)測可能出現(xiàn)的問題，并提出解決方法。DECLAREhandler_typeHANDLERFORcondition_value[,…]sp_statementhandler_type:CONTINUE|EXIT|UNDOCondition_value:Sqlwarning|notfound|sqlexception5流程控制語句（1）條件判斷語句If語句Ifsearch_conditionthenstatement_list[elseif

search_conditionthenstatement_list]…[elsestatement_list]EndifSearch_condition參數(shù):條件判斷語句Statement_list參數(shù):不同條件的執(zhí)行語句Case語句表達(dá)形式1Casecase_valueWhenwhen_valuethenstatement_list[Whenwhen_valuethenstatement_list]…[elsestatement_list]Endcase表達(dá)形式2CaseWhensearch_conditionthenstatement_listEndcase(2)循環(huán)語句While語句、repeat語句和loop語句。While語句語法格式：[begin_label:]while

search_conditiondoStatement_listEndwhile[end_label]判斷條件search_condition是否為真,若為真,則執(zhí)行statement_list中的語句，然后再進(jìn)行判斷，如若仍然為真則繼續(xù)循環(huán)，直至條件判斷不為真時循環(huán)結(jié)束。Repeat語句語法格式[begin_label:]repeatStatement_listUntilsearch_conditionEndrepeat[end_label]Repeat語句首先執(zhí)行statement_list中的語句，然后判斷條件search_condition是否為真，倘若為真，則結(jié)束循環(huán)，若不為真，繼續(xù)循環(huán)。Repeat先執(zhí)行后判斷，while先判斷后執(zhí)行。Loop語句語法格式：[begin_label:]loopStatement_listEndloop[end_label]Loop語句允許重復(fù)執(zhí)行某個特定語句或語句塊，實現(xiàn)一個簡單的循環(huán)構(gòu)造，其中statement_list用于指定需要重復(fù)執(zhí)行的語句。在循環(huán)體statement_list中語句會一直重復(fù)被執(zhí)行，直至循環(huán)使用leave語句或者iterate退出。Leave語句主要用于跳出循環(huán)控制，語法結(jié)構(gòu)Leavelabeladd_num:loopset@count=@count+1;If@count=100thenleaveadd_num;Endloopadd_num;ITERATE語句跳出循環(huán)語句，跳出本次循環(huán)，然后直接進(jìn)入下一個循環(huán)。語法形式IteratelabelAdd_num:loopSet@count=@count+1;If@count=100thenleaveadd_num;Elseifmod(@count,3)=0theniterateadd_num;Select*fromemployee;Endloopadd_num;5光標(biāo)（游標(biāo)）查詢語句可能查詢出多條記錄，在存儲過程和函數(shù)中使用光標(biāo)標(biāo)來逐條讀取查詢結(jié)果集中的記錄。光標(biāo)的使用包括聲明光標(biāo)、打開光標(biāo)、使用光標(biāo)和關(guān)閉光標(biāo)。光標(biāo)必須聲明光標(biāo)、打開光標(biāo)、使用光標(biāo)和關(guān)閉光標(biāo)。光標(biāo)必須聲明在處理程序之前，并且聲明在變量和條件之后。1聲明光標(biāo)Declarecursor_namecursorforselect_statement;Cursor_name:光標(biāo)名稱Select_statement:select語句的內(nèi)容Declarecur_employeecursorforselectname,agefromemployee;2打開光標(biāo)Opencursor_nameOpencur_employee;3使用光標(biāo)Mysql中使用fetch關(guān)鍵字來使用光標(biāo)，語法形式Fetchcur_nameintovar_name[,var_name…];Cur_name表示光標(biāo)的名稱Var_name表示將光標(biāo)中的select語句查詢出來的信息存入該參數(shù)。Var_name必須在聲明光標(biāo)前就定義好。Fetchcur_employeeintoemp_name,emp_age;4關(guān)閉光標(biāo)Closecursor_name;Closecur_employee;每個光標(biāo)不再需要時都應(yīng)該被關(guān)閉，使用close語句將會釋放光標(biāo)所使用的全部資源。在一個光標(biāo)被關(guān)閉后，如果沒有重新被打開，則不能被使用。對于聲明過的光標(biāo)，則不需要再次聲明，可直接使用open語句打開。課堂習(xí)題在teacher表上創(chuàng)建名為teacher_info1的存儲過程，要求：teacher_info1有3個參數(shù)。輸入?yún)?shù)為teacher_id和type，輸出參數(shù)為info。存儲過程的作用是根據(jù)編號teacher_id來查詢teacher表中的記錄。如果type的值為1時，將姓名name傳給輸出參數(shù)info；如果type的值為2時，將年齡傳給輸出參數(shù)info；如果type的值為其他值，則返回字符串”Error”。9.2調(diào)用存儲過程Callsp_name([parameter[,…]]);Sp_name被調(diào)用存儲過程的名稱Parameter:指定調(diào)用存儲過程所要使用的參數(shù)。9.3修改存儲過程Alterprocedureproc_name[characteristic…]只能修改存儲過程的特征，如果要修改存儲過程的內(nèi)容，可以先刪除該存儲過程，然后再重新創(chuàng)建9.4刪除存儲過程Dropprocedure[ifexists]sp_name;存儲過程與存儲函數(shù)聯(lián)系與區(qū)別存儲過程與存儲函數(shù)一樣，都是由sql語句和過程式語句所組成的代碼片段，并且可以被應(yīng)用程序和其他sql語句調(diào)用。區(qū)別：存儲函數(shù)不能擁有輸出參數(shù)，因為存儲函數(shù)自身就是輸出參數(shù)；而存儲過程可以擁有輸出參數(shù)?？梢灾苯訉Υ鎯瘮?shù)進(jìn)行調(diào)用，而不需要使用call語句;而對存儲過程的調(diào)用，需要使用call語句。存儲函數(shù)中必須包含一條return語句，而這條特殊的sql語句不允許包含于存儲過程中。創(chuàng)建存儲函數(shù)MySQL中，創(chuàng)建存儲函數(shù)的基本形式如下：CREATEFUNCTIONsp_name([func_parameter[,...]])RETURNStype