MySQL 游标的作用与使用相关

最新推荐文章于 2022-09-03 20:06:16 发布

sxudong2010

最新推荐文章于 2022-09-03 20:06:16 发布

阅读量586

点赞数 1

分类专栏： mysql

原文链接：http://www.cppcns.com/shujuku/mysql/374794.html

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

转载自：http://www.cppcns.com/shujuku/mysql/374794.html

文章目录

定义

我们经常会遇到这样的一种情况，需要对我们查询的结果进行遍历操作，并对遍历到的每一条数据进行处理，这时候就会使用到游标。

所以：游标（Cursor）是处理数据的一种存储在mysql服务器上的数据库查询方法，为了查看或者处理结果集中的数据，提供了在结果集中一次一行遍历数据的能力。

游标主要用在循环处理、存储过程、函数、触发器中。

游标的作用

比如我们上面那个students学生，需要对每个用户进行遍历，然后根据他们的其他评价进行加分或者减分。这时候我们就需要查询到所有的学生信息（包含成绩）。

select studentid,studentname,score from students;

执行之后返回了的学生数据集合，我们如果需要对学生数据逐一遍历，然后根据具体的情况进行加分，那就需要是使用游标了。
游标相当于一个指针，这个指针指向select的第一行数据，可以通过移动指针来遍历后面的数据。

游标的使用

声明游标：创建一个游标，并指定这个游标需要遍历的select查询，声明游标时并不会去执行这个sql。
打开游标：打开游标的时候，会执行游标对应的select语句。
遍历数据：使用游标循环遍历select结果中每一行数据，然后进行处理。
业务操作：对遍历到的每行数据进行操作的过程，可以放置任何需要执行的执行的语句(增删改查):这里视具体情况而定
关闭游标：游标使用完之后一定要释放。

注: 使用的临时字段需要在定义游标之前进行声明。

声明游标

声明一个游标。也可以在子程序中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。声明游标后也是单条操作的，但是SELECT语句不能有INTO子句。
一个begin end中只能声明一个游标。

DECLARE cursor_name CURSOR FOR select_statement;

打开游标

打开先前声明的游标。

OPEN cursor_name;

遍历游标数据

这个语句用指定的打开游标读取下一行（如果有下一行的话），并且前进游标指针。取出当前行的结果，将结果放在对应的变量中，并将游标指针指向下一行的数据。
当调用fetch的时候，会获取当前行的数据，如果当前行无数据，会引发mysql内部的NOT FOUND错误。

FETCH cursor_name INTO var_list;

关闭游标

切记游标使用完毕之后要关闭。

CLOSE cursor_name;

游标举例

写一个函数，里面包含对students 学生用户成绩的计算和附加分计算
数据

-- ----------------------------
-- Table structure for `students`
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `studentid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `studentname` varchar(20) DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  `classid` int(11) DEFAULT NULL,
  PRIMARY KEY (`studentid`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('1', 'brand', '97.50', '1');
INSERT INTO `students` VALUES ('2', 'helen', '96.50', '1');
INSERT INTO `students` VALUES ('3', 'lyn', '96.00', '1');
INSERT INTO `students` VALUES ('4', 'sol', '97.00', '1');
INSERT INTO `students` VALUES ('5', 'b1', '81.00', '2');
INSERT INTO `students` VALUES ('6', 'b2', '82.00', '2');
INSERT INTO `students` VALUES ('7', 'c1', '71.00', '3');
INSERT INTO `students` VALUES ('8', 'c2', '72.50', '3');
INSERT INTO `students` VALUES ('9', 'lala', '73.00', '0');
INSERT INTO `students` VALUES ('10', 'A', '99.00', '3');
INSERT INTO `students` VALUES ('16', 'test1', '100.00', '0');
INSERT INTO `students` VALUES ('17', 'trigger2', '100.00', '0');
INSERT INTO `students` VALUES ('22', 'trigger1', '107.00', '0');

编写包含游标的函数

-- 判断函数如果存在则删除
DROP FUNCTION IF EXISTS fun_test;

-- 声明结束符为$ 
DELIMITER $
-- 创建函数，对符合条件的每个同学的分数进行加分，加的分数不能超过给定的值 max_score 
CREATE FUNCTION fun_test(max_score decimal(10,2))
RETURNS int

BEGIN
    -- 声明本地变量
    -- 定义实时 StudentId 的变量
    DECLARE var_studentId int DEFAULT 0;
    -- 定义计算后分数的变量
    DECLARE var_score decimal(10,2) DEFAULT 0;
    -- 定义游标结束标志变量
    DECLARE var_done int DEFAULT FALSE;
    
    -- 创建游标 
    DECLARE cur_test CURSOR 
        FOR SELECT studentid,score from students where classid<>0;
        
    -- 游标结束时会设置 var_done 为true，后续可以使用 var_done 来判断游标是否结束
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = TRUE;
    
    -- 打开游标
    OPEN cur_test;
    -- 使用 Loop 循环遍历游标
    select_loop:LOOP
        -- 先获取当前行的数据，然后将当前行的数据放入 var_studentId, var_score 中，如果无数据行了，var_done会被置为true
        FETCH cur_test INTO var_studentId, var_score;
        -- 通过var_done来判断游标是否结束了，退出循环
        IF var_done THEN
            LEAVE select_loop;
        END IF;
        -- 对 var_score 值添加随机值，不能超过给定的分数
        set var_score = var_score + LEAST(ROUND(rand()*10,0), max_score);
        update students set score = var_score where studentId = var_studentId;
    END LOOP;
    -- 关闭游标
    CLOSE cur_test;
    -- 返回结果:可以根据实际情况返回需要的内容
    RETURN 1;
END $
-- 结束符置为 ; 
DELIMITER ;

调用函数
参数为8，表示加分上限为8

mysql>
select fun_test(8);
+-------------+
| fun_test(8) |
+-------------+
|    1        |
+-------------+
1 row in set

查看结果
对比原来的成绩的值，发现成绩添加了随机值，但没超过给定的分数 8

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1         | brand       | 105.5 | 1       |
| 2         | helen       | 98.5  | 1       |
| 3         | lyn         | 97    | 1       |
| 4         | sol         | 97    | 1       |
| 5         | b1          | 89    | 2       |
| 6         | b2          | 90    | 2       |
| 7         | c1          | 76    | 3       |
| 8         | c2          | 73.5  | 3       |
| 9         | lala        | 73    | 0       |
| 10        | A           | 100   | 3       |
| 16        | test1       | 100   | 0       |
| 17        | trigger2    | 107   | 0       |
| 22        | trigger1    | 100   | 0       |
+-----------+-------------+-------+---------+
13 rows in set

查看触发器日志

符合条件被修改分数的有9条数据，都已经被触发器记录到日志里面了

mysql>
/*上一篇编写了触发器，当修改students表的时候触发日志记录 */
select * from triggerlog;
+----+--------------+---------------+-----------------------------------------+
| id | trigger_time | trigger_event |             memo                        |
+----+--------------+---------------+-----------------------------------------+
| 1  | after        | insert        | new student info,id:21                  |
| 2  | after        | update        | update student info,id:21               |
| 3  | after        | update        | delete student info,id:21               |
| 4  | after        | update        | from:test2,101.00 to:trigger2,106.00    |
| 5  | after        | update        | from:trigger2,106.00 to:trigger2,107.00 |
| 6  | after        | update        | delete student info,id:11               |
| 7  | after        | update        | from:brand,97.50 to:brand,105.50        |
| 8  | after        | update        | from:helen,96.50 to:helen,98.50         |
| 9  | after        | update        | from:lyn,96.00 to:lyn,97.00             |
| 10 | after        | update        | from:sol,97.00 to:sol,97.00             |
| 11 | after        | update        | from:b1,81.00 to:b1,89.00               |
| 12 | after        | update        | from:b2,82.00 to:b2,90.00               |
| 13 | after        | update        | from:c1,71.00 to:c1,76.00               |
| 14 | after        | update        | from:c2,72.50 to:c2,73.50               |
| 15 | after        | update        | from:A,99.00 to:A,100.00                |
+----+--------------+---------------+-----------------------------------------+
15 rows in set

游标的执行过程
按照上面的例子，分析下这个游标的执行过程。

我们创建了一个游标，数据源取自于student学生表。
游标中有个指针，当打开游标的时候，会执行游标对应的select语句，这个指针会指向select结果中第一行记录。
当调用fetch 游标名称时，会获取当前行的数据，如果当前行无数据，会触发NOT FOUND异常。
当触发NOT FOUND异常的时候，我们可以使用一个变量来标记一下，如上面的：
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
将变量var_done的值置为TURE，循环中就可以通过var_done的值控制循环的退出：
LEAVE select_loop;。
如果当前行有数据，则将当前行数据存到对应的变量中，并将游标指针指向下一行数据，如下语句：FETCH cur_test INTO var_studentId,var_score;

总结

1、游标用来对查询结果进行遍历处理。
2、游标的使用过程：声明游标、打开游标、遍历游标、关闭游标。
3、游标主要用在循环处理、存储过程、函数中使用，用来查询结果集。
4、游标的缺点是只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢。数据库大部分是面对集合的，业务会比较复杂，而游标使用会有死锁，影响其他的业务操作，不可取。当数据量大时，使用游标会造成内存不足现象。

sxudong2010

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 游标的作用与使用相关

转载自：http://www.cppcns.com/shujuku/mysql/374794.html定义我们经常会遇到这样的一种情况，需要对我们查询的结果进行遍历操作，并对遍历到的每一条数据进行处理，这时候就会使用到游标。所以：游标（Cursor）是处理数据的一种存储在mysql服务器上的数据库查询方法，为了查看或者处理结果集中的数据，提供了在结果集中一次一行遍历数据的能力。游标主要用在循环处理、存储过程、函数、触发器中。游标的作用比如我们上面那个students学生，需要对每个用户进行遍历
复制链接

扫一扫