MySQL存储过程和函数.docx
MySQL存储过程和函数、基本介绍存储过程和函数是事先经过编译并存储在数据库中的段SQL语句的集合，减少数据在数据库和应服务之间的传输，对于提数据处理的 效率是有好处的。存储过程和函数的区别在于函数必须有返回值，存储过程没有，存储过程的参数可以使IN、OUT、INOUT类型，函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL，可能需要将函数改造成存储过程。存储过程和函数允许包含DDL语句，也允许使事务，还可以调其他的存储过程和函数，但不允许执Load Data Infile 语句；、相关操作创建存储过程或函数需要 CREATE ROUTINE 权限，修改或删除存储过程或函数需要 ALTER ROUTINE 权限，执存储过程或函数需要EXECUTE 权限。创建、修改存储过程或函数Create procedure sp_name（proc_parameter,） characteristic routine_bodyCreate function sp_name（func_parameter,） Returns typecharacteristic routine_body Return xxx调语法：call sp_name(parameter,)参数说明：pro_parameter IN | OUT | INOUT param_name type func_parameterparam_name type存储过程和函数中不允许执 LOAD DATA INFILE 语句。Delimiter $修改命令结束符Characteristic特征值：Language sql 说明下body是使sql编写，系统默认Sql security definer | invoker 可以指定程序该创建程序者的许可来执还是使调者的权限执。默认是definer Comment string 存储过程或函数的注释信息 Contains sql | no sql | reads sql data | modifies sql data供程序使数据的内在信息，前只提供给服务器，并没有根据这些特征值来约束过程实际使数据的情况，默认是contains sql；1. Contains sql 表程序不包含读或写数据的语句。2. No sql 表程序不包含sql语句。3. Reads sql data 表程序包含读数据的语句，但不包含写数据的语句。4. Modifies sql data 表程序包含写数据的语句。实例：返回值 xxx删除存储过程或函数：次只能删除个存储过程或函数，需 ALTER ROUTINE 权限Drop procedure name；查看存储过程或者函数：查看存储过程或函数的状态：Show procedure | function status like pattern;查看存储过程或函数的定义：Show create procedure | function name；通过查看information_schema.Routines了解存储过程和函数的信息Select * from Routines where routine_name = “name”;三、变量的使 变量不区分写变量的定义Declare 定义个局部变量，作域在 BEGIN END 块中，可以在嵌套的块中。必须写在复合语句的开头，并且在任何其他语句的前。可次声明多个相同类型的变量。如需要，可以使default赋默认值。Declare var_name, type default value;变量的赋值变量可以直接赋值，或者通过查询赋值。直接赋值使set，可以赋常量或者赋表达式。Set var_name = expr ,var_name = expr Select col_name , INTO var_name , from xxx.; #查询结果必须只有Set a = xxx; 相当于全局变量定义条件和处理：处理过程中遇到问题时相应的处理步骤。条件定义Declare condition_name CONDITION FOR condition_value条件处理（游标中有实例）Declare handler_type HANDLER FOR condition_value , sp_statement说明：Handler_type 前持 continue 和 exit ，continue继续执下的语句，exit表终。Condition_value 值可以通过declare定义的 condition_name，可以是SQLSTATE 的值或者mysql-error-code的值或SQLWARING、NOT FOUND、SQLEXECEPTION ，这3个值是3种定义好的错误类别。1. SQLWARING 是对所有以01开头的SQLSTATE 代码速记2. NOT FOUND 是对所有以02开头的SQLSTATE 代码速记3. SQLEXCEPTION 是对所有没有被SQLWARING 或 NOT FOUND 捕获的SQLSTATE 代码速记4.四、游标的使对结果集进循环的处理，包括光标的声明、open、fetch 和 close。1条sql，对应N条结果集的资源，取出资源接/句柄，就是游标，沿着游标，可以次取出1。好处是，每的处理权利在我们中。 游标通俗来讲相当于你买东西别件件的给你，不是下全给你。执没有数据错误： 修改存储过程：BEGINDECLARE row_id int; DECLARE row_dt varchar(50); DECLARE row_catalog int; DECLARE row_total int; DECLARE i int default 1;DECLARE getArticle CURSOR FOR select id,dt,catalog from tblarticle where catalog = 75; select count(*) INTO row_total from tblarticle where catalog=75;OPEN getArticle;WHILE i<=row_total DOFETCH getArticle into row_id, row_dt, row_catalog;SELECT row_id, row_dt, row_catalog; set i = i+1;END WHILE;CLOSE getArticle;ENDBEGINDECLARE row_id int; DECLARE row_dt varchar(50); DECLARE row_catalog int; DECLARE row_total int; DECLARE i int DEFAULT 1;DECLARE getArticle CURSOR FOR select id,dt,catalog from tblarticle where catalog = 75; DECLARE EXIT HANDLER FOR NOT FOUND set i = 0;OPEN getArticle;REPEATFETCH getArticle into row_id, row_dt, row_catalog;SELECT row_id, row_dt, row_catalog;UNTIL i = 0 END REPEAT;CLOSE getArticle;ENDDeclare continue/exit handler for not found close 游标名;Continue 和 exit 的区别：游标循环读取的正确逻辑：五、流程控制If 、case、loop、leave、iterate、repeat、while 语句If语句IF search_condition THEN statement_listELSEIF search_condition THEN statement_list ELSE statement_listEND IF ;While语句WHILE search_condition Do Statement_listEND WHILE end_label ;case语句repeat语句（类似dowhile ）While 和 repeat 的区别：While 是满条件才执循环，repeat是满条件退出循环；While在次循环执之前就判断条件，所以循环最少执0次，repeat是在次执循环之后才判断条件，类似dowhile ，所以循坏最少执1次；
