Mysql之存储过程和函数

存储过程就是一条或多条SQL语句的集合，可视为批文件，但是其作用不仅用于批处理。

存储程序分为：1、存储过程2、函数

使用Call语句来调用存储过程，只能用输出变量返回值。

一、创建存储过程

语法：

createproceduresp_name(proc_parameter)[characteristics……]routine_body创建存储函数名为sp_name，存储过程的名为：proc_parameter

指定存储参数列表为：

[IN | OUT | INOUT] param_name type

其中IN表示输入参数，OUT表示输出参数，INOUT表示即可输入也可输出

param_name表示参数名称

type 表示参数类型，该类型可以是Mysql数据库中的任意类型。

characteristics 指定存储过程的特性，有以下取值：

LANGUAGE SQL：说明routine_body部分是由SQL语句组成，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。

[NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。

1.DETERMINISTIC表示结果是正确的。每次执行存储过程时，相同输入会得到相同的输出。

2.NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

{ CONTAINS SQL | NO SQL |REDAS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。

1. CONTAINS SQL：表示子程序包含SQL语句，但是不包含读写数据的语句。

2.NO SQL：表示子程序不包含SQL语句。

3.REDAS SQL DATA ：说明子程序包含数据的语句。

4.MODIFIES SQL DATA：表明子程序包含写数据的语句。默认为CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER}:指明谁有权限来执行。

1. DEFINER表示只有定义者才能执行。

2.INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER

COMMENT ‘string’：注释信息，可以用来描述存储过程或函数。

routine_body是SQL代码的内容。通常用begin……end表示SQL代码的开始和结束。

编写存储过程并不是简单的事情，可能存储过程中需要复杂的SQL语句，并且要创建存储过程的权限；但是使用存储过程将简化操作，减少冗余的操作步骤，同时，还可以减少操作过程中的失误、提高效率，因此存储过程非常的有用，而且应该尽量学会使用。

例1：

mysql>delimiter//#定义SQL语句的结束符号为//，使用这条命令时，应该避免（‘\’）字符，因为反斜线是Mysql的转意符。mysql>createprocedurep1()->begin->select*fromt;->end//mysql>delimiter;mysql>showprocedurestatus\G#查看存储过程信息mysql>callp1#读取这个存储过程

例2：

mysql>delimiter//mysql>createprocedurep2(nint)->begin->select*fromtwhereid=n;->end//mysql>delimiter;mysql>showprocedurestatus\Gmysql>callp2(1)#需要带入取值

例3：

mysql>createdatabasedb_proc;mysql>usedb_procmysql>CREATETABLE`proc_test`(->`id`tinyint(4)NOTNULLAUTO_INCREMENT,->`username`varchar(20)NOTNULL,->`password`varchar(20)NOTNULL,->PRIMARYKEY(`id`)->)ENGINE=MyISAMAUTO_INCREMENT=50DEFAULTCHARSET=utf8;mysql>delimiter//mysql>createproceduremytest(innamevarchar(20),inpwdvarchar(20))->begin->insertintoproc_test(username,password)values(name,pwd);->end//mysql>delimiter;mysql>callmytest('lxq','password');mysql>select*fromproc_test;#验证插入了数据

************************

mysql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT

Create procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数

表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数

该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数

调用时指定，并且可被改变和返回

IN参数例子：

mysql>DELIMITER//mysql>CREATEPROCEDUREsp_demo_in_parameter(INp_inINT)->BEGIN->SELECTp_in;#查询输入参数->SETp_in=2;#修改->selectp_in;#查看修改后的值->END//mysql>DELIMITER;

执行结果:

mysql>set@p_in=1;mysql>callsp_demo_in_parameter(@p_in);mysql>select@p_in;以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

OUT参数例子

mysql>DELIMITER//mysql>CREATEPROCEDUREsp_demo_out_parameter(OUTp_outINT)->BEGIN->SELECTp_out;#查看输出参数->SETp_out=2;#修改参数值->SELECTp_out;#看看有否变化->END//mysql>DELIMITER;

执行结果:

mysql>SET@p_out=1;mysql>CALLsp_demo_out_parameter(@p_out);mysql>SELECT@p_out;p_out在存储过程中被修改,直接影响@p_out的值

INOUT参数例子：

mysql>DELIMITER//mysql>CREATEPROCEDUREsp_demo_inout_parameter(INOUTp_inoutINT)->BEGIN->SELECTp_inout;->SETp_inout=2;->SELECTp_inout;->END;mysql>DELIMITER;

执行结果：

set@p_inout=1;callsp_demo_inout_parameter(@p_inout);select@p_inout;

****************************

二、特定异常

在MySQL中，特定异常需要特定处理。这些异常可以联系到错误，以及子程序中的一般流程控制。定义异常是事先定义程序执行过程中遇到的问题，异常处理定义了在遇到问题时对应当采取的处理方式，并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行。

1 异常定义

1.1 语法

DECLAREcondition_nameCONDITIONFOR[condition_type];

1.2 说明

condition_name参数表示异常的名称；condition_type参数表示条件的类型，condition_type由SQLSTATE[VALUE]sqlstate_value|mysql_error_code组成：sqlstate_value和mysql_error_code都可以表示MySQL的错误；sqlstate_value为长度为5的字符串类型的错误代码；mysql_error_code为数值类型错误代码；

1.3 示例

定义“ERROR 1148(42000)”错误，名称为command_not_allowed。可以有以下两种方法：

#方法一：使用sqlstate_valueDECLAREcommand_not_allowedCONDITIONFORSQLSTATE'42000';#方法二：使用mysql_error_codeDECLAREcommand_not_allowedCONDITIONFOR1148;

2 自定义异常处理

2.1 异常处理语法

DECLAREhandler_typeHANDLERFORcondition_value[,...]sp_statement

2.2 参数说明

handler_type:CONTINUE|EXIT|UNDOhandler_type为错误处理方式，参数为3个值之一；CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误时马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL暂不支持回滚操作；condition_value:SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_codecondition_value表示错误类型；SQLSTATE[VALUE]sqlstate_value为包含5个字符的字符串错误值；condition_name表示DECLARECONDITION定义的错误条件名称；SQLWARNING匹配所有以01开头的SQLSTATE错误代码；NOTFOUND匹配所有以02开头的SQLSTATE错误代码；SQLEXCEPTION匹配所有没有被SQLWARNING或NOTFOUND捕获的SQLSTATE错误代码；mysql_error_code匹配数值类型错误代码；

2.3 异常捕获方法

方法一：捕获sqlstate_value异常这种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02"，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息DECLARECONTINUEHANDLERFORSQLSTATE'42S02'SET@info='NO_SUCH_TABLE';方法二：捕获mysql_error_code异常这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息；DECLARECONTINUEHANDLERFOR1146SET@info='NO_SUCH_TABLE';方法三：先定义条件，然后捕获异常DECLAREno_such_tableCONDITIONFOR1146;DECLARECONTINUEHANDLERFORNO_SUCH_TABLESET@info='NO_SUCH_TABLE';方法四：使用SQLWARNING捕获异常DECLAREEXITHANDLERFORSQLWARNINGSET@info='ERROR';方法五：使用NOTFOUND捕获异常DECLAREEXITHANDLERFORNOTFOUNDSET@info='NO_SUCH_TABLE';方法六：使用SQLEXCEPTION捕获异常DECLAREEXITHANDLERFORSQLEXCEPTIONSET@info='ERROR';定义条件和处理程序：mysql>createtabletest.t(s1int,primarykey(s1));mysql>delimiter//mysql>createprocedurehandlerdermo()->begin->declareCONTINUEHANDLERFORSQLSTATE'23000'set@x2=1;->set@x=1;->insertintotest.tvalues(1);->set@x=2;->insertintotest.tvalues(1);->set@x=3;->end//mysql>delimiter;mysql>callhandlerdermo();mysql>select@x;mysql>select*fromtest.t;

三、函数

函数的作用：提高代码的复用率

函数可以调用函数中的方法来实现某些功能

利用now()来实现空参数函数：

mysql>selectnow();+---------------------+|now()|+---------------------+|2018-08-1618:19:09|+---------------------+mysql>selectdate_format(now(),'%Y年%m月%d号%H点%i分%s秒');+------------------------------------------------------+|date_format(now(),'%Y年%m月%d号%H点%i分%s秒')|+------------------------------------------------------+|2018年08月16号18点19分57秒|+------------------------------------------------------+

------------------------------------------------------------------------------------

mysql>createfunctionmy_time()returnsvarchar(50)->returndate_format(now(),'%Y-%m-%d%H-%i-%s');QueryOK,0rowsaffected(0.00sec)mysql>selectmy_time();+---------------------+|my_time()|+---------------------+|2018-08-1618-22-10|+---------------------+

函数分为空参数函数和传参函数

注意：函数必需要有返回值类型用returns描述

returns后面跟的是函数体

如果函数体只有单条就直接描述

函数体如果有多条 在returns后面 begin开始 函数体结束后要写end结束

end之前一定要确定返回值

-----------------------------------------------------------------------------------------------

创建传参函数：

mysql>CREATEFUNCTIONcont_AVG(num1int,num2int)RETURNSdecimal(8,2)->RETURN(num1+num2)/2;QueryOK,0rowsaffected(0.00sec)mysql>selectcont_AVG(2,2);+---------------+|cont_AVG(2,2)|+---------------+|2.00|+---------------+1rowinset(0.00sec)mysql>selectcont_AVG(3,2);+---------------+|cont_AVG(3,2)|+---------------+|2.50|+---------------+

创建给stu表添加用户的多函数体传参函数：

mysql>descstu;+-------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+-------+|id|int(11)|NO||NULL|||name|varchar(10)|NO||NULL||+-------+-------------+------+-----+---------+-------+mysql>select*fromstu;Emptyset(0.00sec)

mysql>delimiter//mysql>createfunctionadduse(u_idintunsigned,u_namevarchar(10))->returnsintunsigned->begin->insertstuvalues(u_id,u_name);->returnlast_insert_id();->end//mysql>delimiter;mysql>selectadduse(1,'zs');+----------------+|adduse(1,'zs')|+----------------+|0|+----------------+1rowinset(0.02sec)mysql>selectadduse(2,'ls');+----------------+|adduse(2,'ls')|+----------------+|0|+----------------+1rowinset(0.01sec)mysql>selectadduse(3,'ww');+----------------+|adduse(3,'ww')|+----------------+|0|+----------------+1rowinset(0.02sec)mysql>select*fromstu;+----+------+|id|name|+----+------+|1|zs||2|ls||3|ww|+----+------+3rowsinset(0.00sec)