存储过程和函数语法以及调用
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名
字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象
一、创建存储过程和函数
1、创建存储过程
MySQL中创建存储过程的语句是create procedure语句，使用call语句调用存储过程。存储过程也可以调用其它存储过程。
语法：
create procedure sp_name([proc_parameter]) [characteristics...] routine_body
create procedure用来创建存储过程的关键字。sp_name是存储过程名称，proc_parameter为存储过程参数列表。列表形式如下：
[IN | OUT | OUT] param_name type
IN表示入参，OUT表示出参，INOUT表示既可以入参也可以出参。param_name表示参数名称，type表示参数类型。
characteristics指定存储的过程的特性，有以下几种取值：
（1）、LANGUAGE SQL:说明routine_body部分由SQL组成，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值
（2）、[NOT] DETERMINITIC:指明存储过程的结果是否确定，DETERMINITIC表示结果确定的。每次执行存储过程，相同的输入得到相同的输出。NOT DETERMINITIC表示结果 不确定的，相同的输出可能不同的输出，默认为NOT DETERMINITIC。
（3）、{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。
（4）、SQL SECURITY{DEFINER | INVOKER}:指明谁有执行权限。DEFINER表示定义着可以执行，INVOKER表示有权限的调用者也可以执行。默认值是DEFINER。
（5）、COMMENT 'string':注释信息，用来描述存储过程和函数
routine_body是SQL代码的内容。可以用BEGIN........END来表示SQL代码的开始和技术
例子：
create procedure test() begin select * from student; end;
2、创建函数
创建函数需要用到CREATE FUNCTION关键字。基本语法格式如下：
create function func_name([proc_parameter]) returns type [characteristics...] routine_body
create function是创建函数的关键字，func_name是函数名。proc_parameter是参数，有IN OUT INOUT三种，characteristics表示函数的特性，和存储过程一样，不再重复叙述。RETURNS type表示函数返回数据的类型。
例子：
create function test_func() returns char(50) return (select b from student where a = 1); select test_func() # 调用函数
如果return语句返回值的类型不同于returns子句指定类型的值，返回值被强制为恰当的类型。
3、变量的使用
变量可以在子程序中声明并使用，这些变量的作用范围是BEGIN.....END程序中。
（1）、变量的定义
DECLARE var_name [, var_name].. data_type [DEFAULT value]
var_name 是局部变量的名称，DEFALULT给变量一个默认值。值除了可以给常数，还可以给一个表达式。如果没有默认值，则值为NULL
(2)、为变量赋值
定义变量后，为变量赋值可以改变变量的默认值。MySQL使用set语句为变量赋值。语法格式如下：
SET var_name = expr [,var_name = expr]....
MySQL还可以通过select....into.为变量复制
select colname[,....] into var_name[,....] table_expr
4、定义条件和处理程序
特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程和函数在遇到警告或错误时继续执行。这样可以增强存储程序处理问题的能力。
（1）、定义条件，语法格式如下：
DECLARE condition_name CONDITION FOR [condition_type] [condition_type]: SQLSTATE [value] sqlstate_value | mysql_error_code
condition_name参数表示条件的名称，cindition_type参数表示条件的类型。sqlstate_value和mysql_error_code都可以表示MySQL的错误。例如ERROR1142(42000)， sqlstate_value的值是42000，mysql_error_code的值是1142。
例子：定义“ERROR 1148(42000)”错误，名称为command_not_allowed。有两种方法：
# 方法一：使用sqlstate_value DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'; # 方法二：使用mysql_error_code DECLARE command_not_allowed CONDITION FOR 1148
2、定义处理程序
定义处理程序时，使用DECLARE语法如下：
DECLARE 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
其中，handiler_type时错误处理方式，CONTINURE表示遇到错误不处理，继续执行。EXIT表示遇到错误立即退出，UNDO表示遇到错误后撤销之前的操作，MySQL暂不支持。
condition_value表示错误类型，有以下几种取值：
（1）、SQLSTATE [VALUE] sqlstatue_value:包含5个字符的字符串错误值
（2）、表示DECLARE CONDITION定义的错误条件名称
（3）、SQLWARNING:匹配所有以01开头的SQLSTATE错误代码
（4）、NOT FOUND:匹配所有以02开头的SQLSTATE错误代码
（5）、SQLEXCEPTION:匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
（6）、mysql_error_code:匹配数值类型错误代码
sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。
例子：定义处理程序的几种方式，代码如下：
# 方法一：捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSATE '42S02' SET @info='NO_SUCH_TABLE' # 方法二：捕获mysql_error_code DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE' # 方法三：先定义条件，然后调用 DECLARE no_such_table CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR no_such_table SET @info='NO_SUCH_TABLE'; # 方法四：使用SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING set @info='ERROR'; # 方法五：使用NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND set @info='NO_SUCH_TABLE'; # 方法六：使用SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION set @info='ERROR'
说明：@var_name表示用户变量，仅在当前会话有效。例：set @a = 1;
5、游标的使用
查询语句可能返回多条记录，数据量比较大。需要使用游标读取查询结果中的记录。
（1）、声明游标，语法格式如下：
DECALARE cursor_name CURSOR FOR select_satement
其中，cursor_name参数表示游标名称。select_statement参数表示SELECT语句的内容，返回一个用于创建游标的结果集。
例：
DECLARE cur_1 CURSOR FOR SELECT id,name FROM student;
（2）、打开游标，语法如下：
OPEN cursor_name;
（3）、使用游标，语法如下：
FETCH cursor_name INTO var_name[,var_name...]{参数名称}
其中，cursor_name参数表示游标的名称，var_name参数并表示将游标的查询结果存入到该参数。var_name必须事先定义好。
例：
FETCH cur_1 INTO ID,NAME;
（4）、关闭游标，语法如下;
CLOSE cursor_name;
6、流程控制
流程控制语句用来根据条件控制语句的执行。
（1）、IF语句，IF包含多个条件判断，语法如下：
IF expr_condition THEN statement_list [ELSEIF expr_condition THEN statement_list]... [ELSE statement_list] END IF;
（2）、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;
第一种，判断case_expr表达式，与WHEN后边的参数比较，如果匹配执行之后的语句，都不匹配执行ELSE之后的语句
第二种，该语句中，WHEN语句将被逐个执行，直到某个expr_condition位置。则执行其对应的语句。如果没有条件匹配，则执行ELSE的子句。
（3）、LOOP语句
LOOP循环语句用户来重复执行某些语句，LOOP内的语句一直重复执行直到循环退出，跳出循环使用LEAVE子句。LOOP的语法如下：
[loop_label:] LOOP statement_list END LOOP [loop_label]
loop_label表示LOOP语句的标注名称，该参数可以省略。statement_list表示需要循环的语句。
例：
DECALRE id INT DELAULT 0; add_loop: LOOP SET id = id + 1; IF id >= 10 THEN LEAVE add_loop; END IF; END LOOP add_loop;
（4）、lEAVE语句
LEAVE语句用来退出任何被标注的流程控制语句，语法如下：
LEAVE label;
（5）、ITERATE语句
ITERATE语句将执行顺序转到语句开头处，语法如下：
ITERATE label;
ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE表示再次循环，label参数表示循环的标志。
例：
CREATE PROCEDURE test() BEGIN DECLARE id INT DEFAULT 0; add_loop: LOOP SET id = id + 1; IF id < 10 THEN ITERATE add_loop; ELSEIF id > 20 THEN LEAVE add_loop; END IF; SELECT 'p1 is between 10 and 20'; END LOOP add_loop; END;
id = 0,如果id < 10,重复执行id加1操作。当p1大于等于10小于20,打印p1 is between 10 and 20。当id大于20，退出循环。
（6）REPEAT语句
REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，如果为真循环结束，否则重复执行循环中的语句，语法如下：
[repeat_label:] REPAET statement_list UNTIL expr_condition END REPEAT [repeat_label]
repeat_label为REPEAT语句的标注名称，可以省略。
（7）、WHILE语句
WHILE语句创建一个带条件判断的循环过程，与REPEAT不同。WHILE在执行语句时，先对指定的表达式进行判断，如果为真，执行循环内的语句，否则退出循环。语法如下：
[while_label:] WHILE expr_condition DO statement_list END WHILE [while_label]
二、查看存储过程和函数
MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS或SHOW CREATE语句来查看。也可以从系统的information_schema数据库中查询。
1、使用show status语句查看存储过程和函数的状态，语法如下：
show [procedure | function] status [like 'pattern']
2、使用show create语句查看存储过程和函数的定义
show create [procedure | function] sp_name;
3、从information_schema.Routines表中查看存储过程和函数的信息
select * from information_schema.Routines where routine_name = 'sp_name';
三、修改存储过程和函数
使用ALTER语句可以修改存储过程或函数的特性，语法如下：
ALTER [procedure | function] sp_name [characteristic...]
其中，sp_name蚕食表示存储过程或函数的名称。characteristic表示指定存储过程或函数的特性。可能的取值在上文已经描述。
四、删除存储过程和函数
使用drop语句删除存储过程或函数，语法如下：
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;
