MySQL数据库原理与应用(微课版)ch11存储程序

第11章

存储程序本章概述存储程序就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。存储程序分为存储过程与存储函数。本章主要介绍如何创建存储过程和存储函数以及变量的使用，如何调用、查看、修改、删除存储过程和存储函数，等等。本章学习目标掌握如何创建存储过程；掌握如何创建存储函数；熟悉变量的使用方法；熟悉如何定义条件和处理程序；了解光标的使用方法；掌握流程控制的使用；掌握如何调用存储过程和函数；熟悉如何查看存储过程和函数；掌握修改存储过程和函数的方法；熟悉如何删除存储过程和函数；掌握综合使用存储过程和函数的方法和技巧。目录01020304定义条件和处理程序创建、调用存储函数查看存储过程和函数创建、调用存储过程05修改存储过程和函数06删除存储过程和函数07全局变量的持久化本章小结08创建、调用存储过程创建存储过程通过CREATEPROCEDURE语句实现，基本语法格式如下：CREATEPROCEDUREsp_name([proc_parameter])[characteristics…]routine_bodyCREATEPROCEDURE是用来创建存储函数的关键字；sp_name是存储过程的名称；proc_parameter是存储过程的参数列表。参数列表的形式如下：[IN|OUT|INOUT]param_nametype其中，IN表示输入参数；OUT表示输出参数；INOUT表示既可以输入又可以输出；param_name表示参数名称；type表示参数的数据类型，该类型可以是MySQL数据库中的任意数据类型。创建存储过程创建、调用存储过程存储过程是通过CALL语句进行调用的，语法如下：CALLsp_name([parameter[,...]])CALL语句调用一个先前用CREATEPROCEDURE创建的存储过程，其中sp_name为存储过程名称，parameter为存储过程的参数。调用存储过程创建、调用存储过程定义一个水果表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');建立测试数据表创建、调用存储过程下面的代码演示了存储过程的内容，名称为AvgFruitPrice，返回所有水果的平均价格，输入代码如下：CREATEPROCEDUREAvgFruitPrice()BEGINSELECTAVG(price)ASavgpriceFROMtb_fruits;END调用存储过程，语句如下：CALLAvgFruitPrice();建立一个简单的存储过程并调用目录01020304定义条件和处理程序创建、调用存储函数查看存储过程和函数创建、调用存储过程05修改存储过程和函数06删除存储过程和函数07全局变量的持久化本章小结08创建、调用存储函数创建存储函数需要使用CREATEFUNCTION语句，基本语法格式如下：CREATEFUNCTIONfunc_name([func_parameter]))RETURNStype[characteristic…]routine_bodyCREATEFUNCTION为用来创建存储函数的关键字；func_name表示存储函数的名称；func_parameter为存储过程的参数列表，参数列表形式如下：[IN|OUT|INOUT]param_nametype其中，IN表示输入参数；OUT表示输出参数；INOUT表示既可以输入又可以输出；param_name表示参数名称；type表示参数的数据类型，该数据类型可以是MySQL数据库中的任意数据类型。创建、调用存储函数【例11-5】定义存储函数CountProc2，然后调用这个函数，代码如下：DELIMITER//CREATEFUNCTIONCountProc2(sidINT)RETURNSINTBEGINRETURN(SELECTCOUNT(*)FROMtb_fruitsWHEREf_id=sid);END;//运行程序，结果如图所示。创建、调用存储函数可以在子程序中声明并使用变量。这些变量的作用范围是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定义条件和处理程序创建、调用存储函数查看存储过程和函数创建、调用存储过程05修改存储过程和函数06删除存储过程和函数07全局变量的持久化本章小结080302定义条件和处理程序定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。条件定义条件和处理程序定义条件使用DECLARE语句，语法格式如下：DECLAREcondition_nameCONDITIONFOR[condition_type][condition_type]:SQLSTATE[VALUE]sqlstate_value|mysql_error_code其中，condition_name参数表示条件的名称；condition_type参数表示条件的类型；sqlstate_value和mysql_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码，mysql_error_code为数值类型错误代码。定义条件定义条件和处理程序【例11-9】定义ERROR1148(42000)错误，名称为command_not_allowed。可以用两种不同的方法来定义，代码如下：//方法一：使用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为错误处理方式，参数取3个值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不处理，继续执行；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';定义处理程序的几种方式定义条件和处理程序查询语句可能返回多条记录，如果数据量非常大，就需要在存储过程和储存函数中使用光标来逐条读取查询结果集中的记录。MySQL中使用DECLARE关键字来声明光标，语法格式如下：DECLAREcursor_nameCURSORFORselect_statement打开光标的语法格式如下：OPENcursor_name;使用光标的语法如下：FETCHcursor_nameINTOvar_name[,var_name]...光标的使用定义条件和处理程序流程控制语句根据条件控制语句的执行。MySQL中流程控制语句有：IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHILE语句。每个流程中可能包含一个单独语句，或者使用BEGIN...END构造的复合语句，构造可以被嵌套。流程控制定义条件和处理程序IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式如下：IFexpr_conditionTHENstatement_list[ELSEIFexpr_conditionTHENstatement_list]…[ELSEstatement_list]ENDIFIF实现了一个基本的条件构造。若expr_condition求值为真（TRUE），则相应的SQL语句列表被执行；若没有expr_condition匹配，则ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。流程控制定义条件和处理程序CASE是另一个进行条件判断的语句，该语句有两种语句格式，第一种格式如下：CASEcase_exprWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]…[ELSEstatement_list]ENDCASE其中，case_expr参数表示条件判断的表达式，决定了哪一个WHEN子句会被执行；when_value参数表示表达式可能的值，如果某个when_value表达式与case_expr表达式的结果相同，就执行对应THEN关键字后的statement_list中的语句；statement_list参数表示不同when_value值的执行语句。流程控制定义条件和处理程序LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出，跳出循环过程使用LEAVE子句，LOOP语句的基本语法格式如下：[loop_label:]LOOPstatement_listENDLOOP[loop_label]loop_label表示LOOP语句的标注名称，该参数可以省略；statement_list参数表示需要循环执行的语句。流程控制定义条件和处理程序LEAVE语句用来退出任何被标注的流程控制构造，LEAVE语句基本语法格式如下：LEAVElabel其中，label参数表示循环的标志。LEAVE和BEGIN...END或循环一起使用。流程控制定义条件和处理程序ITERATE语句将执行顺序转到语句段开头处，语句基本语法格式如下：ITERATElabelITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为“再次循环”，label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。流程控制定义条件和处理程序REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，若表达式为真，则循环结束；否则重复执行循环中的语句。REPEAT语句的基本格式如下：[repeat_label:]]REPEATstatement_listUNTILexpr_conditionENDREPEAT[repeat_label]repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真。流程控制定义条件和处理程序WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：[while_label:]WHILEexpr_conditionDOstatement_listENDWHILE[while_label]while_label为WHILE语句的标注名称；expr_condition为进行判断的表达式，若表达式结果为真，则WHILE语句内的语句或语句群被执行，直至expr_condition为假，退出循环。流程控制目录01定义条件和处理程序创建、调用存储函数查看存储过程和函数创建、调用存储过程05修改存储过程和函数06删除存储过程和函数07全局变量的持久化本章小结08020403查看存储过程和函数SHOWSTATUS语句可以查看存储过程和函数的状态，其基本语法格式如下：SHOW{PROCEDURE|FUNCTION}STATUS[LIKE'pattern']这个语句是MySQL的一个扩展。它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。若没有指定样式，则根据使用的语句，所有存储程序或存储函数的信息都被列出。PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。使用SHOWSTATUS语句查看存储过程和函数的状态查看存储过程和函数MySQL还可以使用SHOWCREATE语句查看存储过程和函数的状态。SHOWCREATE{PROCEDURE|FUNCTION}sp_name这个语句是MySQL的一个扩展。类似于SHOWCREATETABLE，它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数；sp_name参数表示匹配存储过程或函数的名称。使用SHOWCREATE语句查看存储过程和函数的定义查看存储过程和函数MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='sp_name';其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称；sp_name参数表示存储过程或函数的名称。从information_schema.Routines表中查看存储过程和函数的信息目录01定义条件和处理程序创建、调用存储函数查看存储过程和函数创建、调用存储过程修改存储过程和函数06删除存储过程和函数07全局变量的持久化本章小结0802030504修改存储过程和函数使用ALTER语句可以修改存储过程或函数的特性，本节将介绍如何使用ALTER语句修改存储过程和函数。ALTER{PROCEDURE|FUNCTION}sp_name[characteristic...]其中，sp_name参数表示存储过程或函数的名称；characteristic参数指定存储函数的特性，可能的取值如下。CONTAINSSQL：表示子程序包含SQL语句，但不包含读或写数据的语句。NOSQL：表示子程序中不包含SQL语句。READSSQLDATA：表示子程序中包含读数据的语句。MODIFIESSQLDATA：表示子程序中包含写数据的语句。SQLSECURITY{DEFINER|INVOKER}：指明谁有权限来执行。DEFINER：表示只有定义者自己才能够执行。INVOKER：表示调用者可以执行。COMMENT'string'：表示注释信息。目录01定义条件和处理程序创建、调用存储函数查看存储过程和函数创建、调用存储过程修改存储过程和函数删除存储过程和函数07全局变量的持久化本章小结080203040605删除存储过程和函数删除存储过程和函数可以使用DROP语句，其语法格式如下：DROP{PROCEDURE|FUNCTION}[IF