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

創建存儲過程MySQL中，創建存儲過程的基本形式如下：CREATEPROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_bodySp_name:存儲過程的名稱，默認在當前數據庫中創建。這個名稱應當盡量避免與MySQL的內置函數相同的名稱Proc_parameter:存儲過程的參數列表格式[IN|OUT|INOUT]param_nametypeParam_name為參數名，type為參數的數據類型。多個參數彼此間用逗號分隔。輸入參數、輸出參數和輸入/輸出參數，分別用in/out/inout標識。參數的取名不要與數據表的列名相同。Characteristic:存儲過程的某些特征設定，分別介紹1COMMENT’string’:用于對存儲過程的描述，其中string為描述內容,comment為關鍵字。2LANGUAGESQL:指明編寫這個存儲過程的語言為SQL語言。這個選項可以不指定。3DETERMINISTIC:表示存儲過程對同樣的輸入參數產生相同的結果;NOTDETERMINISTIC，則表示會產生不確定的結果（默認）。4containssql|nosql|readssqldata|modifiessqldataContainssql表示存儲過程包含讀或寫數據的語句（默認）Nosql表示不包含sql語句Readssqldata表示存儲過程只包含讀數據的語句Modifiessqldata表示存儲過程只包含寫數據的語句5sqlsecurity:這個特征用來指定存儲過程使用創建該存儲過程的用戶(definer)的許可來執行，還是使用調用者(invoker)的許可來執行。默認是definerRoutine_body:存儲過程的主體部分，包含了在過程調用的時候必須執行的sql語句。以begin開始，以end結束。如果存儲過程體中只有一條sql語句,可以省略begin-end標志。例題在數據庫example中創建一個存儲過程，用于實現給定表customers中一個id號即可修改表customers中該客戶的性別為一個指定的性別。調用存儲過程sp_update_sex，將id為2的客戶性別修改為男性”M”Callsp_update_sex(2,’M’);課堂練習在數據庫example中創建一個存儲過程，用于實現給定employee表中給定一個部門號d_id,即可統計出該部門的總人數。9.1.2存儲過程體存儲過程體中可以使用各種sql語句和過程式語句的組合，來封裝數據庫應用中復雜的業務邏輯和處理規則，以實現數據庫應用的靈活編程。下面主要介紹幾個用于構造存儲過程體的常用語法元素。1局部變量在存儲過程體中可以聲明局部變量，用來存儲存儲過程體中臨時結果。DECLAREvar_name[,…]type[DEFAULTvalue]Var_name:指定局部變量的名稱Type:用于聲明局部變量的數據類型default子句:用于為局部變量指定一個默認值。若沒有指定，默認為null.Declarecidint(10);使用說明：局部變量只能在存儲過程體的begin…end語句塊中聲明。局部變量必須在存儲過程體的開頭處聲明。局部變量的作用范圍僅限于聲明它的begin..end語句塊，其他語句塊中的語句不可以使用它。局部變量不同于用戶變量，兩者區別：局部變量聲明時，在其前面沒有使用@符號，并且它只能在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語句返回的結果集只能有一行數據。4定義處理程序是事先定義程序執行過程中可能遇到的問題。并且可以在處理程序中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題，并提出解決方法。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參數:條件判斷語句Statement_list參數:不同條件的執行語句Case語句表達形式1Casecase_valueWhenwhen_valuethenstatement_list[Whenwhen_valuethenstatement_list]…[elsestatement_list]Endcase表達形式2CaseWhensearch_conditionthenstatement_listEndcase(2)循環語句While語句、repeat語句和loop語句。While語句語法格式：[begin_label:]while

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