文章详情页_MySQL 游标的定义与使用方式

游标是一个存储在 MySQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

注：在 MySQL 中，游标只能用于存储过程和函数。

使用游标

使用游标涉及几个明确的步骤：

在能够使用游标前，必须声明（定义）游标。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句
一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来
对于填有数据的游标，根据需要取出（检索）各行
在结束游标使用时，必须关闭游标

在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行获取操作。

声明游标

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

DECLARE cursor_name CURSOR FOR select_statement;

cursor_name：表示游标的名称
select_statement：表示 SELECT 子句，可以返回一行或多行数据

例 1：声明一个名为 cur 的游标，代码如下：

mysql> use yiibaidb;
Database changed

mysql> create procedure p_cursor()
begin
  declare cur cursor for select firstname, lastname from employees;
end;

以上语句定义了 cur 游标，游标只局限于存储过程或函数中，存储过程或函数处理完成后，游标也会随之消失。

打开游标

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

OPEN cursor_name;

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

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

使用游标

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

FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

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

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

关闭游标

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

CLOSE cursor_name;

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

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

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

游标循环

创建测试数据：

mysql> create table student(
  stuid   int primary key auto_increment,
  stuname varchar(20),
  stusex  varchar(2),
  stuage  int
);

mysql> insert into student(stuname, stusex, stuage) values
  ('小明', '男', 20),
  ('小花', '女', 19),
  ('大赤', '男', 20),
  ('可乐', '男', 19),
  ('莹莹', '女', 19);

LOOP 循环

创建存储过程，游标使用 LOOP 循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_CUR_LOOP`()
BEGIN
  -- 声明用于接收游标值的变量
  DECLARE V_ID  , V_AGE, V_TOTAL INT;
  DECLARE V_NAME, V_SEX          VARCHAR(20);

  -- 声明游标结束的标志 
  DECLARE DONE INT DEFAULT FALSE;

  -- 声明游标
  DECLARE CUR CURSOR FOR SELECT STUID, STUNAME, STUSEX, STUAGE FROM STUDENT WHERE STUAGE > 19; 

  -- 声明游标循环结束时的返回值
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;

  -- 初始化变量
  SET V_TOTAL = 0; 

  -- 打开游标
  OPEN CUR;

  -- LOOP 循环
  READ_LOOP:LOOP 

    -- 获取游标当前指向的一行数据 
    FETCH CUR INTO V_ID, V_NAME, V_SEX, V_AGE;

    -- 判断游标的循环是否结束 
    IF DONE THEN 

      -- 跳出游标循环  
      LEAVE READ_LOOP;

    END IF;

    -- 累计值
    SET V_TOTAL = V_TOTAL + 1;

  END LOOP;

  -- 关闭游标
  CLOSE CUR;

  -- 输出结果
  SELECT V_TOTAL;

END;

调用存储过程

mysql> call p_cur_loop();
+---------+
| V_TOTAL |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

WHILE 循环

创建存储过程，游标使用 WHILE 循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_CUR_WHILE`()
BEGIN
  -- 声明用于接收游标值的变量
    DECLARE V_ID  , V_AGE, V_TOTAL INT;
    DECLARE V_NAME, V_SEX                VARCHAR(20);

    -- 声明游标结束的标志 
    DECLARE DONE INT DEFAULT FALSE;

    -- 声明游标
    DECLARE CUR CURSOR FOR SELECT STUID, STUNAME, STUSEX, STUAGE FROM STUDENT WHERE STUAGE > 19; 

    -- 声明游标循环结束时的返回值
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;

  -- 初始化变量
  SET V_TOTAL = 0;  

  -- 打开游标
  OPEN CUR;

  -- WHILE 循环
  WHILE DONE <> TRUE DO 

    -- 获取游标当前指向的一行数据    
      FETCH CUR INTO V_ID, V_NAME, V_SEX, V_AGE;

      -- 判断游标的循环是否结束 
      IF DONE <> TRUE THEN

            -- 累计值
            SET V_TOTAL = V_TOTAL + 1;

    END IF;

  END WHILE;

  -- 关闭游标
  CLOSE CUR;

  -- 输出结果
  SELECT V_TOTAL;

END;

调用存储过程

mysql> call p_cur_while();
+---------+
| V_TOTAL |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

REPEAT 循环

创建存储过程，游标使用 REPEAT 循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_CUR_REPEAT`()
BEGIN
  -- 声明用于接收游标值的变量
    DECLARE V_ID  , V_AGE, V_TOTAL INT;
    DECLARE V_NAME, V_SEX                VARCHAR(20);

    -- 声明游标结束的标志 
    DECLARE DONE INT DEFAULT FALSE;

    -- 声明游标
    DECLARE CUR CURSOR FOR SELECT STUID, STUNAME, STUSEX, STUAGE FROM STUDENT WHERE STUAGE > 19; 

    -- 声明游标循环结束时的返回值
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;

  -- 初始化变量
  SET V_TOTAL = 0;  

  -- 打开游标
  OPEN CUR;

  -- WHILE 循环
  REPEAT

    -- 获取游标当前指向的一行数据    
      FETCH CUR INTO V_ID, V_NAME, V_SEX, V_AGE;

      -- 判断游标的循环是否结束 
      IF DONE <> TRUE THEN

            -- 累计值
            SET V_TOTAL = V_TOTAL + 1;

    END IF;

        -- 注意此处不能有分号
        UNTIL DONE = TRUE

  END REPEAT;

  -- 关闭游标
  CLOSE CUR;

  -- 输出结果
  SELECT V_TOTAL;

END;

调用存储过程

mysql> call p_cur_repeat();
+---------+
| V_TOTAL |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

参考资料

https://www.cnblogs.com/oukele/p/10684639.html

原创文章，转载请注明出处：http://www.opcoder.cn/article/55/