MySQL中的存储过程和函数

共计 11501 个字符，预计需要花费 29 分钟才能阅读完成。

• 简单的说，存储过程就是一条或者多条 SQL 语句的集合。可以视为批文件，但是其作用不仅仅局限于批处理。本文主要介绍如何创建存储过程和存储函数，以及如何调用、查看、修改、删除存储过程和存储函数等。

存储程序分为存储过程和存储函数。MySQL 创建存储过程和存储函数的语句分别为 CREATE PROCEDURE 和 CREATE FUNCTION。使用 CALL 语句来调用存储过程，只能用输出变量返回值。存储函数可以从语句外调用（通过引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。废话少说，如下步入正文。

• 创建存储过程

创建存储过程的基本语法格式为：CREATE PROCEDURE sp_name([proc_parameter]) [characteristics] routine_body

其中 CREATE PROCEDURE 为创建存储过程的关键字，sp_name 为存储过程的名称，pro_parameter 为指定存储过程的参数列表，其中参数列表如下：

1. [IN|OUT|INOUT] param_name type 其中，IN 表述输入参数，OUT 表示输出参数，INOUT 表示即可输入也可输出；param_name 表述参数名称；type 表示参数类型，该类型可以是 MySQL 数据库中的任意类型。
2. 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’：注释信息，用来描述存储过程或函数。
3. 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。 那么存储过程和函数具体的区别又是什么呢？

1. 存储过程的功能更加复杂，而函数的功能针对性更强；
2. 存储过程可以返回参数（通过 OUT|INOUT），而函数只能返回单一值或者表对象；
3. 存储过程作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于 FROM 关键字之后；
4. 存储过程是通过关键字 CALL 来调用，作为一个独立的执行部分。而存储函数则可作为 SELECT 语句的一部分调用，嵌入到 SQL 语句中；
5. 当存储过程和函数被执行的时候，SQLManager 会到 procedure cache 中去取相应的查询语句，如果在 procedure cache 里没有相应的查询语句，SQLManager 就会对存储过程和函数进行编译。

• 变量的使用

变量可以在子程序中声明并使用， 作用范围是在 BEGIN…END 程序中 ，如下将主要介绍如何定义变量和为变量赋值。

1. 定义变量。语法格式为：DECLARE var_name[,var_name]…data_type[DEFAULT value]; var_name 为局部变量名称，DEFAULT value 给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果缺少 DEFAULT 子句，初始值为 NULL。
2. 为变量赋值。MySQL 中使用 SET 语句为变量赋值，语法格式为：SET var_name=expr[,var_name=expr]…

• 流程控制的使用

流程控制与用来根据条件控制语句的执行。MySQL 中的用来构造控制流程的语句有：IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句。各语句介绍如下：

1. 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。该功能代码如下图所示：
2. CASE 语句。另外一个进行条件判断的语句，该语句有 2 种语句格式。
   1. 第一种格式为：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。
   2. 第二种格式为：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 子句里的语句被执行。
      
3. LOOP 语句。循环语句用来重复执行某些语句，与 IF 和 CASE 相比，LOOP 只是创建一个循环操作的过程，并不进行条件判断。LOOP 内的语句一直重复执行直到循环被退出。跳出循环过程使用 LEAVE 子句，LOOP 语句基本格式为：[label] LOOP statement_list END LOOP[label] label 表示 LOOP 语句的标注名称，该参数可以省略，statement_list 表示需要执行的语句。

4. LEAVE 语句。从 LOOP 语句的例子中可知 LEAVE 语句用来退出任何被标注的流程控制构造，LEAVE 语句基本格式为：LEAVE label

5. 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 时，退出循环。

6. REPEAT 语句。创建一个带条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，表达式为真循环结束，否则重复执行循环中的语句。语句基本格式：[label]:REPEAT statement_list UNTIL expr_condition END REPEAT [label]

7. 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 参数指定存储函数的特性，可能的取值有：

1. CONTAINS SQL 表示子程序包含 SQL 语句，但是不包含读或写数据的语句；
2. NO SQL 表示子程序中不包含 SQL 语句；
3. READES SQL DATA 表示子程序中包含读数据的语句；
4. MODIFIES SQL DATA 表示子程序中包含写数据的语句；
5. SQL SECURITY{DEFINER|INVOKER} 指明谁有权限来执行；
6. DEFINER 表示只有定义着自己才能够执行；
7. INVOKER 表示调用者可以执行；
8. COMMENT ‘string’ 表示注释信息。

修改存储过程使用 ALTER PROCEDURE 语句，修改存储函数使用 ALTER FUNCTION 语句。

删除存储过程和函数可以使用 DROP 语句，语法格式为：DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name 这个语句被用来移除一个存储过程或函数，sp_name 为要移除的存储过程或函数的名称。IF EXISTS 子句是一个 MySQL 的扩展，如果存储过程或函数不存在，以防发生错误，产生一个用 SHOW WARNINGS 查看的警告。

最后再说几点值得大家注意的吧：

1. 目前 MySQL 不支持对已存在的存储过程代码进行修改，如果必须修改，则先使用 DROP 语句删除该存储过程，再重新创建新的存储过程；
2. 存储过程中包含用户定义的 SQL 语句集合，也可是使用 CALL 语句调用存储过程，但不能使用 DROP 删除其他存储过程；
3. 在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开，否则会报错。
4. 如果存储过程中需要传入中文参数，这时需要在定义存储过程的时候，在参数后加上 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 为指定存储过程的参数列表，其中参数列表如下：

1. [IN|OUT|INOUT] param_name type 其中，IN 表述输入参数，OUT 表示输出参数，INOUT 表示即可输入也可输出；param_name 表述参数名称；type 表示参数类型，该类型可以是 MySQL 数据库中的任意类型。
2. 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’：注释信息，用来描述存储过程或函数。
3. 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。 那么存储过程和函数具体的区别又是什么呢？

1. 存储过程的功能更加复杂，而函数的功能针对性更强；
2. 存储过程可以返回参数（通过 OUT|INOUT），而函数只能返回单一值或者表对象；
3. 存储过程作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于 FROM 关键字之后；
4. 存储过程是通过关键字 CALL 来调用，作为一个独立的执行部分。而存储函数则可作为 SELECT 语句的一部分调用，嵌入到 SQL 语句中；
5. 当存储过程和函数被执行的时候，SQLManager 会到 procedure cache 中去取相应的查询语句，如果在 procedure cache 里没有相应的查询语句，SQLManager 就会对存储过程和函数进行编译。

• 变量的使用

变量可以在子程序中声明并使用， 作用范围是在 BEGIN…END 程序中 ，如下将主要介绍如何定义变量和为变量赋值。

1. 定义变量。语法格式为：DECLARE var_name[,var_name]…data_type[DEFAULT value]; var_name 为局部变量名称，DEFAULT value 给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果缺少 DEFAULT 子句，初始值为 NULL。
2. 为变量赋值。MySQL 中使用 SET 语句为变量赋值，语法格式为：SET var_name=expr[,var_name=expr]…

• 流程控制的使用

流程控制与用来根据条件控制语句的执行。MySQL 中的用来构造控制流程的语句有：IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句。各语句介绍如下：

1. 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。该功能代码如下图所示：
2. CASE 语句。另外一个进行条件判断的语句，该语句有 2 种语句格式。
   1. 第一种格式为：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。
   2. 第二种格式为：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 子句里的语句被执行。
      
3. LOOP 语句。循环语句用来重复执行某些语句，与 IF 和 CASE 相比，LOOP 只是创建一个循环操作的过程，并不进行条件判断。LOOP 内的语句一直重复执行直到循环被退出。跳出循环过程使用 LEAVE 子句，LOOP 语句基本格式为：[label] LOOP statement_list END LOOP[label] label 表示 LOOP 语句的标注名称，该参数可以省略，statement_list 表示需要执行的语句。

4. LEAVE 语句。从 LOOP 语句的例子中可知 LEAVE 语句用来退出任何被标注的流程控制构造，LEAVE 语句基本格式为：LEAVE label

5. 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 时，退出循环。

6. REPEAT 语句。创建一个带条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，表达式为真循环结束，否则重复执行循环中的语句。语句基本格式：[label]:REPEAT statement_list UNTIL expr_condition END REPEAT [label]

7. WHILE 语句。WHILE 语句创建一个带条件判断的循环过程，与 REPEAT 不同，WHILE 在执行语句时先对指定的表达式进行判断，为真则执行循环内的语句，否则退出循环。语句基本格式：[label] WHILE expr_condition DO statement_list END WHILE [label]

更多详情见请继续阅读下一页的精彩内容 ：http://www.linuxidc.com/Linux/2017-04/142500p2.htm