共计 11501 个字符，预计需要花费 29 分钟才能阅读完成。
| 存储过程和函数 |
- 简单的说，存储过程就是一条或者多条 SQL 语句的集合。可以视为批文件，但是其作用不仅仅局限于批处理。本文主要介绍如何创建存储过程和存储函数，以及如何调用、查看、修改、删除存储过程和存储函数等。
| 创建存储过程和函数 |
存储程序分为存储过程和存储函数。MySQL 创建存储过程和存储函数的语句分别为 CREATE PROCEDURE 和 CREATE FUNCTION。使用 CALL 语句来调用存储过程，只能用输出变量返回值。存储函数可以从语句外调用（通过引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。废话少说，如下步入正文。
- 创建存储过程
创建存储过程的基本语法格式为：CREATE PROCEDURE sp_name([proc_parameter]) [characteristics] routine_body
其中 CREATE PROCEDURE 为创建存储过程的关键字，sp_name 为存储过程的名称，pro_parameter 为指定存储过程的参数列表，其中参数列表如下：
- [IN|OUT|INOUT] param_name type 其中，IN 表述输入参数，OUT 表示输出参数，INOUT 表示即可输入也可输出；param_name 表述参数名称；type 表示参数类型，该类型可以是 MySQL 数据库中的任意类型。
- characteristics 指定存储过程的特性，有以下取值：
- LANGUAGE SQL: 说明 routine_body 部分是由 SQL 语句组成的，当前系统支持的语言为 SQL，SQL 是 LANGUAGE 特性的唯一值。
- [NOT] DETERMINISTIC: 指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出，NOT 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 表名子程序包含写数据的语句。默认情况下，系统会指定为 CONTAINS SQL。
- SQL SECURITY[DEFINER|INVOKER]：指明谁有权限来执行。DEFINER 表示只有定义着才能执行。INVOKER 表示用友权限的调用者可以执行。默认情况下，系统指定为 DEFINER。
- COMMENT ‘string’：注释信息，用来描述存储过程或函数。
- routine_body 是 SQL 代码的内容，可以用 BEGIN…END 来表示 SQL 代码的开始和结束。
我们从最简单的存储过程开始说起，如下是不包含任何参数的存储过程，代码为：CREATE PROCEDURE Proc() BEGIN SELECT * FROM tb_score; END; 我们定义了一个名称为 Proc 的存储过程，该过程是用来查询 tb_score（该表接上篇博客，已存在）数据库表中的所有数据。
第一张图是通过执行 sql 语句查询到 tb_score 表中的数据。第二张图是创建存储过程，其中第一句 DELIMITER // 是将 MySQL 的结束符设置为 //，因为 MySQL 默认的结束符为分号，为了避免与存储过程中 SQL 语句结束符相冲突，需要 DELIMITER 改变存储过程的结束符，并以“END //”结束存储过程。第三张图是调用存储过程，在调用存储过程之前先将 MySQL 结束符恢复为默认的分号（DELIMITER ;）然后通过 CALL Proc() 调用。
接下来我们讲解 MySQL 存储过程中的参数 IN、OUT、INOUT，IN 作为输入，将输入作为参数传输到存储过程的执行当中去；OUT 作为输出，将存储过程的输出通过参数传出来，而 INOUT 参数可以同时作为输入和输出。
还是通过存储过程查询 tb_score 表，不过这次我们要查询课程号为 1（cID=1）的所有学生的成绩，存储过程定义为：CREATE PROCEDURE Proc_cID(IN classID INT) BEGIN SELECT * FROM tb_score WHERE cID=classID; END;
如我们需要查询课程号为 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;
- 创建存储函数
创建存储函数需要使用 CREATE FUNCTION 语句，基本语法格式为：CREATE FUNCTION func_name([func_parameter]) returns type [characteristic] routine_body CREATE FUNCTION 为用来创建存储函数的关键字，func_name 表示存储函数的名称，func_parameter 为存储过程的参数列表如下：
- [IN|OUT|INOUT] param_name type 其参数含义同存储过程（PROCEDURE）相同，其默认为 IN 参数。
RETURNS type 语句表示函数返回数据的类型，characteristic 指定存储函数的特性，取值与创建存储过程时相同。
查询某个学生某门课程的成绩函数代码为：CREATE FUNCTION Query_score(classID INT,studentID INT) RETURNS INT RETURN (SELECT grade FROM tb_score WHERE cID=classID AND sID=studentID); 通过 SELECT Query_score(1,1) 查询 1 号学生 1 号课程的成绩。
读者可能会发现存储过程的查询结果可能为多个值，而存储函数的查询结果是某一类型的单值。而且存储过程在调用时用 CALL 而存储函数是 SELECT。 那么存储过程和函数具体的区别又是什么呢？
- 存储过程的功能更加复杂，而函数的功能针对性更强；
- 存储过程可以返回参数（通过 OUT|INOUT），而函数只能返回单一值或者表对象；
- 存储过程作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于 FROM 关键字之后；
- 存储过程是通过关键字 CALL 来调用，作为一个独立的执行部分。而存储函数则可作为 SELECT 语句的一部分调用，嵌入到 SQL 语句中；
- 当存储过程和函数被执行的时候，SQLManager 会到 procedure cache 中去取相应的查询语句，如果在 procedure cache 里没有相应的查询语句，SQLManager 就会对存储过程和函数进行编译。
- 变量的使用
变量可以在子程序中声明并使用， 作用范围是在 BEGIN…END 程序中 ，如下将主要介绍如何定义变量和为变量赋值。
- 定义变量。语法格式为：DECLARE var_name[,var_name]…data_type[DEFAULT value]; var_name 为局部变量名称，DEFAULT value 给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果缺少 DEFAULT 子句，初始值为 NULL。
- 为变量赋值。MySQL 中使用 SET 语句为变量赋值，语法格式为：SET var_name=expr[,var_name=expr]…
- 流程控制的使用
流程控制与用来根据条件控制语句的执行。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]
更多详情见请继续阅读下一页的精彩内容 ：http://www.linuxidc.com/Linux/2017-04/142500p2.htm
| 查看存储过程和函数 |
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’ 表示注释信息。
修改存储过程使用 ALTER PROCEDURE 语句，修改存储函数使用 ALTER FUNCTION 语句。
| 删除存储过程 |
删除存储过程和函数可以使用 DROP 语句，语法格式为：DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name 这个语句被用来移除一个存储过程或函数，sp_name 为要移除的存储过程或函数的名称。IF EXISTS 子句是一个 MySQL 的扩展，如果存储过程或函数不存在，以防发生错误，产生一个用 SHOW WARNINGS 查看的警告。
最后再说几点值得大家注意的吧：
- 目前 MySQL 不支持对已存在的存储过程代码进行修改，如果必须修改，则先使用 DROP 语句删除该存储过程，再重新创建新的存储过程；
- 存储过程中包含用户定义的 SQL 语句集合，也可是使用 CALL 语句调用存储过程，但不能使用 DROP 删除其他存储过程；
- 在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开，否则会报错。
- 如果存储过程中需要传入中文参数，这时需要在定义存储过程的时候，在参数后加上 character set gbk，不然调用存储过程使用中文参数会出错。如 CREATE PROCEDURE userInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)。
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-04/142500.htm
| 存储过程和函数 |
- 简单的说，存储过程就是一条或者多条 SQL 语句的集合。可以视为批文件，但是其作用不仅仅局限于批处理。本文主要介绍如何创建存储过程和存储函数，以及如何调用、查看、修改、删除存储过程和存储函数等。
| 创建存储过程和函数 |
存储程序分为存储过程和存储函数。MySQL 创建存储过程和存储函数的语句分别为 CREATE PROCEDURE 和 CREATE FUNCTION。使用 CALL 语句来调用存储过程，只能用输出变量返回值。存储函数可以从语句外调用（通过引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。废话少说，如下步入正文。
- 创建存储过程
创建存储过程的基本语法格式为：CREATE PROCEDURE sp_name([proc_parameter]) [characteristics] routine_body
其中 CREATE PROCEDURE 为创建存储过程的关键字，sp_name 为存储过程的名称，pro_parameter 为指定存储过程的参数列表，其中参数列表如下：
- [IN|OUT|INOUT] param_name type 其中，IN 表述输入参数，OUT 表示输出参数，INOUT 表示即可输入也可输出；param_name 表述参数名称；type 表示参数类型，该类型可以是 MySQL 数据库中的任意类型。
- characteristics 指定存储过程的特性，有以下取值：
- LANGUAGE SQL: 说明 routine_body 部分是由 SQL 语句组成的，当前系统支持的语言为 SQL，SQL 是 LANGUAGE 特性的唯一值。
- [NOT] DETERMINISTIC: 指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出，NOT 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 表名子程序包含写数据的语句。默认情况下，系统会指定为 CONTAINS SQL。
- SQL SECURITY[DEFINER|INVOKER]：指明谁有权限来执行。DEFINER 表示只有定义着才能执行。INVOKER 表示用友权限的调用者可以执行。默认情况下，系统指定为 DEFINER。
- COMMENT ‘string’：注释信息，用来描述存储过程或函数。
- routine_body 是 SQL 代码的内容，可以用 BEGIN…END 来表示 SQL 代码的开始和结束。
我们从最简单的存储过程开始说起，如下是不包含任何参数的存储过程，代码为：CREATE PROCEDURE Proc() BEGIN SELECT * FROM tb_score; END; 我们定义了一个名称为 Proc 的存储过程，该过程是用来查询 tb_score（该表接上篇博客，已存在）数据库表中的所有数据。
第一张图是通过执行 sql 语句查询到 tb_score 表中的数据。第二张图是创建存储过程，其中第一句 DELIMITER // 是将 MySQL 的结束符设置为 //，因为 MySQL 默认的结束符为分号，为了避免与存储过程中 SQL 语句结束符相冲突，需要 DELIMITER 改变存储过程的结束符，并以“END //”结束存储过程。第三张图是调用存储过程，在调用存储过程之前先将 MySQL 结束符恢复为默认的分号（DELIMITER ;）然后通过 CALL Proc() 调用。
接下来我们讲解 MySQL 存储过程中的参数 IN、OUT、INOUT，IN 作为输入，将输入作为参数传输到存储过程的执行当中去；OUT 作为输出，将存储过程的输出通过参数传出来，而 INOUT 参数可以同时作为输入和输出。
还是通过存储过程查询 tb_score 表，不过这次我们要查询课程号为 1（cID=1）的所有学生的成绩，存储过程定义为：CREATE PROCEDURE Proc_cID(IN classID INT) BEGIN SELECT * FROM tb_score WHERE cID=classID; END;
如我们需要查询课程号为 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;
- 创建存储函数
创建存储函数需要使用 CREATE FUNCTION 语句，基本语法格式为：CREATE FUNCTION func_name([func_parameter]) returns type [characteristic] routine_body CREATE FUNCTION 为用来创建存储函数的关键字，func_name 表示存储函数的名称，func_parameter 为存储过程的参数列表如下：
- [IN|OUT|INOUT] param_name type 其参数含义同存储过程（PROCEDURE）相同，其默认为 IN 参数。
RETURNS type 语句表示函数返回数据的类型，characteristic 指定存储函数的特性，取值与创建存储过程时相同。
查询某个学生某门课程的成绩函数代码为：CREATE FUNCTION Query_score(classID INT,studentID INT) RETURNS INT RETURN (SELECT grade FROM tb_score WHERE cID=classID AND sID=studentID); 通过 SELECT Query_score(1,1) 查询 1 号学生 1 号课程的成绩。
读者可能会发现存储过程的查询结果可能为多个值，而存储函数的查询结果是某一类型的单值。而且存储过程在调用时用 CALL 而存储函数是 SELECT。 那么存储过程和函数具体的区别又是什么呢？
- 存储过程的功能更加复杂，而函数的功能针对性更强；
- 存储过程可以返回参数（通过 OUT|INOUT），而函数只能返回单一值或者表对象；
- 存储过程作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于 FROM 关键字之后；
- 存储过程是通过关键字 CALL 来调用，作为一个独立的执行部分。而存储函数则可作为 SELECT 语句的一部分调用，嵌入到 SQL 语句中；
- 当存储过程和函数被执行的时候，SQLManager 会到 procedure cache 中去取相应的查询语句，如果在 procedure cache 里没有相应的查询语句，SQLManager 就会对存储过程和函数进行编译。
- 变量的使用
变量可以在子程序中声明并使用， 作用范围是在 BEGIN…END 程序中 ，如下将主要介绍如何定义变量和为变量赋值。
- 定义变量。语法格式为：DECLARE var_name[,var_name]…data_type[DEFAULT value]; var_name 为局部变量名称，DEFAULT value 给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果缺少 DEFAULT 子句，初始值为 NULL。
- 为变量赋值。MySQL 中使用 SET 语句为变量赋值，语法格式为：SET var_name=expr[,var_name=expr]…
- 流程控制的使用
流程控制与用来根据条件控制语句的执行。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]
更多详情见请继续阅读下一页的精彩内容 ：http://www.linuxidc.com/Linux/2017-04/142500p2.htm
