MySQL數(shù)據(jù)庫原理與應用(微課版)ch11存儲程序

第11章

存儲程序本章概述存儲程序就是一條或者多條SQL語句的集合，可視為批文件，但是其作用不僅限于批處理。存儲程序分為存儲過程與存儲函數(shù)。本章主要介紹如何創(chuàng)建存儲過程和存儲函數(shù)以及變量的使用，如何調用、查看、修改、刪除存儲過程和存儲函數(shù)，等等。本章學習目標掌握如何創(chuàng)建存儲過程；掌握如何創(chuàng)建存儲函數(shù)；熟悉變量的使用方法；熟悉如何定義條件和處理程序；了解光標的使用方法；掌握流程控制的使用；掌握如何調用存儲過程和函數(shù)；熟悉如何查看存儲過程和函數(shù)；掌握修改存儲過程和函數(shù)的方法；熟悉如何刪除存儲過程和函數(shù)；掌握綜合使用存儲過程和函數(shù)的方法和技巧。目錄01020304定義條件和處理程序創(chuàng)建、調用存儲函數(shù)查看存儲過程和函數(shù)創(chuàng)建、調用存儲過程05修改存儲過程和函數(shù)06刪除存儲過程和函數(shù)07全局變量的持久化本章小結08創(chuàng)建、調用存儲過程創(chuàng)建存儲過程通過CREATEPROCEDURE語句實現(xiàn)，基本語法格式如下：CREATEPROCEDUREsp_name([proc_parameter])[characteristics…]routine_bodyCREATEPROCEDURE是用來創(chuàng)建存儲函數(shù)的關鍵字；sp_name是存儲過程的名稱；proc_parameter是存儲過程的參數(shù)列表。參數(shù)列表的形式如下：[IN|OUT|INOUT]param_nametype其中，IN表示輸入?yún)?shù)；OUT表示輸出參數(shù)；INOUT表示既可以輸入又可以輸出；param_name表示參數(shù)名稱；type表示參數(shù)的數(shù)據(jù)類型，該類型可以是MySQL數(shù)據(jù)庫中的任意數(shù)據(jù)類型。創(chuàng)建存儲過程創(chuàng)建、調用存儲過程存儲過程是通過CALL語句進行調用的，語法如下：CALLsp_name([parameter[,...]])CALL語句調用一個先前用CREATEPROCEDURE創(chuàng)建的存儲過程，其中sp_name為存儲過程名稱，parameter為存儲過程的參數(shù)。調用存儲過程創(chuàng)建、調用存儲過程定義一個水果表tb_fruits，SQL語句如下：CREATETABLE`tb_fruits`(`f_id`tinyintNOTNULLAUTO_INCREMENT,`name`varchar(255)COLLATEutf8mb4_general_ciDEFAULTNULL,`price`decimal(10,0)DEFAULTNULL,PRIMARYKEY(`f_id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_general_ci;INSERTINTO`tb_fruits`VALUES('1','apple','9');INSERTINTO`tb_fruits`VALUES('2','pear','3');INSERTINTO`tb_fruits`VALUES('3','grape','7');INSERTINTO`tb_fruits`VALUES('4','strawberry','13');INSERTINTO`tb_fruits`VALUES('5','tangerine','6');建立測試數(shù)據(jù)表創(chuàng)建、調用存儲過程下面的代碼演示了存儲過程的內容，名稱為AvgFruitPrice，返回所有水果的平均價格，輸入代碼如下：CREATEPROCEDUREAvgFruitPrice()BEGINSELECTAVG(price)ASavgpriceFROMtb_fruits;END調用存儲過程，語句如下：CALLAvgFruitPrice();建立一個簡單的存儲過程并調用目錄01020304定義條件和處理程序創(chuàng)建、調用存儲函數(shù)查看存儲過程和函數(shù)創(chuàng)建、調用存儲過程05修改存儲過程和函數(shù)06刪除存儲過程和函數(shù)07全局變量的持久化本章小結08創(chuàng)建、調用存儲函數(shù)創(chuàng)建存儲函數(shù)需要使用CREATEFUNCTION語句，基本語法格式如下：CREATEFUNCTIONfunc_name([func_parameter]))RETURNStype[characteristic…]routine_bodyCREATEFUNCTION為用來創(chuàng)建存儲函數(shù)的關鍵字；func_name表示存儲函數(shù)的名稱；func_parameter為存儲過程的參數(shù)列表，參數(shù)列表形式如下：[IN|OUT|INOUT]param_nametype其中，IN表示輸入?yún)?shù)；OUT表示輸出參數(shù)；INOUT表示既可以輸入又可以輸出；param_name表示參數(shù)名稱；type表示參數(shù)的數(shù)據(jù)類型，該數(shù)據(jù)類型可以是MySQL數(shù)據(jù)庫中的任意數(shù)據(jù)類型。創(chuàng)建、調用存儲函數(shù)【例11-5】定義存儲函數(shù)CountProc2，然后調用這個函數(shù)，代碼如下：DELIMITER//CREATEFUNCTIONCountProc2(sidINT)RETURNSINTBEGINRETURN(SELECTCOUNT(*)FROMtb_fruitsWHEREf_id=sid);END;//運行程序，結果如圖所示。創(chuàng)建、調用存儲函數(shù)可以在子程序中聲明并使用變量。這些變量的作用范圍是BEGIN…END之間。在存儲過程中使用DECLARE語句定義變量，語法格式如下：DECLAREvar_name[,varname]…date_type[DEFAULTvalue];【例11-6】定義名稱為myparam的變量，類型為INT，默認值為10，代碼如下：DECLAREmyparamINTDEFAULT10;MySQL中使用SET語句為變量賦值，語法格式如下：SETvar_name=expr[,var_name=expr]...;例如：DECLAREvar1,var2,var3INT;SETvar1=10,var2=20;SETvar3=var1+var2;目錄0104定義條件和處理程序創(chuàng)建、調用存儲函數(shù)查看存儲過程和函數(shù)創(chuàng)建、調用存儲過程05修改存儲過程和函數(shù)06刪除存儲過程和函數(shù)07全局變量的持久化本章小結080302定義條件和處理程序定義條件是事先定義程序執(zhí)行過程中遇到的問題，處理程序定義了在遇到這些問題時應當采取的處理方式，并且保證存儲過程或函數(shù)在遇到警告或錯誤時能繼續(xù)執(zhí)行。這樣可以增強存儲程序處理問題的能力，避免程序異常停止運行。條件定義條件和處理程序定義條件使用DECLARE語句，語法格式如下：DECLAREcondition_nameCONDITIONFOR[condition_type][condition_type]:SQLSTATE[VALUE]sqlstate_value|mysql_error_code其中，condition_name參數(shù)表示條件的名稱；condition_type參數(shù)表示條件的類型；sqlstate_value和mysql_error_code都可以表示MySQL的錯誤，sqlstate_value為長度為5的字符串類型錯誤代碼，mysql_error_code為數(shù)值類型錯誤代碼。定義條件定義條件和處理程序【例11-9】定義ERROR1148(42000)錯誤，名稱為command_not_allowed?？梢杂脙煞N不同的方法來定義，代碼如下：//方法一：使用sqlstate_valueDECLAREcommand_not_allowedCONDITIONFORSQLSTATE'42000';//方法二：使用msql_error_codeDECLAREcommand_not_allowedCONDITIONFOR1148定義條件定義條件和處理程序定義處理程序時，使用DECLARE語句，語法如下：DECLAREhandler_typeHANDLERFORcondition_value[,…]sp_statementhandler_type:CONTINUE|EXIT|UNDOCondition_value:SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_code其中，handler_type為錯誤處理方式，參數(shù)取3個值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到錯誤不處理，繼續(xù)執(zhí)行；EXIT表示遇到錯誤馬上退出；UNDO表示遇到錯誤后撤回之前的操作，MySQL中暫時不支持這樣的操作。定義處理程序定義條件和處理程序//方法一：捕獲sqlstate_valueDECLARECONTINUEHANDLERFORSQLSTATE'42S02'SET@info='NO_SUCH_TABLE',//方法二：捕獲mysql_error_codeDECLARECONTINUEHANDLERFOR1146SET@info='NO_SUCH_TABLE';//方法三：先定義條件，然后調用DECLAREno_such_tableCONDITIONFOR1146;DECLARECONTINUEHANDLERFORNO_SUCH_TABLESET@info='NO_SUCH_TABLE';//方法四：使用SQLWARNINGDECLAREEXITHANDLERFORSOLWARNINGSET@info='ERROR';//方法五：使用NOTFOUNDDECLAREEXITHANDLERFORNOTFOUNDSET@info='NO_SUCH_TABLE';//方法六：使用SQLEXCEPTIONDECLAREEXITHANDLERFORSQLEXCEPTIONSET@info='ERROR';定義處理程序的幾種方式定義條件和處理程序查詢語句可能返回多條記錄，如果數(shù)據(jù)量非常大，就需要在存儲過程和儲存函數(shù)中使用光標來逐條讀取查詢結果集中的記錄。MySQL中使用DECLARE關鍵字來聲明光標，語法格式如下：DECLAREcursor_nameCURSORFORselect_statement打開光標的語法格式如下：OPENcursor_name;使用光標的語法如下：FETCHcursor_nameINTOvar_name[,var_name]...光標的使用定義條件和處理程序流程控制語句根據(jù)條件控制語句的執(zhí)行。MySQL中流程控制語句有：IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHILE語句。每個流程中可能包含一個單獨語句，或者使用BEGIN...END構造的復合語句，構造可以被嵌套。流程控制定義條件和處理程序IF語句包含多個條件判斷，根據(jù)判斷的結果為TRUE或FALSE執(zhí)行相應的語句，語法格式如下：IFexpr_conditionTHENstatement_list[ELSEIFexpr_conditionTHENstatement_list]…[ELSEstatement_list]ENDIFIF實現(xiàn)了一個基本的條件構造。若expr_condition求值為真（TRUE），則相應的SQL語句列表被執(zhí)行；若沒有expr_condition匹配，則ELSE子句里的語句列表被執(zhí)行。statement_list可以包括一個或多個語句。流程控制定義條件和處理程序CASE是另一個進行條件判斷的語句，該語句有兩種語句格式，第一種格式如下：CASEcase_exprWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]…[ELSEstatement_list]ENDCASE其中，case_expr參數(shù)表示條件判斷的表達式，決定了哪一個WHEN子句會被執(zhí)行；when_value參數(shù)表示表達式可能的值，如果某個when_value表達式與case_expr表達式的結果相同，就執(zhí)行對應THEN關鍵字后的statement_list中的語句；statement_list參數(shù)表示不同when_value值的執(zhí)行語句。流程控制定義條件和處理程序LOOP循環(huán)語句用來重復執(zhí)行某些語句，與IF和CASE語句相比，LOOP只是創(chuàng)建一個循環(huán)操作的過程，并不進行條件判斷。LOOP內的語句一直重復執(zhí)行直到循環(huán)被退出，跳出循環(huán)過程使用LEAVE子句，LOOP語句的基本語法格式如下：[loop_label:]LOOPstatement_listENDLOOP[loop_label]loop_label表示LOOP語句的標注名稱，該參數(shù)可以省略；statement_list參數(shù)表示需要循環(huán)執(zhí)行的語句。流程控制定義條件和處理程序LEAVE語句用來退出任何被標注的流程控制構造，LEAVE語句基本語法格式如下：LEAVElabel其中，label參數(shù)表示循環(huán)的標志。LEAVE和BEGIN...END或循環(huán)一起使用。流程控制定義條件和處理程序ITERATE語句將執(zhí)行順序轉到語句段開頭處，語句基本語法格式如下：ITERATElabelITERATE只可以出現(xiàn)在LOOP、REPEAT和WHILE語句內。ITERATE的意思為“再次循環(huán)”，label參數(shù)表示循環(huán)的標志。ITERATE語句必須跟在循環(huán)標志前面。流程控制定義條件和處理程序REPEAT語句創(chuàng)建一個帶條件判斷的循環(huán)過程，每次語句執(zhí)行完畢之后，會對條件表達式進行判斷，若表達式為真，則循環(huán)結束；否則重復執(zhí)行循環(huán)中的語句。REPEAT語句的基本格式如下：[repeat_label:]]REPEATstatement_listUNTILexpr_conditionENDREPEAT[repeat_label]repeat_label為REPEAT語句的標注名稱，該參數(shù)可以省略；REPEAT語句內的語句或語句群被重復，直至expr_condition為真。流程控制定義條件和處理程序WHILE語句創(chuàng)建一個帶條件判斷的循環(huán)過程，與REPEAT不同，WHILE在執(zhí)行語句執(zhí)行時，先對指定的表達式進行判斷，如果為真，就執(zhí)行循環(huán)內的語句，否則退出循環(huán)。WHILE語句的基本格式如下：[while_label:]WHILEexpr_conditionDOstatement_listENDWHILE[while_label]while_label為WHILE語句的標注名稱；expr_condition為進行判斷的表達式，若表達式結果為真，則WHILE語句內的語句或語句群被執(zhí)行，直至expr_condition為假，退出循環(huán)。流程控制目錄01定義條件和處理程序創(chuàng)建、調用存儲函數(shù)查看存儲過程和函數(shù)創(chuàng)建、調用存儲過程05修改存儲過程和函數(shù)06刪除存儲過程和函數(shù)07全局變量的持久化本章小結08020403查看存儲過程和函數(shù)SHOWSTATUS語句可以查看存儲過程和函數(shù)的狀態(tài)，其基本語法格式如下：SHOW{PROCEDURE|FUNCTION}STATUS[LIKE'pattern']這個語句是MySQL的一個擴展。它返回子程序的特征，如數(shù)據(jù)庫、名字、類型、創(chuàng)建者及創(chuàng)建和修改日期。若沒有指定樣式，則根據(jù)使用的語句，所有存儲程序或存儲函數(shù)的信息都被列出。PROCEDURE和FUNCTION分別表示查看存儲過程和函數(shù)；LIKE語句表示匹配存儲過程或函數(shù)的名稱。使用SHOWSTATUS語句查看存儲過程和函數(shù)的狀態(tài)查看存儲過程和函數(shù)MySQL還可以使用SHOWCREATE語句查看存儲過程和函數(shù)的狀態(tài)。SHOWCREATE{PROCEDURE|FUNCTION}sp_name這個語句是MySQL的一個擴展。類似于SHOWCREATETABLE，它返回一個可用來重新創(chuàng)建已命名子程序的確切字符串。PROCEDURE和FUNCTION分別表示查看存儲過程和函數(shù)；sp_name參數(shù)表示匹配存儲過程或函數(shù)的名稱。使用SHOWCREATE語句查看存儲過程和函數(shù)的定義查看存儲過程和函數(shù)MySQL中存儲過程和函數(shù)的信息存儲在information_schema數(shù)據(jù)庫下的Routines表中?？梢酝ㄟ^查詢該表的記錄來查詢存儲過程和函數(shù)的信息。其基本語法形式如下：SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='sp_name';其中，ROUTINE_NAME字段中存儲的是存儲過程和函數(shù)的名稱；sp_name參數(shù)表示存儲過程或函數(shù)的名稱。從information_schema.Routines表中查看存儲過程和函數(shù)的信息目錄01定義條件和處理程序創(chuàng)建、調用存儲函數(shù)查看存儲過程和函數(shù)創(chuàng)建、調用存儲過程修改存儲過程和函數(shù)06刪除存儲過程和函數(shù)07全局變量的持久化本章小結0802030504修改存儲過程和函數(shù)使用ALTER語句可以修改存儲過程或函數(shù)的特性，本節(jié)將介紹如何使用ALTER語句修改存儲過程和函數(shù)。ALTER{PROCEDURE|FUNCTION}sp_name[characteristic...]其中，sp_name參數(shù)表示存儲過程或函數(shù)的名稱；characteristic參數(shù)指定存儲函數(shù)的特性，可能的取值如下。CONTAINSSQL：表示子程序包含SQL語句，但不包含讀或寫數(shù)據(jù)的語句。NOSQL：表示子程序中不包含SQL語句。READSSQLDATA：表示子程序中包含讀數(shù)據(jù)的語句。MODIFIESSQLDATA：表示子程序中包含寫數(shù)據(jù)的語句。SQLSECURITY{DEFINER|INVOKER}：指明誰有權限來執(zhí)行。DEFINER：表示只有定義者自己才能夠執(zhí)行。INVOKER：表示調用者可以執(zhí)行。COMMENT'string'：表示注釋信息。目錄01定義條件和處理程序創(chuàng)建、調用存儲函數(shù)查看存儲過程和函數(shù)創(chuàng)建、調用存儲過程修改存儲過程和函數(shù)刪除存儲過程和函數(shù)07全局變量的持久化本章小結080203040605刪除存儲過程和函數(shù)刪除存儲過程和函數(shù)可以使用DROP語句，其語法格式如下：DROP{PROCEDURE|FUNCTION}[IF