mysql存储过程

我的MYSQL学习心得（10）：自定义存储过程和函数这一篇《我的MYSQL学习心得（十）》将会讲解MYSQL的存储过程和函数MYSQL中创建存储过程和函数分别使用CREATEPROCEDURE和CREATEFUNCTION使用CALL语句来调用存储过程，存储过程也可以调用其他存储过程函数可以从语句外调用，能返回标量值创建存储过程语法CREATEPROCEDUREsp_name([proc_parameter])[characteristics..]routine_bodyproc_parameter指定存储过程的参数列表，列表形式如下：[IN|OUT|INOUT]param_nametype其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型该类型可以是MYSQL数据库中的任意类型有以下取值：characteristic:LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'routine_body:ValidSQLprocedurestatementorstatementsLANGUAGESQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值[NOT]DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。[NOT]DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为[NOT]DETERMINISTICCONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA：指明子程序使用SQL语句的限制。CONTAINSSQL表明子程序包含SQL语句，但是不包含读写数据的语句；NOSQL表明子程序不包含SQL语句；READSSQLDATA：说明子程序包含读数据的语句；MODIFIESSQLDATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINSSQLSQLSECURITY{DEFINER|INVOKER}：指明谁有权限来执行。DEFINER表示只有定义者才能执行INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINERCOMMENT‘string’：注释信息，可以用来描述存储过程或函数routine_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。下面的语句创建一个查询t1表全部数据的存储过程DROPPROCEDUREIFEXISTSProc;DELIMITER//CREATEPROCEDUREProc()BEGINSELECT*FROMt3;END//DELIMITER;CALLProc();t3表是我们上一节创建的表这里的逻辑是1、先判断是否有Proc()这个存储过程，有就drop掉2、创建Proc()存储过程3、执行Proc()存储过程注意：“DELIMITER//”语句的作用是将MYSQL的结束符设置为//，因为MYSQL默认的语句结束符为分号;，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以“END//”结束存储过程。存储过程定义完毕之后再使用DELIMITER;恢复默认结束符。DELIMITER也可以指定其他符号为结束符！！！！！！！！！！！如果你是这样写的话，就会得到如下错误，初学者很容易犯这个错误，包括本人CREATEPROCEDUREProc()BEGINSELECT*FROMt3;ENDQuery:CREATEPROCEDUREProc()BEGINSELECT*FROMt3ErrorCode:1064YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear''atline3ExecutionTime:0secTransferTime:0secTotalTime:0.001sec---------------------------------------------------Query:ENDErrorCode:1064创建名为CountProc的存储过程，代码如下：DELIMITER//CREATEPROCEDURECountProc(OUTparam1INT)BEGINSELECTCOUNT(*)INTOparam1FROMt3;END//DELIMITER;上面代码的作用是创建一个获取t3表记录数的存储过程，名称是CountProc，COUNT(*)计算后把结果放入参数param1中。注意：当使用DELIMITER命令时，应该避免使用反斜杠（\）字符，因为反斜杠是MYSQL的转义字符！！！存储函数创建存储函数，需要使用CREATEFUNCTION语句，基本语法如下：CREATEFUNCTIONfunc_name([func_parameter])RETURNSTYPE[characteristics...]routine_bodyCREATEFUNCTION为用来创建存储函数的关键字；func_name表示存储函数的名称func_parameter为存储函数的参数列表，参数列表如下[IN|OUT|INOUT]PARAM_NAMETYPE其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数类型，该类型可以是MYSQL数据库中的任意类型RETURNSTYPE语句表示函数返回数据的类型；characteristics：指定存储函数的特性，取值与创建存储过程时相同创建存储函数，名称为NameByT，该函数返回SELECT语句的查询结果，数值类型为字符串型DELIMITER//CREATEFUNCTIONNameByT()RETURNSCHAR(50)RETURN(SELECTNAMEFROMt3WHEREid=2);//DELIMITER;注意：RETURNSCHAR(50)数据类型的时候，RETURNS是有S的，而RETURN(SELECTNAMEFROMt3WHEREid=2)的时候RETURN是没有S的所以有时候大家可能觉得MYSQL很烦，谁不知是自己写错了这里有一个方法，就是利用SQLYOG的代码格式化功能，选中要格式化的代码，然后按F12，如果能格式化，证明你的代码没有问题，如果不能格式化证明你写的代码有问题！！！不加s的话就会出现语法错误了Query:createfunctionNameByT()returnchar(50)return(selectnamefromt3whereid=2)ErrorCode:1064YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'returnchar(50)return(selectnamefromt3whereid=2)'atline2ExecutionTime:0secTransferTime:0secTotalTime:0.003sec-----------------------------调用函数SELECTnameByT()如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制转换为恰当的类型。例如，如果一个函数返回一个SET或ENUM值，但是RETURN语句返回一个整数，对于SET成员集的相应ENUM成员，从函数返回的值是字符串。指定参数为IN、OUT、INOUT只对PROCEDURE是合法的。（FUNCTION中总是默认是IN参数）RETURNS子句对FUNCTION做指定，对函数而言这是强制的。他用来指定函数的返回类型，而且函数体必须包含一个RETURNvalue语句变量的使用变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN…END程序中1、定义变量在存储过程中定义变量DECLAREvar_name[,varname]...date_type[DEFAULTVALUE];var_name为局部变量的名称。DEFAULTVALUE子句给变量提供一个默认值。值除了可以被声明为一个常数外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULLDECLAREMYPARAMINTDEFAULT100;2、为变量赋值定义变量之后，为变量赋值可以改变变量的默认值，MYSQL中使用SET语句为变量赋值SETvar_name=expr[,var_name=expr]...在存储过程中的SET语句是一般SET语句的扩展版本。被SET的变量可能是子程序内的变量，或者是全局服务器变量，如系统变量或者用户变量他运行SETa=x,b=y,….声明3个变量，分别为var1，var2和var3DECLAREvar1,var2,var3INT;SETvar1=10,var2=20;SETvar3=var1+var2;MYSQL中还可以通过SELECT…INTO为一个或多个变量赋值DECLARENAMECHAR(50);DECLAREidDECIMAL(8,2);SELECTid,NAMEINTOid,NAMEFROMt3WHEREid=2;定义条件和处理程序特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时候应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行1、定义条件DECLAREcondition_nameCONDITIONFOR[condition_type][condition_type]:SQLSTATE[VALUE]sqlstate_value|mysql_error_codecondition_name：表示条件名称condition_type：表示条件的类型sqlstate_value和mysql_error_code都可以表示mysql错误sqlstate_value为长度5的字符串错误代码mysql_error_code为数值类型错误代码，例如：ERROR1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值是1142这个语句指定需要特殊处理条件。他将一个名字和指定的错误条件关联起来。这个名字随后被用在定义处理程序的DECLAREHANDLER语句中定义ERROR1148(42000)错误，名称为command_not_allowed。可以用两种方法定义//方法一：使用sqlstate_valueDECLAREcommand_not_allowedCONDITIONFORSQLSTATE'42000'//方法二：使用mysql_error_codeDECLAREcommand_not_allowedCONDITIONFORSQLSTATE11482．定义处理程序MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：DECLAREhandler_typeHANDLERFORcondition_value[,...]sp_statementhandler_type:CONTINU