MySQL存储过程和函数

MySQL 存储过程和函数⼀、基本介绍存储过程和函数是事先经过编译并存储在数据库中的⼀段SQL语句的集合，减少数据在数据库和应⽤服务之间的传输，对于提⾼数据处理的效率是有好处的。存储过程和函数的区别在于函数必须有返回值，⽽存储过程没有，存储过程的参数可以使⽤IN、OUT、INOUT类型，⽽函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL，可能需要将函数改造成存储过程。存储过程和函数允许包含DDL语句，也允许使⽤事务，还可以调⽤其他的存储过程和函数，但不允许执⾏LoadDataInfile语句；⼆、相关操作创建存储过程或函数需要CREATEROUTINE权限，修改或删除存储过程或函数需要ALTERROUTINE权限，执⾏存储过程或函数需要EXECUTE权限。创建、修改存储过程或函数Createproceduresp_name（[proc_parameter[,…]）[characteristic…]routine_bodyCreatefunctionsp_name（[func_parameter[,…]）Returnstype[characteristic…]routine_bodyReturnxxx调⽤语法：callsp_name([parameter[,…])参数说明：pro_parameter[IN|OUT|INOUT]param_nametypefunc_parameterparam_nametype存储过程和函数中不允许执⾏LOADDATAINFILE语句。Delimiter$$修改命令结束符Characteristic特征值：Languagesql说明下⾯body是使⽤sql编写，系统默认Sqlsecurity{definer|invokerdefinerComment‘string存储过程或函数的注释信息Containssql|nosql|readssqldata|modifiessqldata}供⼦程序使⽤数据的内在信息，⽬前只提供给服务器，并没有根据这些containssql；Containssql表⽰⼦程序不包含读或写数据的语句。Nosqlsql语句。Readssqldata表⽰⼦程序包含读数据的语句，但不包含写数据的语句。Modifiessqldata表⽰⼦程序包含写数据的语句。实例：返回值⽤@xxx删除存储过程或函数：⼀次只能删除⼀个存储过程或函数，需ALTERROUTINE权限Dropprocedurename；查看存储过程或者函数：查看存储过程或函数的状态：Show{procedure|function}status[like‘pattern’];查看存储过程或函数的定义：Showcreate{procedure|function}name；通过查看information_schema.Routines了解存储过程和函数的信息Select*fromRoutineswhereroutine_name=“name”;三、变量的使⽤变量的定义DeclareBEGINEND块中，可以⽤在嵌套的块中。必须写在复合语句的开头，并且在任何其他语句的default赋默认值。Declarevar_name[,…]type[defaultvalue];变量的赋值变量可以直接赋值，或者通过查询赋值。直接赋值使⽤set，可以赋常量或者赋表达式。Setvar_name=expr[,var_name=expr]…Selectcol_name[,…]INTOvar_name[,…]fromxxx….;#查询结果必须只有⼀⾏Set@a=xxx;相当于全局变量定义条件和处理：处理过程中遇到问题时相应的处理步骤。条件定义Declarecondition_nameCONDITIONFORcondition_value条件处理（游标中有实例）Declarehandler_typeHANDLERFORcondition_value[,…]sp_statement说明：Handler_type⽬前⽀持continue和exit，continue继续执⾏下⾯的语句，exit表⽰终⽌。Condition_value值可以通过declare定义的condition_name，可以是SQLSTATE的值或者mysql-error-code的值或SQLWARING、NOTFOUND、SQLEXECEPTION，这3个值是3种定义好的错误类别。SQLWARING01SQLSTATE代码速记NOTFOUND02SQLSTATE代码速记SQLEXCEPTIONSQLWARING或NOTFOUNDSQLSTATE代码速记4.四、游标的使⽤对结果集进⾏循环的处理，包括光标的声明、open、fetch和close。1条sql，对应N条结果集的资源，取出资源接⼝/句柄，就是游标，沿着游标，可以⼀次取出1⾏。好处是，每⼀⾏的处理权利在我们⼿中。游标通俗来讲相当于你买东西别⼈⼀件件的给你，⽽不是⼀下⼦全给你。执⾏没有数据错误：修改存储过程：BEGINDECLARErow_idint;DECLARErow_dtvarchar(50);DECLARErow_catalogint;DECLARErow_totalint;DECLAREiintdefault1;DECLAREgetArticleCURSORFORselectid,dt,catalogfromtblarticlewherecatalog=75;selectcount(*)INTOrow_totalfromtblarticlewherecatalog=75;OPENgetArticle;WHILEi<=row_totalDOFETCHgetArticleintorow_id,row_dt,row_catalog;SELECTrow_id,row_dt,row_catalog;seti=i+1;ENDWHILE;CLOSEgetArticle;ENDBEGINDECLARErow_idint;DECLARErow_dtvarchar(50);DECLARErow_catalogint;DECLARErow_totalint;DECLAREiintDEFAULT1;DECLAREgetArticleCURSORFORselectid,dt,catalogfromtblarticlewherecatalog=75;DECLAREEXITHANDLERFORNOTFOUNDseti=0;OPENgetArticle;REPEATFETCHgetArticleintorow_id,row_dt,row_catalog;SELECTrow_id,row_dt,row_catalog;UNTILi=0ENDREPEAT;CLOSEgetArticle;ENDDeclarecontinue/exithandlerfornotfoundclose游标名;Continue和exit的区别：游标循环读取的正确逻辑：五、流程控制If、case、loop、leave、iterate、repeat、while语句If语句IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]…[ELSEstatement_list]ENDIF;While语句WHILEsearch_conditionDoStatement_listEND