MySQL存储过程与存储函数基础详解

前言

我们大家都对MySQL函数不陌生，例如我们使用now函数可以查看当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-05-17 11:05:15 |
+---------------------+
1 row in set (0.00 sec)

和c，java，python一样，MySQL也可以自己创建函数，实现一些特定功能，存储过程与存储函数是一组可编程的函数，是为了完成特定功能的SQL语句集，创建并保存在数据库中，用户可通过指定存储过程与存储函数的名称并给定参数(如果需要的话)来调用执行。在基础阶段，我们完全可以把存储过程和存储函数看成相同的封装起来的封装体，因为过程就像一个没有返回值的函数，而函数不过是有返回值的过程。

创建存储过程

创建存储过程，需要使用CREATE PROCEDURE语句：

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表示参数的类型，例如int等MySQL数据库中任意的数据类型

characteristics指定存储过程的特性，有以下取值：

1. LANGUAGE SQL：说明routine_body部分是由SQL语句组成，当前系统支持的语言为SQL，SQL是LANGUAGE特性唯一值；
2. [NOT] DETERMINISTIC：知名存储过程执行的结果是否正确，DETERMINISTIC表示结果确定的，每次执行存储过程，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果不确定，相同的输入可能得到不同的输出，如果没有指定任意一个值，默认为NOT DETERMINISTIC；
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'：注释信息，用于描述

目前阶段，characteristics是创建过程/函数中的可选项，这部分内容我们是用不到的

routine_body是SQL代码内容，需要用BEGIN和END来表示SQL代码的开始和结束

例1—创建存储过程

创建一个过程，该过程可以需要传入一个参数，每次调用这个过程之后，实现为这个参数数值加1的功能

mysql> #delimite可以将MySQL中默认分号（;）结束符号替换成自定义符号
mysql> #这么做是为了保证存储过程内的SQL语句能够使用分号结束
mysql> delimiter //
mysql> #创建名为t1的存储过程
mysql> #存储过程的核心功能在begin...end的范围内
mysql> create procedure t1(in x int)
    -> begin
    -> select x+1;
    -> end //
Query OK, 0 rows affected (0.01 sec)
mysql> #调用存储过程，并且传入一个参数x=10
mysql> call t1(10) //
+------+
| x+1  |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

delimiter // 语句的作用是将MySQL的结束符设置为// 因为MySQL默认结束符号是";"，为了避免存储过程与SQL语句冲突，需要使用delimiter暂时改变结束符，并以END // 结束存储过程

上述代码，存储过程名为t1，并且指定了一个传入（in）的整型（int）类型的参数，参数名为x；begin表示SQL代码开始，end表示SQL代码结束，begin与end之间即为存储过程的核心代码，是一系列SQL代码的集合，可以完成一个比较复杂的功能，在上面的例子中，将传入的参数x进行数值加1操作，再输出

存储过程的调用使用call，和调用函数类似，也是过程名(参数)的形式，如果没有参数，使用过程名()，调用存储过程和函数的具体我会在下面详述

例2—创建存储过程

创建一个过程，这个过程可以输出一个参数，因此我们可以通过调用一次这个存储过程生成我们需要的参数

mysql> delimiter //
mysql> #创建存储过程
mysql> #out表明了这个过程将会有一个输出的参数
mysql> create procedure t2(out x int)
    -> begin
    -> set x=100;
    -> end//
Query OK, 0 rows affected (0.01 sec)
mysql> #使用@p接收存储过程输出的那个参数
mysql> call t2(@p)//
Query OK, 0 rows affected (0.00 sec)
mysql> #过程调用成功之后，之后就可以任意使用@p的值
mysql> #来查看一下@p的值是多少
mysql> select @p //
+------+
| @p   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

上述代码，存储过程名为t2，并且会传出（out）一个整型（int）类型的参数，参数名为x；在过程中使用：

set x=100;

这是MySQL中的赋值语句，将参数x赋值为100，之后这个参数就可以在被外部调用过程时所使用，在使用

call t2(@p)

call调用存储过程后，又将参数值赋值给@p，调用成功之后，之后随时可以使用这个变量@p

@符号表示用户变量，使用set为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其它客户端看到，客户端退出时，该客户端连接的所有用户变量自动释放，用户变量可以随时随地被创建并定义

创建存储函数

存储函数和now()，sum()，max()，count()函数一样，本质上就是函数，你平时怎么使用函数，就用相同的方法去操作你自己的存储函数，创建存储函数的格式：

create function sp_name ([func_parameter])
returns type
[characteristics...] routine_body

其中，create function为用来创建存储函数的关键字，sp_name为存储函数的名称，func_parameter为指定存储过程的参数列表，参数的格式为：

param_name type

和存储过程不同 ，存储过程的参数有传入IN，传出OUT和传入传出INOUT，但是 对于存储函数来说，只有传出参数的操作 ，并且不需要也不能使用IN特别说明，param_name表示参数名称，type表示参数的类型，例如int类型等MySQL数据库中任意的类型；

returns type表示函数有一个返回值，type表明了返回数据的类型；

characteristics表示指定存储函数的特性，与存储过程相同，不再赘述，在现阶段是用不到的

routine_body也和存储过程一样，是SQL代码内容，需要用BEGIN和END来表示SQL代码的开始和结束

例1—创建存储函数

创建一个存储函数，这个函数会返回一个字符串

mysql> delimiter //
mysql> #创建一个名为t1的存储函数
mysql> create function t1()
    -> returns varchar(50)
    -> begin
    -> return "Hello World";
    -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> #就像调用其它函数一样，可以直接使用select调用
mysql> select t1() //
+-------------+
| t1()        |
+-------------+
| Hello World |
+-------------+
1 row in set (0.00 sec)
mysql> #也可以像调用其它函数一样作为一个参数传入其它函数
mysql> select concat("<",t1(),">") //
+----------------------+
| concat("<",t1(),">") |
+----------------------+
| <Hello World>        |
+----------------------+
1 row in set (0.00 sec)

注意在创建函数时，如果你的MySQL报错：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA 
in its declaration and binary logging is enabled (you *might* want to use the less safe 
log_bin_trust_function_creators variable)

那么有两种解决方法，第一种，在存储函数的特性（characteristics）部分加上READS SQL DATA说明子程序包含读数据的语句，所以上面的示例应该改成：

mysql> create function t1()
    -> returns varchar(50)
    -> READS SQL DATA
    -> begin
    -> return "Hello World";
    -> end //
Query OK, 0 rows affected (0.01 sec)

第二种，在配置文件中添加

# 在创建存储函数时，可以不使用characteristics
log_bin_trust_function_creators=1

重启MySQL服务器之后即可，不需要添加特性的内容也可以创建成功了

到这里小结一下，存储过程和存储函数的主要区别：

1. 参数：存储过程对待参数有三种方式：输入（IN），输出（OUT）和输入并输出（INOUT），因为有三种方式所以必须对参数指明其用途；对于存储函数只有一种方式：输入参数，因此不需要写IN；
2. 存储函数有一个returns type（注意这里的return后加上s），存储过程没有也不需要这个返回语句。另外需要说明的是，returns只能对function做指定，对函数而言这是 强制 的，它用来指定函数的返回类型，因此函数体内必须包含一个return值，即一句return的代码
3. 调用存储过程使用call，调用存储函数可以直接使用select查看函数返回值，也可以作为其它函数的参数

调用存储过程和函数

存储过程和函数有多种调用方法，存储过程必须使用 call语句 调用，而且存储过程和数据库相关，如果要执行其它数据库中的存储过程，需要指定数据库名，例如CALL dbname.sp_name。存储函数的调用与预定义的函数调用方法相同

调用存储过程

mysql> call sp_name([parameter[,...])

在存储过程中的参数parameter部分（如果有的话），如果是输入（IN）的参数，需要直接按照对应的数据类型放入对应的位置，如果是输出（OUT）的参数，可以使用@var_name用户变量接收

调用存储函数

在MySQL中，存储函数的使用方法和MySQL内部的函数使用方法是一样的，虽然存储函数和存储过程的定义有所不同，但是可以实现相同的功能，在实际应用中需要灵活选择

查看存储过程和函数

SHOW STATUS

使用show status可以查看存储过程和存储函数

show {procedure | function} status [like 'pattern'] \G

这个是MySQL的扩展，返回子程序的特征，例如数据库，名字，类型创建者及创建和修改日期，如果没有指定procedure或function，那么所有过程和函数会被列出，LIKE语句表示匹配存储过程或者存储函数的名字，例如

mysql> show procedure status like "t1"\G
*************************** 1. row ***************************
                  Db: test
                Name: t1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2022-05-17 11:28:48
             Created: 2022-05-17 11:28:48
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

SHOW CREATE

show create {procedure | function} sp_name\G

使用show create可以查看存储过程和存储函数，例如：

mysql> show create procedure t1\G
*************************** 1. row ***************************
           Procedure: t1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `t1`(in x int)
begin
select x+1;
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

information_schema.Routines

MySQL中存储过程和函数的信息存储在information_schema数据库下Routines表中，在information_schema数据库下Routines表中存储了所有过程和函数的定义，使用select查询时，一定要使用Routine_name字段指定过程或函数的名字，否则会查出所有过程和函数，如果有过程和函数同名，则需要同时指定Routine_type字段（PROCEDURE | FUNCTION，注意大写）区分是过程还是函数，例如查询某个名为t1的过程或函数

mysql> 进入information_schema数据库中
mysql> use information_schema;
Database changed
mysql> #查询t1的存储过程或者存储函数
mysql> #如果有一个存储过程和一个存储函数同名，那么都会被查询到
mysql> select * from Routines where Routine_name = "t1"\G
*************************** 1. row ***************************
           SPECIFIC_NAME: t1
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: t1
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 50
  CHARACTER_OCTET_LENGTH: 200
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_0900_ai_ci
          DTD_IDENTIFIER: varchar(50)
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
return "Hello World";
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: READS SQL DATA
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2022-05-18 01:53:49
            LAST_ALTERED: 2022-05-18 01:53:49
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
           SPECIFIC_NAME: t1
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: t1
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
select x+1;
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2022-05-17 11:28:48
            LAST_ALTERED: 2022-05-17 11:28:48
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
2 rows in set (0.01 sec)
mysql> #我们增加一个查询条件，只查询名为t1的存储过程
mysql> #注意Routine_type="PROCEDURE"的PROCEDURE必须全部大写，因为这是一个数据
mysql> select * from Routines where Routine_name = "t1" and Routine_type="PROCEDURE"\G
*************************** 1. row ***************************
           SPECIFIC_NAME: t1
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: t1
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
select x+1;
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2022-05-17 11:28:48
            LAST_ALTERED: 2022-05-17 11:28:48
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

修改过程和函数

目前MySQL还不提供已存在的存储过程代码的修改，如果必须要修改，需要使用DROP删除后重新创建

删除过程或函数

drop {procedure | function} sp_name;

变量的使用

在存储过程和函数中 ，可以定义和使用变量，用户可以使用关键字DECLARE来定义变量，然后为变量赋值，这些变量主要范围是begin...end的程序体中

定义变量

declare var_name [,var_name]... type [default value];

var_name为局部变量名称，type是MySQL中的数据类型，default value给变量提供一个默认值，除了可以用一个常数之外，还可以使用表达式，如果没有default，则为null，例如：

declare myparam int default 100;

上面代码定义了一个int类型的变量，变量名为myparam，默认值为100

变量赋值

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

在存储程序中的set语句是一般set语句的扩展版本，被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量，这句话的意思是，使用set可以为declare定义的变量赋值，也可以为用户变量（即带@符号的变量）赋值

例如，我们定义三个变量var1,var2和var3

declare var1,var2,var3 int;
set var1=10,var2=20;
set var3=var1+var2;

在MySQL中，还可以使用SELECT...INTO...将数据表中数据或某些数据赋值给变量，例如，我们将test表中的id为1的数据中的v1和v2字段的值分别赋值给var_1和var_2

select v1,v2 into var_1 ,var_2 from test where id = 1;

定义条件和处理程序(异常)

特定条件需要特定处理，定义条件是事先定义程序执行过程中遇到的问题，而处理程序定义了遇到这些问题时应当采取的处理方式，并且 保证存储过程或函数在遇到警告或错误时能继续执行 ，避免程序异常停止，使用DECLARE关键字来定义条件和处理程序

类似Java中的try...catch或者python中的try...except...，虽然MySQL中的条件和处理程序和它们完全不同，但是都可以看作对待异常的处理方式

定义条件

declare condition_name condition for [condition_type]

condition_name参数表示条件的名称，condition_type表示条件的类型，有以下两种类型：

1. sqlstate_value：表示MySQL的错误，sqlstate_value为长度5字符串类型的错误代码
2. mysql_error_code：表示MySQL的错误，为数值类型的错误代码

我们以主键冲突的错误代码为例，5字符串类型的错误代码"23000"和数值类型的错误代码1062在MySQL中都表示的是主键冲突

ERROR 1062 (23000): Duplicate entry '1' for key 'test.PRIMARY'

例1—定义条件

定义一个条件，自定义条件名为PRIMARY_Duplicate，表示主键冲突，使用sqlstate_value定义

declare PRIMARY_Duplicate condition for sqlstate '23000';

例2—定义条件

定义一个条件，自定义条件名为PRIMARY_Duplicate，表示主键冲突，使用mysql_error_code定义

declare PRIMARY_Duplicate condition for 1062;

例1与例2的结果完全相同

定义处理程序

使用declare...handler语句指定需要特殊处理的条件，即定义一个处理方式，当遇到一个条件时

declare handler_type handler for condition_value sp_statement;

handler_type为错误处理方式有三种：

1. continue：表示遇到错误不处理；
2. exit：表示遇到错误马上退出；
3. undo：表示遇到错误撤回之前操作

condition_value表示错误类型：

1. sqlstate_value：表示MySQL的错误，sqlstate_value为长度5字符串类型的错误代码；
2. mysql_error_code：表示MySQL的错误，为数值类型的错误代码；
3. condition_name：表示自定义的条件名称；
4. sqlwarning：匹配任何01开头的sqlstate错误代码；
5. not found：匹配任何02开头的sqlstate错误代码；
6. sqlexception：匹配所有没有被sqlwarning或not found捕获的sqlstate错误代码

sp_statement 为程序语句段，表示在遇到定义的错误时需要执行的操作

通常情况下，执行过程遇到错误应该立刻停止执行下面的语句，并且撤回之前的操作，但是MySQL目前不支持 UNDO操作，因此最好执行 EXIT 操作，如果事先能够预测错误，并且进行相应的处理，那么可以执行 CONTINUE 操作

下面我将使用六个例子说明处理程序的定义方式，在平时只要会使用其中一种即可

例1—定义处理程序

定义一个处理程序，当捕获sqlstate_value为"42s02"时，执行continue继续操作，并且输出信息

declare continue handler for sqlstate '42s02' set @info='NO_SUCH_TABLE';

例2—定义处理程序

定义一个处理程序，当捕获mysql_error_code为1146时，执行continue继续操作，并且输出信息

declare continue handler for 1146 set @info="NO_SUCH_TABLE";

例3—定义处理程序

首先定义一个条件no_SUCH_TABLE，该条件表示遇到mysql_error_code为1146的问题，再定义一个处理程序，当捕获条件为no_SUCH_TABLE时，执行continue继续操作，并且输出信息

declare no_SUCH_TABLE condition for 1146;
declare continue handler for no_SUCH_TABLE set @info="NO_SUCH_TABLE";

例4—定义处理程序

定义一个处理程序，当遇到sqlwarning问题时，执行exit马上退出程序，并且输出信息

declare exit handler for sqlwarning set @info="ERROR";

例5—定义处理程序

定义一个处理程序，当遇到not found问题时，执行exit马上退出程序，并且输出信息

declare exit handler for not found set @info="ERROR";

例6—定义处理程序

定义一个处理程序，当遇到sqlexception问题时，执行exit马上退出程序，并且输出信息

declare exit handler for sqlexception set @info="ERROR";

一个实例

最后，我们把处理程序放入存储过程中进行展示，学习和了解程序在过程中发挥的作用

mysql> delimiter //
mysql> #创建一个表test，只有一个主键字段
mysql> create table test(id int primary key)//
Query OK, 0 rows affected (0.02 sec)
mysql> #创建一个过程，在过程中定义一个程序，程序在遇到主键冲突时仍然继续执行
mysql> #定义程序之后，尝试往test表插入两个相同的主键数据
mysql> #使用@info标记过程内部代码块执行的位置
mysql> create procedure Handler_for_ERROR()
    -> begin
    -> declare continue handler for sqlstate '23000' set @info="Step-1";
    -> insert into test values(1);
    -> set @info="Step-2";
    -> insert into test values(1);
    -> set @info="Step-3";
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> #调用过程，可依发现调用成功
mysql> call Handler_for_ERROR()//
Query OK, 0 rows affected (0.01 sec)
mysql> #查看@info变量的值，说明即使发生主键冲突，但是对于过程来说，还是会继续执行
mysql> select @info//
+--------+
| @info  |
+--------+
| Step-3 |
+--------+
1 row in set (0.00 sec)

同学们可以自行进行尝试对比，如果存储过程内没有定义的处理程序，那么发生主键冲突时，会立即报错并退出过程，导致调用过程时失败

光标

查询语句可能返回多条行记录，如果数据量非常大，需要在 存储过程和存储函数 中使用光标来 逐条读取 查询结果集的记录

定义光标

declare cursor_name cursor for select_statement;

其中，cursor_name表示光标名称，select_statement表示普通的select查询语句，例如，我们定义一个名为cur的光标， 光标用于逐行查询test表的行数据

declare cur cursor for select * from test;

打开光标

open cursor_name;

cursor_name表示之前已经定义的光标名称

使用光标

fetch cursor_name into val1 [,val2 ...];

将光标中的信息存入变量val中，这个val值不能使用用户变量（即@开头的变量），变量必须在之前已经被定义。成功赋值之后，光标会自动移动下一行（可以通过一个循环结构实现光标的移动，关于循环结构会在下文流程控制中详细介绍），如果没有下一条数据再使用fetch为变量赋值时会报错

关闭光标

close cursor_name;

cursor_name表示之前已经定义的光标名称，如果没有关闭光标，默认在存储过程/存储函数的END后自动关闭

存储过程/存储函数中各种数据定义的顺序

这里有一个非常重要的点，现在我们知道在一个存储过程/存储函数中，可以定义变量，定义条件和程序，定义光标，但是这些数据在被定义时具有较为严格的出场顺序，假设现在有一个存储过程/存储函数同时需要用到变量、条件和程序、光标，那么，必须按照下列顺序执行：定义变量->定义光标但是不开启光标->定义条件和程序->开启光标，示例：

create procedure test()
begin
declare a int default 0;
declare cur cursor for select ...
declare b condition for ...
declare exit handler for ...
open cur;
... 

流程控制的使用

路程控制语句用来根据条件控制语句的执行，MySQL中有以下七种语句：

1. IF语句
2. CASE语句
3. LOOP语句
4. ITERATE语句
5. LEAVE语句
6. REPEAT语句
7. WHILE语句

IF语句

if语句包含多个条件判断，根据判断结果为True或False执行相应的语句

if expr_condition then statement_list
	[elseif expr_condition then statement_list] ...
	[else statement_list]
end if

IF实现了一个基本条件构造，如果expr_condition求值为真，相应的SQL语句列表被执行，否则判断elseif的条件或执行else的SQL语句

例1—IF语句

这是一个简单的条件判断语句，如果2>1是True输出"2>1"，如果1<2是True输出"2<1"，如果if和elseif的条件都为False则输出NULL

mysql> create procedure test()
    -> begin
    -> if 2>1 then select "2>1";
    -> elseif 1<2 then select "1<2";
    -> else select "NULL";
    -> end if;
    -> end//
Query OK, 0 rows affected (0.01 sec)
mysql> call test()//
+-----+
| 2>1 |
+-----+
| 2>1 |
+-----+
1 row in set (0.00 sec)

IF条件判断语句

额外介绍一下IF判断函数，这是MySQL内置函数，它的格式为

IF(expr,v1,v2)

如果表达式expr是TRUE则返回v1，否则返回v2，例如：

mysql> select if(2>1,"True","False")//
+------------------------+
| if(2>1,"True","False") |
+------------------------+
| True                   |
+------------------------+
1 row in set (0.00 sec)

还有一个IFNULL判断是否为空函数

IFNULL(v1,v2)

如果v1不为NULL返回v1，否则返回v2，例如：

mysql> select ifnull(null,2),ifnull(1,null)//
+----------------+----------------+
| ifnull(null,2) | ifnull(1,null) |
+----------------+----------------+
|              2 |              1 |
+----------------+----------------+
1 row in set (0.00 sec)

CASE语句

CASE是另一个进行条件判断的语句，有两种格式

CASE的第一种格式

case case_expr
	when when_value then statement_list
	[when when_value then statement_list] ...
	[else statement_list]
end case

case_expr表示条件判断的表达式，决定了哪一个WHEN被执行，when_value表示表达式可能的值，如果某一个when_value与表达式值相同，则执行相应的THEN关键字后面的statement_list，如果所有when_value均不匹配，执行else之后的SQL语句

例1—CASE语句

这是一个简单的条件判断语句，给出条件判断的表达式1，只执行某一个when_value=1的SQL语句，如果WHEN之后的when_value都没有与之条件匹配的1，那么执行ELSE之后的语句

mysql> create procedure test()
    -> begin
    -> case 1
    -> when 1 then select "1";
    -> when 2 then select "2";
    -> else select "Other";
    -> end case;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> call test()//
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

CASE的第二种格式

case 
	when expr_condition then statement_list
	[when expr_condition then statement_list] ...
	[else statement_list]
end case

相较于第一种格式，这种方式没有了case_expr条件判断的表达式，哪一个WHEN被执行，是由WHEN之后的expr_condition决定的，如果expr_condition表达式为True，则执行对应的SQL，如果为False则不执行，如果所有WHEN都不执行，则执行else之后的SQL语句

例2—CASE语句

mysql> create procedure test()
    -> begin
    -> case
    -> when 2>1 then select "2>1";
    -> when 2<1 then select "2<1";
    -> else select NULL;
    -> end case;
    -> end//
Query OK, 0 rows affected (0.01 sec)
mysql> call test()//
+-----+
| 2>1 |
+-----+
| 2>1 |
+-----+
1 row in set (0.00 sec)

CASE条件判断语句

在MySQL内置函数中，也有CASE的函数，并且也有两种格式，第一种格式：

CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ... ELSE r END

如果expr的值等于某个WHEN后面的值，则返回对应的THEN，如果每个WHEN都不匹配，则返回ELSE后面的值

第二种格式：

CASE WHEN v1 THEN r1 WHEN v2 THEN r2.... ELSE r END

当某个vn的值为TRUE时返回THEN后面的rn，如果均不为TRUE，则返回ELSE后面的值

LOOP语句

LOOP循环用来重复执行某些语句，LOOP创建了一个循环操作的过程，不会进行条件的判断，LOOP内的语句一直重复执行直到退出（使用LEAVE子句），跳出循环过程，LOOP的基本格式：

loop_label:loop
	statement_list
end loop loop_name

其中loop_name表示循环的名称，定义循环的名称之后同时相当于定义了一个label标签，statement_list表示需要循环的sql语句

例1—LOOP语句

由于LOOP没有离开循环的条件，所以需要使用LEAVE语句跳出循环

mysql> #创建一个循环，用户输入一个整型变量x，计算从1加到x的和
mysql> create procedure test(in x int)
    -> begin
    -> declare i int default 1;
    -> set @sum=0;
    -> t_loop:loop
    -> if i<=x then set @sum=@sum+i;
    -> set i=i+1;
    -> else leave t_loop;
    -> end if;
    -> end loop t_loop;
    -> end//
Query OK, 0 rows affected (0.01 sec)
mysql> call test(100)//
Query OK, 0 rows affected (0.00 sec)
mysql> select @sum//
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

LEAVE语句

LEAVE语句用来退出任何被标签的流程控制构造，格式：

leave label_name

label_name是流程控制语句的名称（即标签），LEAVE语句可以出现在LOOP、REPEAT、WHILE语句内，因为循环结构可以嵌套，因此每一个流程控制结构都需要一个标签，这样就可以使用LEAVE关键字指定退出哪一个循环体

ITERATE语句

ITERATE语句将执行顺序转到流程控制内开头处，格式：

iterate label_name

label_name是流程控制语句的名称（即标签），ITERATE的意思是再次循环，可以出现在LOOP、REPEAT、WHILE语句内，ITERATE语句必须跟在循环标签前面

REPEAT语句

REPEAT语句创建一个带条件的循环结构，每次循环执行完之后会对表达式进行判断，如果判断为真，则循环结束，否则重复执行循环的语句

repeat_label:repeat
	statement_list
until expr_condition
end repeat repeat_label

其中repeat_label表示循环的名称（即标签），statement_list表示需要循环的sql语句，expr_condition表示执行一次循环之后进行判断，如果判断为真，则循环结束，否则重复执行循环的语句

例1—REPEAT语句

mysql> #创建一个循环，用户输入一个整型变量x，计算从1加到x的和
mysql> create procedure test(in x int)
    -> begin
    -> declare i int default 0;
    -> set @sum=0;
    -> t_repeat:repeat
    -> set @sum=@sum+i;
    -> set i=i+1;
    -> until i>x
    -> end repeat t_repeat;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> call test(100)//
Query OK, 0 rows affected (0.00 sec)
mysql> select @sum//
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

注意until的语句后面不需要分号(;)

WHILE语句

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE是在执行语句执行之前判断，条件为真则执行循环的语句，否则退出循环

while_label:while expr_condition DO
statement_list
end while while_label;

其中while_label表示循环的名称（即标签），statement_list表示需要循环的sql语句，expr_condition表示执行一次循环之前进行的判断，如果判断为真，则进入循环，否则跳出循环

例1—WHILE语句

mysql> #创建一个循环，用户输入一个整型变量x，计算从1加到x的和
mysql> create procedure test(in x int)
    -> begin
    -> declare i int default 0;
    -> set @sum=0;
    -> t_while:while i<=x do
    -> set @sum=@sum+i;
    -> set i=i+1;
    -> end while t_while;
    -> end//
Query OK, 0 rows affected (0.01 sec)
mysql> call test(100)//
Query OK, 0 rows affected (0.00 sec)
mysql> select @sum//