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

9.1、创建存储过程和函数

存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用( 即通过引用函数名)，也能返回标量值。存储过程也能调用其他存储过程。

创建存储过程

创建存储过程，需要使用CREATE PROCEDURE语句，基本语法为：

CREATEPROCEDUREsp_name([proc_parameter])[characteristic...]routine_body

CREATE PROCEDURE：创建存储过程的关键字

sp_name：存储过程的名称

proc_parameter：参数列表，列表形式为 [IN|OUT|INOUT] param_name type

IN表示输入参数；OUT表示输出参数；INOUT表示既可输入又可输出；

param_name参数名称；type参数的类型，该类型可以是MySQL中的任意类型

characteristic：指定存储过程的特性

LANGUAGE SQL：说明routine_body部分是由SQL语句组成，SQL是LANGUAGE特性的唯一值

[NOT] DETERMINISTIC：指明存储过程执行的结果是否确定，DETERMINISTIC表示结果确定，每次执行存储过程时，相同输入得到相同输出；NOT DETERMINISTIC表示不确定。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA表明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER }：指明谁有权限执行。DEFINER表示只有定义者才能执行；INVOKER表示拥有权限的调用者可以执行。

COMMENT 'string'：注释信息。

routine_body：SQL代码的内容，可以用BEGIN...END来表示代码的开始和结束。

创建查看fruits表的存储过程

mysql>DELIMITER//mysql>CREATEPROCEDUREProc()->BEGIN->SELECT*FROMfruits;->END//QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;

创建名称为CountProc的存储过程

mysql>DELIMITER//mysql>CREATEPROCEDURECountProc(OUTparam1INT)->BEGIN->SELECTCOUNT(*)INTOparam1FROMfruits;->END//QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;

'DELIMITER //'作用是将MySQL的结束符设置为//，当使用DELIMITER命令时，应该避免使用反斜杠（’\’）字符，因为反斜线是MySQL的转义字符。

创建存储函数

创建存储函数，需要使用CREATE FUNCTION语句，基本语法为：

CREATEFUNCTIONfunc_name([func_parameter])RETURNStype[characteristic...]routine_body

CREATE FUNCTION：创建存储函数的关键字func_name表示存储函数的名称
func_parameter存储函数的参数列表，形式为 [IN|OUT|INOUT] param_name type

IN表示输入参数；OUT表示输出参数；INOUT表示既可输入又可输出；

param_name参数名称；type参数的类型，该类型可以是MySQL中的任意类型

RETURNS type表示函数返回数据类型characteristic指定存储函数的特性，取值和与创建存储过程相同

创建存储函数，名称为NameByZip，该函数返回SELECT语句的查询结果，数值类型为字符串型

mysql>DELIMITER//mysql>CREATEFUNCTIONNameByZip()->RETURNSCHAR(50)->RETURN(SELECTs_nameFROMsuppliersWHEREs_call='48075');->//QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;

变量的使用

在存储过程中使用DECLARE语句定义变量，语法格式为：

DECLAREvar_name[,varname]...date_type[DEFAULTvalue];

定义名称为myparam的变量，类型为INT类型，默认值为100

DECLAREmyparamINTDEFAULT100;

定义变量后，为变量赋值可以改变变量的默认信息，MySQL使用SET语句为变量赋值，语法格式为：

SETvar_name=expr[,var_name=expr]...

声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值

DECLAREvar1,var2,var3INT;SETvar1=10,var2=20;SETvar3=var1+var2;

MySQLhankeyishiyong SELECT...INTO为一个或多个变量赋值，语法为：

SELECTcol_name[,...]INTOvar_name[,...]table_expr;

声明变量fruitname和fruitprice，通过SELECT ... INTO语句查询指定记录并为变量赋值

DECLAREfruitnameCHAR(50);DECLAREfruitpriceDECIMAL(8,2);SELECTf_name,f_priceINTOfruitname,fruitpriceFROMfruitsWHEREf_id='a1';

定义条件和处理程序

特定条件需要特定处理。这些条件可以联系到错误，以及子程序的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。使用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为数值类型错误代码

定义"ERROR 1148(42000)"错误，名称为command_not_allowed

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

定义处理程序时，使用DECLARE语句的语法为：

DECLAREhandler_actionHANDLERFORcondition_valuestatementhandler_action:CONTINUE|EXIT|UNDOcondition_value:mysql_error_code|SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION

handler_action:处理错误方式，参数有3个取值：CONTINUE，EXIT，UNDO。

CONTINUE表示遇到错误不处理，继续执行

EXIT遇到错误马上退出

UNDO遇到错误后撤回之前的操作

condition_value表示错误类型

SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值

condition_name：DECLARE CONDITION定义的错误条件名称

SQLWARNING：匹配所有01开头的SQLSTATE错误代码

NOT FOUND：匹配所有02开头的SQLSTATE错误代码

SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码

mysql_error_code：匹配数值类型错误代码

statement参数为程序语句段，表示遇到定义的错误时，需要执行的存储过程或函数

定义处理程序的几种方式

//方法一：捕获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';//方法四：使用SQLWARNINGDECLAREEXITHANDLERFORSQLWARNINGSET@info='ERROR';//方法五：使用NOTFOUNDDECLAREEXITHANDLERFORNOTFOUNDSET@info='NO_SUCH_TABLE';//方法六：使用SQLEXCEPTIONDECLAREEXITHANDLERFORSQLEXCEPTIONSET@info='ERROR';

定义条件和处理程序

mysql>CREATETABLEtest.t(s1int,primarykey(s1));QueryOK,0rowsaffected(0.05sec)mysql>DELIMITER//mysql>CREATEPROCEDUREhandlerdemo()->BEGIN->DECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@x2=1;->SET@x=1;->INSERTINTOtest.tVALUES(1);->SET@x=2;->INSERTINTOtest.tVALUES(1);->SET@x=3;->END;->//QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;mysql>CALLhandlerdemo();/*调用存储过程*/QueryOK,0rowsaffected,1warning(0.02sec)mysql>SELECT@x;/*查看调用过程结果*/+------+|@x|+------+|3|+------+1rowinset(0.00sec)

'@var_name'表示用户变量，使用SET语句为其赋值，用户与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。客户端退出时，该客户端连接的所有变量自动释放。

光标的使用

查询语句可能返回多条记录，如果数据非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前声明。MySQL中光标只能在存储过程和函数中使用。

MySQL中使用DECLARE关键字声明光标，语法基本形式为：

DECLAREcursor_nameCURSORFORselect_statement

cursor_name表示光标名称

select_statement表示SELECT语句的内容

返回一个用户创建光标的结果集

声明名称为cursor_fruit的光标

DECLAREcursor_fruitCURSORFORSELECTf_name,f_priceFROMfruits;

打开光标的语法为：

OPENcursor_name[光标名称]

打开名称为cursor_fruit的光标

OPENcursor_fruit

使用光标的语法为：

FETCHcursor_nameINTOvar_name[,var_name]...[参数名称]

使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price这两个变量

FETCHcursor_fruitINTOfruit_name,fruit_price;

关闭名称为cursor_fruit的光标

CLOSEcursor_fruit;

流程控制的使用

流程控制语句用来控制条件语句的执行。MySQL终于来控制流程的于具有IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHERE语句。

IF

IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式为：

IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIF

IF语句的示例

IFvalISNULLTHENSELECT'valisNULL';ELSESELECT'valisnotNULL';ENDIF;

CASE

CASE是另一个进行条件判断的语句，该语句有两种格式：

CASEcase_valueWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASEOr:CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE

使用CASE流程控制语句第1种格式，判断val值等于1、等于2，或者两者都不等

CASEvalWHEN1THENSELECT'valis1';WHEN2THENSELECT'valis2';ELSESELECT'valisnot1or2';ENDCASE;当val值为1时，输出字符串“valis1”；当val值为2时，输出字符串“valis2”；否则输出字符串“valisnot1or2”。

使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0

CASEWHENvalISNULLTHENSELECT'valisNULL';WHENval<0THENSELECT'valislessthan0';WHENval>0THENSELECT'valisgreaterthan0';ELSESELECT'valis0';ENDCASE;当val值为空，输出字符串“valisNULL”；当val值小于0时，输出字符串“valislessthan0”；当val值大于0时，输出字符串“valisgreaterthan0”；否则输出字符串“valis0”。

LOOP

LOOP循环语句用来重复执行某些语句，与IF和CASE相比，LOOP只是创建一个循环的过程，并不进行条件判断。LOOP内的语句一直重复只i系那个知道循环被退出，跳出循环过程使用LEAVE子句，LOOP语法格式为：

[begin_label:]LOOPstatement_listENDLOOP[end_label]

使用LOOP语句进行循环操作，id值小于等于10之前，将重复执行循环过程

DECLAREidINTDEFAULT0;add_loop:LOOPSETid=id+1;IFid>=10THENLEAVEadd_loop;ENDIF;ENDLOOPadd_loop;

LEAVE

LEAVE语句用来退出任何被标注的流程控制构造，其语法格式为：

LEAVElabel

使用LEAVE语句退出循环

add_num:LOOPSET@count=@count+1;IF@count=50THENLEAVEadd_num;ENDLOOPadd_num;

ITERATE

ITERATE语句将执行顺序转到语句段开头处，语法格式为：

ITERATElabel

ITERATE只可以出现在LOOP、REPEAT、WHERE语句内。

ITERATE语句示例

CREATEPROCEDUREdoiterate()BEGINDECLAREp1INTDEFAULT0;my_loop:LOOPSETp1=p1+1;IFp1<10THENITERATEmy_loop;ELSEIFp1>20THENLEAVEmy_loop;ENDIF;SELECT'p1isbetween10and20';ENDLOOPmy_loop;END

REPEAT

REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果为真，则结束循环，其语法格式为：

[begin_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[end_label]

REPEAT语句示例，id值小于等于10之前，将重复执行循环过程

DECLAREidINTDEFAULT0;REPEATSETid=id+1;UNTILid>=10ENDREPEAT;

WHILE

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHERE在执行语句时，先对指定表达式进行判断，如果为真，则执行循环的语句，其语法格式为：

[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]

WHILE语句示例，id值小于等于10之前，将重复执行循环过程

DECLAREiINTDEFAULT0;WHILEi<10DOSETi=i+1;ENDWHILE;

9.2、调用存储过程和函数

调用存储过程

存储过程通过CALL语句进行调用，语法格式为：

CALLsp_name([parameter[,...]])

定义名为CountProc1的存储过程，然后调用这个存储过程

mysql>DELIMITER//mysql>CREATEPROCEDURECountProc1(INsidINT,OUTnumINT)->BEGIN->SELECTCOUNT(*)INTOnumFROMfruitsWHEREs_id=sid;->END//QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;mysql>CALLCountProc1(101,@num);QueryOK,1rowaffected(0.00sec)mysql>select@num;+------+|@num|+------+|3|+------+1rowinset(0.02sec)

调用存储函数

定义存储函数CountProc2，然后调用这个函数

mysql>DELIMITER//mysql>CREATEFUNCTIONCountProc2(sidINT)->RETURNSINT->BEGIN->RETURN(SELECTCOUNT(*)FROMfruitsWHEREs_id=sid);->END//QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;mysql>SELECTCountProc2(101);+--------------------+|Countproc(101)|+--------------------+|3|+-------------------+

9.3、查看存储过程和函数

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句查看，也可以直接从系统information_schema数据库中查看。

使用SHOW STATUS语句可以查看存储过程和函数状态，基本语法为：

SHOW[PROCEDURE|FUNCTION]STATUS[LIKE'pattern']

SHOW STATUS语句示例

mysql>SHOWPROCEDURESTATUSLIKE'C%'\G***************************1.row***************************Db:testName:CountProcType:PROCEDUREDefiner:root@localhostModified:2017-08-0411:32:08Created:2017-08-0411:32:08Security_type:DEFINERComment:character_set_client:utf8collation_connection:utf8_general_ciDatabaseCollation:utf8_general_ci1rowinset(0.00sec)

使用SHOW CREATE语句可以查看存储过程和函数状态，基本语法为：

SHOWCREATE[PROCEDURE|FUNCTION]sp_name

SHOW CREATE语句示例

mysql>SHOWCREATEPROCEDUREtest.CountProc\G***************************1.row***************************Procedure:CountProcsql_mode:CreateProcedure:CREATEDEFINER=`root`@`localhost`PROCEDURE`CountProc`(OUTparam1INT)BEGINSELECTCOUNT(*)INTOparam1FROMfruits;ENDcharacter_set_client:utf8collation_connection:utf8_general_ciDatabaseCollation:utf8_general_ci1rowinset(0.00sec)

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中，可以通过查询该表的记录来查询存储过程和函数，其基本语法为：

SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='sp_name';

从Routines表中查询名称为CountProc的存储过程的信息

mysql>SELECT*FROMinformation_schema.Routines->WHEREROUTINE_NAME='CountProc'ANDROUTINE_TYPE='PROCEDURE'\G***************************1.row***************************SPECIFIC_NAME:CountProcROUTINE_CATALOG:defROUTINE_SCHEMA:testROUTINE_NAME:CountProcROUTINE_TYPE:PROCEDUREDATA_TYPE:CHARACTER_MAXIMUM_LENGTH:NULLCHARACTER_OCTET_LENGTH:NULLNUMERIC_PRECISION:NULLNUMERIC_SCALE:NULLCHARACTER_SET_NAME:NULLCOLLATION_NAME:NULLDTD_IDENTIFIER:NULLROUTINE_BODY:SQLROUTINE_DEFINITION:BEGINSELECTCOUNT(*)INTOparam1FROMfruits;ENDEXTERNAL_NAME:NULLEXTERNAL_LANGUAGE:NULLPARAMETER_STYLE:SQLIS_DETERMINISTIC:NOSQL_DATA_ACCESS:CONTAINSSQLSQL_PATH:NULLSECURITY_TYPE:DEFINERCREATED:2017-08-0411:32:08LAST_ALTERED:2017-08-0411:32:08SQL_MODE:ROUTINE_COMMENT:DEFINER:root@localhostCHARACTER_SET_CLIENT:utf8COLLATION_CONNECTION:utf8_general_ciDATABASE_COLLATION:utf8_general_ci1rowinset(0.00sec)

9.4、修改存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性，基本语法为：

ALTER[PROCEDURE|FUNCTION]sp_name[characteristic...]

修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行

mysql>ALTERPROCEDURECountProc->MODIFIESSQLDATA->SQLSECURITYINVOKER;QueryOK,0rowsaffected(0.00sec)mysql>SELECTSPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE->FROMinformation_schema.Routines->WHEREROUTINE_NAME='CountProc'ANDROUTINE_TYPE='PROCEDURE';+---------------+-------------------+---------------+|SPECIFIC_NAME|SQL_DATA_ACCESS|SECURITY_TYPE|+---------------+-------------------+---------------+|CountProc|MODIFIESSQLDATA|INVOKER|+---------------+-------------------+---------------+1rowinset(0.00sec)

9.5、删除存储过程和函数

删除存储过程和函数，可以使用DROP语句，其语法格式为：

DROP[PROCEDURE|FUNCTION][IFEXISTS]sp_name

删除存储过程和存储函数

mysql>DROPPROCEDURECountProc;QueryOK,0rowsaffected(0.00sec)mysql>DROPFUNCTIONCountProc;QueryOK,0rowsaffected(0.00sec)