MySQL教程112-MySQL游标的定义及使用

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

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

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

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

其实游标可以看做是一个标识，用来标识数据取到了什么地方，就像是编程语言中数组中的下标。

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_info;
    -> END//
Query OK, 0 rows affected (0.07 sec)

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

2. 打开游标

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

OPEN cursor_name;

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

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

3. 使用游标

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

FETCH [[NEXT] FROM] 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 来使用该游标。

例 2

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

mysql> create table if not exists user
    -> (
    -> id int unsigned primary key auto_increment,
    -> username varchar(25),
    -> userpass varchar(25)
    -> );
Query OK, 0 rows affected (0.50 sec)

mysql> INSERT INTO users VALUES(null,'sheng','sheng123'),(null,'yu','yu123'),(null,'ling','ling123');
Query OK, 3 rows affected (0.12 sec)

创建存储过程 test_cursor，并创建游标 cur_test，根据入参uid查询users返回, 入参没值默认查询 users 数据表中的第 2 条记录，SQL 语句和执行过程如下：

注意: 这里存储过程中涉及到的控制语句, 在下一章节中有讲解, 这里看不懂没事, 可以看完下一节再回来细品一下!

mysql> . E:javamysqlmy.sql

这里我是将存储过程写在了本地的一个文件my.sql中, 使用命令.或source就可以直接调用执行此文件, 即执行文件中的sql, my.sql文件内容如下

delimiter //
create procedure test_cursor(in uid int(4),  out result varchar(30))
begin
    declare name, pass varchar(10);
    declare done int(2);
    declare count int(2) default 0;　　
    DECLARE cur_test CURSOR FOR select username, userpass from users;
    declare continue handler for sqlstate '02000' set done=1;
    if uid then select concat_ws(',', result, username, userpass) into result from users where id = uid;　　// 如果uid有值直接查询
    else 　　// 如果uid没值
        open cur_test;　　// 打开游标
            jkd:loop　　// 循环
                fetch cur_test into name, pass;
                set count = count + 1;　　// 
                if count = 2 then　　// 如果count等于2, 就是第二条数据            
                    select concat_ws(',', name, pass) into result;
                    leave jkd;  // 跳出循环
                end if;
            end loop jkd;            
        close cur_test;
    end if;
end//
delimiter ;

上面的sql中关于"DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;"的使用, 看这里 -> 说明

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

 source E:javamysqlmy.sql

my.sql内容如下:

delimiter //
create procedure update_users(in newname varchar(20))
begin
    declare done int(2);
    declare uid int(4);
    declare cursor_update cursor for select id from users;
    declare continue handler for sqlstate '02000' set done=1;
    open cursor_update;
        jkd:loop
            fetch cursor_update into uid;
            update users set username=newname where id=uid;
            if done=1 then
                leave jkd;
            end if;
        end loop jkd;
    close cursor_update;
end//
delimiter ;

调用查看:

mysql> call update_users('test');
Query OK, 0 rows affected (0.26 sec)

mysql> select * from users;
+----+----------+----------+
| id | username | userpass |
+----+----------+----------+
|  1 | test     | sheng123 |
|  2 | test     | yu123    |
|  3 | test     | ling123  |
+----+----------+----------+
3 rows in set (0.00 sec)

mysql> call update_users('test666');
Query OK, 0 rows affected (0.25 sec)

mysql> select * from users;
+----+----------+----------+
| id | username | userpass |
+----+----------+----------+
|  1 | test666  | sheng123 |
|  2 | test666  | yu123    |
|  3 | test666  | ling123  |
+----+----------+----------+
3 rows in set (0.00 sec)

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