MySQL数据库游标（Cursor）的定义及使用和MySQL流程控制语句详解_mysql定义游标

MySQL游标（Cursor）的定义及使用

在 MySQL 中，存储过程或函数中的查询有时会返回多条记录，而使用简单的 SELECT 语句，没有办法得到第一行、下一行或前十行的数据，这时可以使用游标来逐条读取查询结果集中的记录。游标在部分资料中也被称为光标。

关系数据库管理系统实质是面向集合的，在 MySQL 中并没有一种描述表中单一记录的表达形式，除非使用 WHERE 子句来限制只有一条记录被选中。所以有时我们必须借助于游标来进行单条记录的数据处理。

一般通过游标定位到结果集的某一行进行数据修改。

结果集是符合 SQL 语句的所有记录的集合。

个人理解游标就是一个标识，用来标识数据取到了什么地方，如果你了解编程语言，可以把他理解成数组中的下标。

不像多数 DBMS，MySQL 游标只能用于存储过程和函数。

下面介绍游标的使用，主要包括游标的声明、打开、使用和关闭。

1. 声明游标

MySQL 中使用 DECLARE 关键字来声明游标，并定义相应的 SELECT 语句，根据需要添加 WHERE 和其它子句。其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

其中，cursor_name 表示游标的名称；select_statement 表示 SELECT 语句，可以返回一行或多行数据。

例 1

下面声明一个名为 nameCursor 的游标，代码如下：

mysql> DELIMITER //
mysql> CREATE PROCEDURE processnames()
    -> BEGIN
    -> DECLARE nameCursor CURSOR
    -> FOR
    -> SELECT name FROM tb_student;
    -> END//
Query OK, 0 rows affected (0.07 sec)

以上语句定义了 nameCursor 游标，游标只局限于存储过程中，存储过程处理完成后，游标就消失了。

2. 打开游标

声明游标之后，要想从游标中提取数据，必须首先打开游标。在 MySQL 中，打开游标通过 OPEN 关键字来实现，其语法格式如下：

OPEN cursor_name;

其中，cursor_name 表示所要打开游标的名称。需要注意的是，打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边。

在程序中，一个游标可以打开多次。用户打开游标后，其他用户或程序可能正在更新数据表，所以有时会导致用户每次打开游标后，显示的结果都不同。

3. 使用游标

游标顺利打开后，可以使用 FETCH...INTO 语句来读取数据，其语法形式如下：

FETCH cursor_name INTO var_name [,var_name]...

上述语句中，将游标 cursor_name 中 SELECT 语句的执行结果保存到变量参数 var_name 中。变量参数 var_name 必须在游标使用之前定义。使用游标类似高级语言中的数组遍历，当第一次使用游标时，此时游标指向结果集的第一条记录。

MySQL 的游标是只读的，也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。

4. 关闭游标

游标使用完毕后，要及时关闭，在 MySQL 中，使用 CLOSE 关键字关闭游标，其语法格式如下：

CLOSE cursor_name;

CLOSE 释放游标使用的所有内部内存和资源，因此每个游标不再需要时都应该关闭。

在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用 OPEN 语句打开它就可以了。

如果你不明确关闭游标，MySQL 将会在到达 END 语句时自动关闭它。游标关闭之后，不能使用 FETCH 来使用该游标。

大厂资深数据库工程师mysql数据库实战训练https://edu.csdn.net/course/detail/39021

例 2

创建 users 数据表，并插入数据，SQL 语句和运行结果如下：

mysql> CREATE TABLE `users`
    ->  (
    ->  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  `user_name` VARCHAR(60),
    ->  `user_pass` VARCHAR(64),
    ->  PRIMARY KEY (`ID`)
    -> );
Query OK, 0 rows affected (0.06 sec)
 
mysql> INSERT INTO users VALUES(null,'sheng','sheng123'),
    -> (null,'yu','yu123'),
    -> (null,'ling','ling123');
Query OK, 3 rows affected (0.01 sec)

创建存储过程 test_cursor，并创建游标 cur_test，查询 users 数据表中的第 3 条记录，SQL 语句和执行过程如下：

mysql> DELIMITER //
mysql> CREATE PROCEDURE test_cursor (in param INT(10),out result VARCHAR(90))
    -> BEGIN
    -> DECLARE name VARCHAR(20);
    -> DECLARE pass VARCHAR(20);
    -> DECLARE done INT;
    -> DECLARE cur_test CURSOR FOR SELECT user_name,user_pass FROM users;
    -> DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;
    -> IF param THEN INTO result FROM users WHERE id = param;
    -> ELSE
    -> OPEN cur_test;
    -> repeat
    -> FETCH cur_test into name,pass;
    -> SELECT concat_ws(',',result,name,pass) INTO result;
    -> until done
    -> END repeat;
    -> CLOSE cur_test;
    -> END IF;
    -> END //
Query OK, 0 rows affected (0.10 sec)
 
mysql> call test_cursor(3,@test)//
Query OK, 1 row affected (0.03 sec)
 
mysql> select @test//
+-----------+
| @test     |
+-----------+
| ling,ling123 |
+-----------+
1 row in set (0.00 sec)

创建 pro_users() 存储过程，定义 cur_1 游标，将表 users 中的 user_name 字段全部修改为 MySQL，SQL 语句和执行过程如下。

mysql> CREATE PROCEDURE pro_users()
    -> BEGIN
    -> DECLARE result VARCHAR(100);
    -> DECLARE no INT;
    -> DECLARE cur_1 CURSOR FOR SELECT user_name FROM users;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET no=1;
    -> SET no=0;
    -> OPEN cur_1;
    -> WHILE no=0 do
    -> FETCH cur_1 into result;
    -> UPDATE users SET user_name='MySQL'
    -> WHERE user_name=result;
    -> END WHILE;
    -> CLOSE cur_1;
    -> END //
Query OK, 0 rows affected (0.05 sec)
 
mysql> call pro_users() //
Query OK, 0 rows affected (0.03 sec)
 
mysql> SELECT * FROM users //
+----+-----------+-----------+
| ID | user_name | user_pass |
+----+-----------+-----------+
|  1 | MySQL     | sheng      |
|  2 | MySQL     | zhang     |
|  3 | MySQL     | ying      |
+----+-----------+-----------+
3 rows in set (0.00 sec)

结果显示，users 表中的 user_name 字段已经全部修改为 MySQL。

MySQL流程控制语句详解

在存储过程和自定义函数中可以使用流程控制语句来控制程序的流程。MySQL 中流程控制语句有：IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句等。

下面将详细讲解这些流程控制语句。

1. IF语句

IF 语句用来进行条件判断，根据是否满足条件（可包含多个条件），来执行不同的语句，是流程控制中最常用的判断语句。其语法的基本形式如下：

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list]...
    [ELSE statement_list]
END IF

其中，search_condition 参数表示条件判断语句，如果返回值为 TRUE ，相应的 SQL 语句列表（statement_list）被执行；如果返回值为 FALSE，则 ELSE 子句的语句列表被执行。statement_list 可以包括一个或多个语句。

注意：MySQL 中的 IF( ) 函数不同于这里的 IF 语句。

例 1

下面是一个使用 IF 语句的示例。代码如下：

IF age>20 THEN SET @count1=@count1+1;
    ELSEIF age=20 THEN @count2=@count2+1;
    ELSE @count3=@count3+1;
END lF;

该示例根据 age 与 20 的大小关系来执行不同的 SET 语句。如果 age 值大于20，那么将 count1 的值加 1；如果 age 值等于 20，那么将 count2 的值加 1；其他情况将 count3 的值加 1。IF 语句都需要使用 END IF 来结束。

2. CASE语句

CASE 语句也是用来进行条件判断的，它提供了多个条件进行选择，可以实现比 IF 语句更复杂的条件判断。CASE 语句的基本形式如下：

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE

其中：

• case_value 参数表示条件判断的变量，决定了哪一个 WHEN 子句会被执行；
• when_value 参数表示变量的取值，如果某个 when_value 表达式与 case_value 变量的值相同，则执行对应的 THEN 关键字后的 statement_list 中的语句；
• statement_list 参数表示 when_value 值没有与 case_value 相同值时的执行语句。
• CASE 语句都要使用 END CASE 结束。

CASE 语句还有另一种形式。该形式的语法如下：

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

其中，search_condition 参数表示条件判断语句；statement_list 参数表示不同条件的执行语句。

与上述语句不同的是，该语句中的 WHEN 语句将被逐个执行，直到某个 search_condition 表达式为真，则执行对应 THEN 关键字后面的 statement_list 语句。如果没有条件匹配，ELSE 子句里的语句被执行。

这里介绍的 CASE 语句与“控制流程函数”里描述的 SQL CASE 表达式的 CASE 语句有轻微的不同。这里的 CASE 语句不能有 ELSE NULL 语句，并且用 END CASE 替代 END 来终止。

例 2

下面是一个使用 CASE 语句的示例。代码如下：

CASE age
    WHEN 20 THEN SET @count1=@count1+1;
    ELSE SET @count2=@count2+1;
END CASE;

代码也可以是下面的形式：

CASE
    WHEN age=20 THEN SET @count1=@count1+1;
    ELSE SET @count2=@count2+1;
END CASE;

本示例中，如果 age 值为 20，count1 的值加 1，否则 count2 的值加 1。

3. LOOP 语句

LOOP 语句可以使某些特定的语句重复执行。与 IF 和 CASE 语句相比，LOOP 只实现了一个简单的循环，并不进行条件判断。

LOOP 语句本身没有停止循环的语句，必须使用 LEAVE 语句等才能停止循环，跳出循环过程。LOOP 语句的基本形式如下：

[begin_label:]LOOP
    statement_list
END LOOP [end_label]

其中，begin_label 参数和 end_label 参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；statement_list 参数表示需要循环执行的语句。

大厂资深数据库工程师mysql数据库实战训练https://edu.csdn.net/course/detail/39021 

例 3

使用 LOOP 语句进行循环操作。代码如下：

add_num:LOOP
    SET @count=@count+1;
END LOOP add_num;

该示例循环执行 count 加 1 的操作。因为没有跳出循环的语句，这个循环成了一个死循环。LOOP 循环都以 END LOOP 结束。

4. LEAVE 语句

LEAVE 语句主要用于跳出循环控制。其语法形式如下：

LEAVE label

其中，label 参数表示循环的标志，LEAVE 语句必须跟在循环标志前面。

例 4

下面是一个 LEAVE 语句的示例。代码如下：

add_num:LOOP
    SET @count=@count+1;
    IF @count=100 THEN
        LEAVE add_num;
END LOOP add num;

该示例循环执行 count 加 1 的操作。当 count 的值等于 100 时，跳出循环。

5. ITERATE 语句

ITERATE 是“再次循环”的意思，用来跳出本次循环，直接进入下一次循环。ITERATE 语句的基本语法形式如下：

ITERATE label

其中，label 参数表示循环的标志，ITERATE 语句必须跟在循环标志前面。

例 5

下面是一个 ITERATE 语句的示例。代码如下：

add_num:LOOP
    SET @count=@count+1;
    IF @count=100 THEN
        LEAVE add_num;
    ELSE IF MOD(@count,3)=0 THEN
        ITERATE add_num;
    SELECT * FROM employee;
END LOOP add_num;

该示例循环执行 count 加 1 的操作，count 值为 100 时结束循环。如果 count 的值能够整除 3，则跳出本次循环，不再执行下面的 SELECT 语句。

说明：LEAVE 语句和 ITERATE 语句都用来跳出循环语句，但两者的功能是不一样的。LEAVE 语句是跳出整个循环，然后执行循环后面的程序。而 ITERATE 语句是跳出本次循环，然后进入下一次循环。使用这两个语句时一定要区分清楚。

6. REPEAT 语句

REPEAT 语句是有条件控制的循环语句，每次语句执行完毕后，会对条件表达式进行判断，如果表达式返回值为 TRUE，则循环结束，否则重复执行循环中的语句。

REPEAT 语句的基本语法形式如下：

[begin_label:] REPEAT
    statement_list
    UNTIL search_condition
END REPEAT [end_label]

其中：

• begin_label 为 REPEAT 语句的标注名称，该参数可以省略；
• REPEAT 语句内的语句被重复，直至 search_condition 返回值为 TRUE。
• statement_list 参数表示循环的执行语句；
• search_condition 参数表示结束循环的条件，满足该条件时循环结束。
• REPEAT 循环都用 END REPEAT 结束。

例 6

下面是一个使用 REPEAT 语句的示例。代码如下：

REPEAT
    SET @count=@count+1;
    UNTIL @count=100
END REPEAT;

该示例循环执行 count 加 1 的操作，count 值为 100 时结束循环。

7. WHILE 语句

WHILE 语句也是有条件控制的循环语句。WHILE 语句和 REPEAT 语句不同的是，WHILE 语句是当满足条件时，执行循环内的语句，否则退出循环。WHILE 语句的基本语法形式如下：

[begin_label:] WHILE search_condition DO
    statement list
END WHILE [end label]

其中，search_condition 参数表示循环执行的条件，满足该条件时循环执行；statement_list 参数表示循环的执行语句。WHILE 循环需要使用 END WHILE 来结束。

例 7

下面是一个使用 WHILE 语句的示例。代码如下：

WHILE @count<100 DO
    SET @count=@count+1;
END WHILE;

该示例循环执行 count 加 1 的操作，count 值小于 100 时执行循环。如果 count 值等于 100 了，则跳出循环。

大厂资深数据库工程师mysql数据库实战训练https://edu.csdn.net/course/detail/39021