作者：best.lei
创建存储过程和函数
创建存储过程的基本语法格式为：CREATE PROCEDURE sp_name([proc_parameter]) [characteristics] routine_body
我们从最简单的存储过程开始说起，如下是不包含任何参数的存储过程，代码为：CREATE PROCEDURE Proc() BEGIN SELECt * FROM tb_score; END; 我们定义了一个名称为Proc的存储过程，该过程是用来查询tb_score（该表接上篇博客，已存在）数据库表中的所有数据。
接下来我们讲解MySQL存储过程中的参数IN、OUT、INOUT，IN作为输入，将输入作为参数传输到存储过程的执行当中去；OUT作为输出，将存储过程的输出通过参数传出来，而INOUT参数可以同时作为输入和输出。
如我们需要查询课程号为1的学生的人数和平均成绩，则存储过程定义如下：CREATE PROCEDURE Proc_AVG(IN classID INT,OUT total INT,OUT a_s FLOAT) BEGIN SELECt COUNT(*),AVG(grade) INTO total,a_s FROM tb_score WHERe cID=classID; END;
- 创建存储函数
RETURNS type语句表示函数返回数据的类型，characteristic指定存储函数的特性，取值与创建存储过程时相同。
读者可能会发现存储过程的查询结果可能为多个值，而存储函数的查询结果是某一类型的单值。而且存储过程在调用时用CALL而存储函数是SELECt。那么存储过程和函数具体的区别又是什么呢？
- 存储过程的功能更加复杂，而函数的功能针对性更强；
- 存储过程可以返回参数（通过OUT|INOUT），而函数只能返回单一值或者表对象；
- 存储过程作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字之后；
- 存储过程是通过关键字CALL来调用，作为一个独立的执行部分。而存储函数则可作为SELECt语句的一部分调用，嵌入到SQL语句中；
- 当存储过程和函数被执行的时候，SQLManager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQLManager就会对存储过程和函数进行编译。
- 变量的使用
流程控制与用来根据条件控制语句的执行。MySQL中的用来构造控制流程的语句有：IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。各语句介绍如下：
- IF语句。包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式为 IF expr_condition THEN statement_list ESLEIF expr_condition THEN statement_list ESLE statement_list END IF 如下我们定义一个学生成绩等级评定函数，将学生成绩以参数的形式传输函数，输出学生成绩等级A(90~100)、B(75~90)、C(60~75)、D(60以下)。代码编写中需要注意，IF中如果有多个限制条件，限制条件间用AND连接，DECLARE的变量声明必须在BEGIN内，以及字符串之间拼接用CONCAT。该功能代码如下图所示：
- CASE语句。另外一个进行条件判断的语句，该语句有2种语句格式。
- 第一种格式为：CASE case_expr WHEN value THEN statement_list [WHEN value THEN statement_list] [ELSE statement_list] END CASE 其中case_expr参数表示条件判断的表达式，决定哪个WHEN子句会被执行，value表示表达式可能的值，如果case_expr等于某个value，则执行相应value后的statement_list。
- 第二种格式为：CASE WHEN expr_condition THEN statement_list [WHEN expr_condition THEN statement_list] [ELSE statement_list] END CASE 其中expr_condition参数表示条件判断语句，该格式下，WHEN语句将被逐个执行，直到某个expr_condition表达式为真，则这行对应THEN关键字后面的statement_list语句。如果没有匹配，ELSE子句里的语句被执行。
- LOOP语句。循环语句用来重复执行某些语句，与IF和CASE相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出。跳出循环过程使用LEAVE子句，LOOP语句基本格式为：[label] LOOP statement_list END LOOP[label] label表示LOOP语句的标注名称，该参数可以省略，statement_list表示需要执行的语句。
- LEAVE语句。从LOOP语句的例子中可知LEAVE语句用来退出任何被标注的流程控制构造，LEAVE语句基本格式为：LEAVE label
- ITERATE语句。将执行顺序转到语句段开头处，语句基本格式为：ITERATE lable ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为再次循环，label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。例子中p1=0，如果p1的值小于10时，重复执行p1加1操作；p1大于等于10并且小于20时，打印消息'p1 is between 10 and 20';p1大于20时，退出循环。
- REPEAT语句。创建一个带条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，表达式为真循环结束，否则重复执行循环中的语句。语句基本格式：[label]:REPEAT statement_list UNTIL expr_condition END REPEAT [label]
- WHILE语句。WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句时先对指定的表达式进行判断，为真则执行循环内的语句，否则退出循环。语句基本格式：[label] WHILE expr_condition DO statement_list END WHILE [label]
MySQL中，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看存储过程和函数，也可以直接从系统的information_schema数据库中查询。本节将通过实例来介绍这3种方法。
- SHOW STATUS语句查看存储过程和函数的状态，其基本语法为：SHOW{PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'] 这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出。PROCEDURE和FUNCTION分别表示查看存储过程和函数，LIKE语句表示匹配存储过程或函数的名称。
- SHOW CREATE查看存储过程和函数语句格式为：SHOW CREATE {PROCEDURE|FUNCTION} sp_name 它返回一个可以来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数，同样也可是使用LIKE匹配。
- 从information_schema.Routines表中查看存储过程和函数的信息。MySQL中存储过程和函数的信息存储在information_schema数据库的Routines表中。通过查询该表的记录查询存储过程和函数的信息。基本语法格式为：SELECT * FROM information_schema.Routines WHERe ROUTINE_NAME='sp_name' 其中ROUTINE_NAME字段存储的是存储过程和函数的名称，sp_name参数表述存储过程或函数的名称。
使用ALTER语句可以修改存储过程或函数的特性，本节将介绍如何通过ALTER语句修改存储过程和函数。语法格式为：ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...] 其中，sp_name参数表示存储过程或函数的名称，characteristic参数指定存储函数的特性，可能的取值有：
- ConTAINS SQL表示子程序包含SQL语句，但是不包含读或写数据的语句；
- NO SQL表示子程序中不包含SQL语句；
- READES SQL DATA表示子程序中包含读数据的语句；
- MODIFIES SQL DATA表示子程序中包含写数据的语句；
- SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行；
- DEFINER表示只有定义着自己才能够执行；
- INVOKER表示调用者可以执行；
- COMMENT 'string'表示注释信息。
删除存储过程
最后再说几点值得大家注意的吧：
- 目前MySQL不支持对已存在的存储过程代码进行修改，如果必须修改，则先使用DROP语句删除该存储过程，再重新创建新的存储过程；
- 存储过程中包含用户定义的SQL语句集合，也可是使用CALL语句调用存储过程，但不能使用DROP删除其他存储过程；
- 在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开，否则会报错。
- 如果存储过程中需要传入中文参数，这时需要在定义存储过程的时候，在参数后加上character set gbk，不然调用存储过程使用中文参数会出错。如CREATE PROCEDURE userInfo(IN u_name VARCHAr(50) character set gbk, OUT u_age INT)。
