游标是一个存储在 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/