MySQL5.6存储过程和函数

1.存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是create procedure和create function,使用call语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（通过引用函数名），也能返回标量值。存储过程也可以调用其他的存储过程。
2.存储过程的概念
存储过程就是一条或者多条SQL语句集合，可视为批文件，但是其作用不仅仅限于批处理。
3.存储过程创建
（1）基本语法：

create procedure sp_name([proc_parameter])
[characteristics...] routine_body

create procedure:是定义存储过程的关键字。
sp_name:存储过程的名字
proc_parameter:指定存储过程的参数列表

[in|out|inout] param_name type

in表示输入参数；out表示输出参数；inout表示可以输入也可以输出；
param_name：表示参数名称；type:表示参数类型，可以是mysql中的任意类型
characteristics指定存储过程的特性：
a)language sql:说明routine body部分是由sql语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
b)[not]deterministic:指明存储过程执行的结果是否正确。DETERMINSTIC表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINSTIC表示结果是不确定的，相同的输入可能得到不同的输出，如果没有指定任意一个值，默认为NOT DETERMINISTIC。
c){CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL标明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
d)SQL SECUEITY{DEFINER|INVOKER}:指明谁有权限来执行，DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下系统指定为DEFINER。
e)COMMENT’string’注释信息，可以用来描述存储过程或者函数。
routine_body:是SQL代码内容，可以用BEGIN…AND来表示SQL代码的开始和结束。
（2）DELIMITER//语句的作用就是将mysql的结束符设置为//，因为mysql默认的语句结束符号为分号，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以END//结束存储过程，存储过程定义完毕之后在使用中DELIMITER；恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。
（3）当使用DELIMITER命令时，应该避免使用反斜杠字符（\）,因为反斜线是mysql的转义字符。
4.创建存储函数
（1）基本语法：
create function([func_parameter]) returns type [characteristic...] routine_body
解释：
create function是创建存储函数的关键字
func_name:表示存储函数的名字
func_parameter:为存储过程的参数列表，参数列表形式如下：[in|out|inout] param_type
其中in表示参数输入，out表示参数输出，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数类型；该类型可以是mysql数据库中的任意类型。
returns type:表示函数返回的数据类型；characteristic指定存储函数的特性，取值与创建存储过程时相同。
（2）如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定的类型值，返回值将被清只为恰当的类型。比如，如果一个函数返回一个ENUM或者SET值，但是RETURN语句返回一个整数，对于SET成员集相应的ENUM成员，从函数返回的值是字符串。
（3）指定参数IN,OUT,INOUT只对PROCEDURE是合法的，RETURNS子句只能对FUNCTION做指定，对于函数而言，是强制的，他用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。
5.变量
（1）变量的作用范围BEGIN…AND之间
（2）定义变量：

DECLARE var_name,...data_type[default value];

var_name：变量的名字
default value:给变量提供一个默认值，除了可以声明为一个常数外，还可以指定为一个表达式，如果没有default子句，初始值为null。
（2）变量赋值

set var_name=expr[,var_name=expr]...;

在存储程序中的set语句是一般SET语句的扩展版本，被参考变量可能是子程序内声明的变量，或是全局服务器变量。在存储过程程序中的SET语句作为预先存在的SET语法的一部分实现。
6.定义条件和处理程序
（1）定义条件概念：定义条件是事先定义程序执行过程中遇到的问题。
（2）处理程序概念：处理程序定义了在遇到这些问题时应该采用的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序有异常停止运行。
（3）定义条件语法

declare condition_name CONDITION FOR[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:数值类型错误代码
（4）定义处理程序

delcare handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
CONTINUE|EXIT|UNDO
condition_value:
	SQLSTATE[VALUE]sqlstate_value
	|condition_name
	|SQLWARNING
	|NOT FOUND
	|SQLEXCEPTION
	|mysql_error_code

handler_type:错误处理方式，三个取值CONTINUE,EXIT,UNDO。
CONTINUE:遇到错误不处理；EXIT:遇到错误退出；UNDO:遇到错误后撤回之间的操作，MYSQL中暂时不支持这样的操作。
condition_value表示错误类型，有这些取值：
SQLSTATE[VALUE] sqlstate_value包含5个字符的字符串错误值
condition_name表示DECLARE CONDITION定义的错误条件名称
SQLWARNING:匹配所有以01开头的SQLSTATE错误代码
NOT FOOUND匹配所有以02开头的SQLSTATE错误代码
SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
mysql_error_code匹配数值类型错误代码
sql_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或者函数。
7.光标
（1）适用场景:
当查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据.
（2）光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

declare cursor_name cursor for select_statement

cursor_name：表示游标的名字；select_statement表示select语句的内容，返回创建一个用于创建游标的结果集。
（3）打开光标open cursor_name{光标名称}
注意：一定要是先声明游标，然后再打开游标，否则会报错误。
（4）使用游标

fetch cursor_name into var_name [,var_name]...{参数名称}

cursor_name游标的名称；var_name:表示将游标中的SELECT语句查询出来的信息存储在该参数中，var_name必须声明在游标之前就定义好。
（5）关闭游标close cursor_name{游标名称};
注意：这个语句关闭先前打开的游标，如果没有被明确的关闭，游标在它被声明的符合语句的末尾被关闭。
（6）mysql中的游标只能在存储过程和函数中使用
8.流程控制的使用
（1）流程控制语句用来根据条件控制语句的执行。mysql中的用来构造控制流程的语句有：IF语句，CASE语句,LOOP语句,WHILE语句,LEAVE语句,ITERATE语句,REPEAT语句。每个流程中可能包含一个单独的语句或者是使用BEGIN…END构造的符合语句，构造可以被嵌套。
（2）IF语句
包含多个条件判断，根据判断的结果为TRUE或者FALSE执行相应的语句。

IF expr_condition THEN statement_list
[ELESEIF expr_condition THEN statement_list]...
[ELESE statement_list]
END IF

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

CASE case_expr
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE

CASE
WHEN expr_condition THEN statement_list
[WHEN expr_condition THEN statement_list]
[ELSE statement_list]
END CASE

（4）LOOP语句
LOOP循环语句用来重复执行某些语句。LOOP只是创建一个循环操作过程，并不进行条件判断。LOOP内的语句一直重复执行知道循环被退出，跳出循环过程，使用LEAVE子句，LOOP基本格式

[loop:label:]LOOP
statement_list
END LOOP[loop_label]

（5）LEAVE语句
LEAVE语句用来退出任何被标注的流程控制构造，LEAVE语句基本格式如下:LEAVE label
LEAVE和BEGIN…AND或循环一起使用。
（6）ITERATE语句
将执行顺序转到语句段开头处
基本语法格式：ITERATE label
ITERATE只可以出现在LOOP,REPEAT和WHILE语句内，ITERATE再次循序，这个语句必须跟在循环标志前面。
（7）REPEAT语句
创建一个带条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。REPEAT语句基本格式：

[repeat_label:]REPEAT
statement_list
UNTIL expr_condition
END REPEAT[repeat_label]

（8）WHILE语句
创建一个待条件判断的循环过程，与REPEAT不同，WHILE在执行语句时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。
WHILE语句的基本格式：

[while_label:]WHILE expr_condition DO
statement_list
END WHILE[while_label]

9.存储过程和函数的调用
（1）存储过程必须使用CALL来调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，比如CALL dbname.proname。存储函数调用与MYSQL中预定义的函数的调用方式一样。
（2）调用存储过程CALL sp_name([parameter[,...]])
CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程，其中sp_name是存储过程的名称，parameter是存储过程的参数。
（3）调用存储函数
存储函数和Mysql内部函数调用方式时一样的，区别在于存储函数是用户自己定义的，mysql内部函数是有开发者定义的。
10.查看存储过程和函数
（1）查看存储过程和函数状态
show {procedure|function} status [like 'pattern']
like语句表示匹配存储过程或者函数的名字
（2）查看存储过程和函数的定义

show create {procedure|function} sp_name

（3）从information_schema.Routines表中查看存储过程和函数的信息
因为MYSQL中存储过程和函数的信息都存储在information_schema数据库下的routines表中。可以通过查询这个表的记录来查询存储过程和函数信息。

SELECT * FROM information_schema.Routines
WHER ROUTINE_NAME='sp_name';

ROUTINE_NAME:存储的是存储过程和函数的名称；SP_NAME参数表示存储过程或者函数的名称。
在Information_schema数据库下的Routines表中，存储所有的存储过程和函数的定义，SELECT语句查询Routines表中的存储过程和函数定义时，一定要使用ROUTINE_NAME字段指定存储过程或函数名称。否则将查询出所有的存储过程或函数的定义。如果存储过程和存储函数的名字相同，则需要同时指定ROUTINE_TYPE字段表名查询的是哪种类型的存储程序。
11.修改存储过程和函数
（1）基本语法：

alter {procedure|function} sp_name [characteristic...] 

characteristic的特性可能的取值：
CONTAINS SQL:子程序包含SQL语句，但不包含读或写的语句
NO SQL:子程序中不包含SQL语句
READS SQL DATA:子程序中包含读数据的语句
MODIFIES SQL DATA:子程序中包含写数据的语句
SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行。
DEFINER：表示只有定义者自己可以执行
COMMENT’string’：表示注释信息
12.删除存储过程和函数
基本的语法：drop {procedure|function} [if exists] sp_name
其中if exists子句是Mysql的扩展，如果程序或者函数不存储，他可防止发生错误，产生一个SHOW WARNINGS查看的警告。